pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix planning of btree index scans using ScalarArrayOpExpr quals. 2+ messages / 1 participants [nested] [flat]
* pgsql: Fix planning of btree index scans using ScalarArrayOpExpr quals. @ 2012-09-18 16:21 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2012-09-18 16:21 UTC (permalink / raw) To: pgsql-committers Fix planning of btree index scans using ScalarArrayOpExpr quals. In commit 9e8da0f75731aaa7605cf4656c21ea09e84d2eb1, I improved btree to handle ScalarArrayOpExpr quals natively, so that constructs like "indexedcol IN (list)" could be supported by index-only scans. Using such a qual results in multiple scans of the index, under-the-hood. I went to some lengths to ensure that this still produces rows in index order ... but I failed to recognize that if a higher-order index column is lacking an equality constraint, rescans can produce out-of-order data from that column. Tweak the planner to not expect sorted output in that case. Per trouble report from Robert McGehee. Branch ------ master Details ------- http://git.postgresql.org/pg/commitdiff/807a40c551dd30c8dd5a0b3bd82f5bbb1e7fd285 Modified Files -------------- src/backend/optimizer/path/indxpath.c | 15 ++++++++- src/test/regress/expected/create_index.out | 45 ++++++++++++++++++++++++++++ src/test/regress/sql/create_index.sql | 24 +++++++++++++++ 3 files changed, 83 insertions(+), 1 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Fix planning of btree index scans using ScalarArrayOpExpr quals. @ 2012-09-18 16:21 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2012-09-18 16:21 UTC (permalink / raw) To: pgsql-committers Fix planning of btree index scans using ScalarArrayOpExpr quals. In commit 9e8da0f75731aaa7605cf4656c21ea09e84d2eb1, I improved btree to handle ScalarArrayOpExpr quals natively, so that constructs like "indexedcol IN (list)" could be supported by index-only scans. Using such a qual results in multiple scans of the index, under-the-hood. I went to some lengths to ensure that this still produces rows in index order ... but I failed to recognize that if a higher-order index column is lacking an equality constraint, rescans can produce out-of-order data from that column. Tweak the planner to not expect sorted output in that case. Per trouble report from Robert McGehee. Branch ------ REL9_2_STABLE Details ------- http://git.postgresql.org/pg/commitdiff/3cccc6990b66265ad7c4bfbe5789ce571a39d876 Modified Files -------------- src/backend/optimizer/path/indxpath.c | 15 ++++++++- src/test/regress/expected/create_index.out | 45 ++++++++++++++++++++++++++++ src/test/regress/sql/create_index.sql | 24 +++++++++++++++ 3 files changed, 83 insertions(+), 1 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-09-18 16:21 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2012-09-18 16:21 pgsql: Fix planning of btree index scans using ScalarArrayOpExpr quals. Tom Lane <tgl@sss.pgh.pa.us> 2012-09-18 16:21 pgsql: Fix planning of btree index scans using ScalarArrayOpExpr quals. Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox