Re: Prepared statements and generic plans

On Thu, Jun  2, 2016 at 09:56:48PM -0400, Bruce Momjian wrote:
> In Postgres 9.2 we improved the logic of when generic plans are used by
> EXECUTE.  We weren't sure how well it would work, and the docs included
> a vague description on when generic plans are chosen.
> 
> I have gotten a few questions lately about how prepared statements are
> handled with multiple executions so I have updated the PREPARE manual
> page with the attached patch to more clearly explain generic plans and
> when they are chosen.
> 
> I would like to apply this to the 9.6 docs.

FYI, I used this set of queries for testing:
DROP TABLE IF EXISTS test;CREATE TABLE test (x INT, y INT);INSERT INTO test SELECT 0, y FROM generate_series(1, 10000)
ASz(y);INSERT INTO test SELECT 1, y FROM generate_series(10001, 20000) AS z(y);-- INSERT INTO test SELECT 2, 20001;--
INSERTINTO test SELECT 3, 20002;CREATE INDEX i_test_x ON test(x);CREATE INDEX i_test_y ON test(y);ANALYZE test;PREPARE
prep_xAS SELECT * FROM test WHERE x = $1;PREPARE prep_y AS SELECT * FROM test WHERE y = $1;
 

Doing execute 5+ times on the two prepared statements with the constants
'0' and '2' show the documented behavior, e.g.:
EXPLAIN EXECUTE prep_x(2);EXPLAIN EXECUTE prep_y(2);

--  Bruce Momjian  <bruce@momjian.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 +