Re: Cached/global query plans, autopreparation

On 2018-03-02 15:29:09 -0500, Bruce Momjian wrote:
> Postgres uses a conservative method for reusing plans with previous
> constants, as described in the PREPARE manual page:
> 
> 	https://www.postgresql.org/docs/10/static/sql-prepare.html
> 	Prepared statements can use generic plans rather than re-planning with
> 	each set of supplied EXECUTE values. This occurs immediately for prepared
> 	statements with no parameters; otherwise it occurs only after five or more
> 	executions produce plans whose estimated cost average (including planning
> 	overhead) is more expensive than the generic plan cost estimate. Once
> 	a generic plan is chosen, it is used for the remaining lifetime of the
> 	prepared statement. Using EXECUTE values which are rare in columns with
> 	many duplicates can generate custom plans that are so much cheaper than
> 	the generic plan, even after adding planning overhead, that the generic
> 	plan might never be used.
> 
> While I have heard people complain about how other databases cache
> prepare plans, I have heard few complaints about the Postgres approach,
> and I haven't even heard of people asking to control the documented "five
> or more" behavior.

This *constantly* is a problem.


Greetings,

Andres Freund