6

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.

  • 1
    Not sure, but what if you use log_parser_stats = on and log_planner_stats = on in your postgresql.conf. Does that log the information you are after? – a_horse_with_no_name Dec 19 '14 at 15:01
  • @a_horse_with_no_name: Thanks, that logs information about the CPU and file I/O per query step. The "PARSE ANALYSIS" "elapsed" value is lower for consecutive invocations. But there is no log that says if the plan cache was used, and it would be difficult to monitor. Is there a system view that provides information related to the query parser? – Andomar Dec 19 '14 at 15:17
  • If there is such a view it will be documented in the manual: postgresql.org/docs/current/static/monitoring.html – a_horse_with_no_name Dec 19 '14 at 16:10
4

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:

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

  • Did some tests and you're right. It looks like you need connection pooling software (like pg_bouncer) to reuse a query plan. Having ruled out cached query plans, I'm now wondering why the load on our production machine went from 3.5 to 0.5 after moving from ad-hoc SQL to pgsql functions! – Andomar Dec 26 '14 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.