1

I'm wondering if I prepare a statement in PostgreSQL, i.e.:

PREPARE newquery(int) AS
SELECT * FROM table1 WHERE somefield > $1;

Will the backend call the parse analysis and the planner when I execute:

EXECUTE newquery(123);

I found some documentation where it says that PostgreSQL "skips" parse analysis and planning of prepared queries, and by doing that significantly optimizes query execution.

I tried testing this by overwriting the post_parse_analyze_hook and planner_hook, and from them simply writing some stuff into log files(i.e. "Planner called"), so I can see if the planner and parse analyzer are called. Is this testing method valid?

I'm confused, because every time I execute EXECUTE newquery(some number); both the parse analyzer and the planner are called.

I'm doing all this because I want to count how many times a query was compiled to get a better understanding of "query caching".

Note: I'm doing this in a single session. Every time I connect to the server I prepare the statements again.

Why are planner and parse analyzer called?

ZrtvenoJagnje
  • 87
  • 11
  • I suppose that the parse analyzer is called for `EXECUTE newquery(123);`, not for the underling query and planner is called because the query plan could be depending on the parameter. Good question but let's wait for someone more experienced. – Abelisto Apr 03 '17 at 10:54

1 Answers1

3

This must be the “custom plan” feature introduced in 9.2. For the first few executions of a prepared statement it will generate a custom plan that uses the parameter values and then switch to the generic plan (that does not use the parameter values) if these plans don't look better than the generic plan.

Try to EXECUTE the prepared statement at least six times and see if the planner is still called.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263