patternsqlMinor
Where is the generic plan stored when using PREPARE statement in PostgreSQL?
Viewed 0 times
storedgenericpostgresqlthestatementwhereplanusingwhenprepare
Problem
According to the document, 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. My question is where the generic plan is saved? Is it stored in a file in disk? If yes, where is it located and what is the file name?
Solution
The generic plan is stored in the in-memory plan cache of the back-end that created the plan. See doxygen for
Plans are not written to disk or read from disk, and are not shared between different backends (therefore, different sessions/connections). The documentation should really read:
if a prepared statement is executed enough times in a single session, the server may eventually decide to save and re-use a generic plan in the per-connection plan cache rather than re-planning each time
You could probably modify the plancache code to serialize plans to disk, but it's not a trivial job to do it in a useful way. Reading them in on another backend correctly is much, much harder, when you consider the consequences of transactional DDL. Writing a proper persistent plan cache with correct plan invalidation etc would be a major job for an experienced PostgreSQL developer who already knew the optimizer/executor/planner/rewriter code well. If you wanted to tackle this you should expect to spend months studying the code before you could propose a reasonable approach, let alone implement it. (I've just been doing that with row-security for the AXLE project, so I'm all too aware of the complexity of this code).
As I said in your last question, let go of the idea that you can save plans, you can't, and concentrate on sorting out the issue that's causing the wrong plan to be chosen.
plancache.c or the source.Plans are not written to disk or read from disk, and are not shared between different backends (therefore, different sessions/connections). The documentation should really read:
if a prepared statement is executed enough times in a single session, the server may eventually decide to save and re-use a generic plan in the per-connection plan cache rather than re-planning each time
You could probably modify the plancache code to serialize plans to disk, but it's not a trivial job to do it in a useful way. Reading them in on another backend correctly is much, much harder, when you consider the consequences of transactional DDL. Writing a proper persistent plan cache with correct plan invalidation etc would be a major job for an experienced PostgreSQL developer who already knew the optimizer/executor/planner/rewriter code well. If you wanted to tackle this you should expect to spend months studying the code before you could propose a reasonable approach, let alone implement it. (I've just been doing that with row-security for the AXLE project, so I'm all too aware of the complexity of this code).
As I said in your last question, let go of the idea that you can save plans, you can't, and concentrate on sorting out the issue that's causing the wrong plan to be chosen.
Context
StackExchange Database Administrators Q#58664, answer score: 3
Revisions (0)
No revisions yet.