Are query execution plans stored anywhere in postgresql?

Question

I am trying to figure out if PostgreSQL query execution plans are stored somewhere (possibly as complimentary to pg_stat_statements and pg_prepared_statements) in a way they are available for longer than the duration of the session. I understand that PREPARE does cache a sql statement in pg_prepared_statements, though the plan itself does not seem to be available in any view as far as I can tell.

I am not sure if there is a doc explaining the life cycle of a query plan for PostgreSQL but from what it sounds in the EXPLAIN documentation, PostgreSQL does not cache query plans at all. Is this accurate?

Thanks!

you can save plan for every query to logs with `auto_explain` — Vao Tsun, Jan 17 '18 at 13:11

score 7 · Accepted Answer · answered Jan 17 '18 at 13:45

PostgreSQL has no shared storage for execution plans, so they cannot be reused across database sessions.

There are two ways to cache an execution plan within a session:

Use prepared statements with the SQL statements PREPARE and EXECUTE. The plan will be cached for the life time of the prepared statement, usually until your session ends.
Use PL/pgSQL functions. The plans for all static SQL statements (that is, statements that are not run with EXECUTE) in such a function will be cached for the session life time.

Other than that, execution plans are not cached in PostgreSQL.

Are query execution plans stored anywhere in postgresql?

1 Answers1