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