Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

On Mon, Apr 29, 2019 at 10:36:20AM +0000, Naik, Sameer wrote:
> Hi,
>
> Since Postgres 9.2, for prepared statements, the CBO automatically switches
> from Custom Plan to Generic plan on the sixth iteration (reference backend/
> utils/cache/plancache.c).

This is not totally true. The PREPARE manual page for PG 11 says:

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.

Also, PG 9.2 is EOL so are you actually using that or something more
recent? It would be interesting to see if this is true on a supported
version of Postgres.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +