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 <[email protected]> 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 +
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
