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) AS z(y);
INSERT INTO test SELECT 1, y FROM generate_series(10001, 20000) AS z(y);
-- INSERT INTO test SELECT 2, 20001;
-- INSERT INTO test SELECT 3, 20002;
CREATE INDEX i_test_x ON test(x);
CREATE INDEX i_test_y ON test(y);
ANALYZE test;
PREPARE prep_x AS 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(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 +