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
– Vao Tsun, Commented Jan 17, 2018 at 13:11

Laurenz Albe · Accepted Answer · 2018-01-17 13:45:20Z

10

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.

answered Jan 17, 2018 at 13:45

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Are query execution plans stored anywhere in postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related