Prepared statements are queries where the constants used in the query are separated from the query text. While this improves security by avoiding SQL injection attacks, it also allows repeatedly-executed queries to avoid parsing and planning overhead by executing saved generic plans that represent queries with typical constants. While generic plans don't have the advantage of being planned based on the statistics of specific constants, the avoidance of parsing and planning overhead is often more beneficial.
Before Postgres 9.2, generic plans were used for all prepared queries. In Postgres 9.2, logic was added to use a generic plan only if it has a cheaper cost after five or more executions. Unfortunately, this behavior was undocumented, causing confusion for users surprised to find the explain plans of prepared queries changing after five executions, sometimes for the worse.
After much discussion, this has been remedied by improving the Notes section of the 9.6 prepare manual page. It now explains how generic plans are created, and when they are used. I have also created an sql script that, when run through psql (output), illustrates the documented behavior.
Bruce Momjian is a Senior Database Architect at EnterpriseDB.
This blog originally appeared on Bruce's personal blog.