Prepared statements are queries where the constants used in the query are separated from the query text. While this improves security by avoidingSQL injection attacks, it also allows repeatedly-executed queries to avoid parsing and planning overhead by executing saved generic plans that represent queries withtypicalconstants. 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. InPostgres 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, causingconfusionfor users surprised to find theexplainplans of prepared queries changing after five executions, sometimes for the worse.
After muchdiscussion,this has been remedied by improving theNotessection of the 9.6preparemanual page. It now explains how generic plans are created, and when they are used. I have also created an sqlscriptthat, when run throughpsql(output), illustrates the documented behavior.
This blog originally appeared on Bruce's personalblog.