How to benchmark query parsing & planning in Postgres?

Question

I'm trying to benchmark the query planning and parsing steps in PG:

• However, it seems that the query planner is using some sort of cache - only the first execution of EXPLAIN ANALYZE seems to report a high value in the Planning time section. Subsequently, it is less than 1ms. How do I turn this off?
• The docs clearly state that the parsing time is not taken into account by EXPLAIN ANALYZE. Is there any other command/tool that will report the time it took to parse a query?

The Planning time shown by EXPLAIN ANALYZE is the time it took to generate the query plan from the parsed query and optimize it. It does not include parsing or rewriting.

Answer 1

It is not caching the actual prepared plans (unless you are using prepared statements). But it does cache all the metadata it needs to look up in order to come up with the plan. So the original planning time includes the time it took to read in all of that metadata.

If you wish to defeat the caching for experimental purposes, the simplest way would be to simply close psql and re-open it between every experiment.

For instrumentation, You can time each step of the parse-plan-execute process using log_statement_stats or its kin. You can also set client_min_messages = log so that those reported statistics show up directly on the psql screen, rather than having to go fetch them from the server log file. You will need to be a superuser to set either of those.