Monitoring the Postgres query parser

Question

As I understand it, when you send a query to Postgres like:

SELECT id FROM table1 WHERE name = $1;

Postgres will create a query plan. The plan will be cached for the same query in the same session. But if you create a function:

CREATE OR REPLACE FUNCTION get_table1 (parname text) RETURNS bigint
LANGUAGE plpgsql AS 
$$BEGIN
  RETURN (SELECT id FROM table1 where name = parname);
END$$;

The query plan will be cached for all future sessions.

I'd like to verify this theory by checking how often the query analyzer is invoked. Is there a way to inspect the number of times Postgres parses a query?

If possible, I'd like to monitor things like #parses per second and the min/max/avg parse duration.

Answer 1

It's not possible to save query plans "for all future sessions". Query plans are only ever saved for the current session. And only reused under a number of favorable conditions.

Plans for ad-hoc SQL queries are not saved at all. All queries inside PL/pgSQL functions are treated like prepared statements. And there are more steps than just the query planning.

It's important to note that there have been substantial changes since Postgres 9.1, where generic plans were more generally saved and reused for statements inside PL/pgSQL. This has become a lot smarter with pg 9.2.

The manual (bold emphasis mine):

Prepared statements only last for the duration of the current database session.

That's not saying anything about query plans, yet:

When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed.

And further down:

If a prepared statement is executed enough times, the server may eventually decide to save and re-use a generic plan rather than re-planning each time. This will occur immediately if the prepared statement has no parameters; otherwise it occurs only if the generic plan appears to be not much more expensive than a plan that depends on specific parameter values. Typically, a generic plan will be selected only if the query's performance is estimated to be fairly insensitive to the specific parameter values supplied.

How to tell whether a generic plan is used?

To examine the query plan PostgreSQL is using for a prepared statement, use EXPLAIN. If a generic plan is in use, it will contain parameter symbols $n, while a custom plan will have the current actual parameter values substituted into it.

A saved plan is always a generic plan, but not vice versa.
Where are prepared statements saved?

You can see all prepared statements available in the session by querying the pg_prepared_statements system view.

Query plans for SQL statements in PL/pgSQL

Those are not normally visible in EXPLAIN output. You can install the additional module auto-explain to write those plans to the log, too. Details here:

• Postgres query plan of a UDF invocation written in pgpsql

For details about Plan Caching in PL/pgSQL functions, best read the manual directly.