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