From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: Prepared statements and generic plans |
Date: | 2016-06-03 02:05:22 |
Message-ID: | 20160603020522.GB16734@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 +
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-06-03 03:44:24 | Re: Prepared statements and generic plans |
Previous Message | Bruce Momjian | 2016-06-03 01:56:48 | Prepared statements and generic plans |