Re: Plan caching and serialization for reuse across executions

On 16/8/2025 14:32, Xuan Chen wrote:
> Hi hackers,
> 
> I am currently exploring whether it is possible to cache a plan generated by the PostgreSQL planner and reuse it
acrosslater executions.
 
> 
> I understand that there are existing mechanisms like PREPARE/EXECUTE and CachedPlan in plancache.c, but these are
tiedto prepared statements and session-level usage. My interest is more on the research side:
 
> 
> - Is there any supported (or experimental) way to serialize a Plan or PlannedStmt structure and reload it in a future
execution,bypassing the planner?
 
> - If not, would it make sense to extend the existing CachedPlan mechanism to support serialization/deserialization,
orare there known architectural reasons why this approach is discouraged?
 
> - Any relevant references in the source code (planner, plancache.c, executor) that I should study would be very
helpful.
> 
> The motivation is research-oriented: I want to experiment with plan persistence as a way to reduce planning overhead
andstudy trade-offs of reusing cached plans.
 
> 
> Thanks a lot for your guidance!
Yes, it is possible. See [1] to find out how it is implemented in the 
Postgres architecture.
We implemented 'freezing' of a statement with parameterisation defined 
by the user. Each incoming query (constant or parameterised) matches the 
QueryId. Then, with matching incoming and the stored parse tree, it 
finds the corresponding plan and proves that it may be used for the 
query (remember, rewriting rules, indexes, and other factors may change).
Such a 'frozen' plan is serialised into the shared memory and is lazily 
transferred to all backends and used across all instances.
There are lots of issues that still exist for me after the finish of 
this project. So, you have a whole room of problems that you can address 
in your research ;).

[1] https://github.com/danolivo/conf/blob/main/2023-PGDay-Israel/sr-plan.pdf

-- 
regards, Andrei Lepikhov