Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit807a40c

Browse files
committed
Fix planning of btree index scans using ScalarArrayOpExpr quals.
In commit9e8da0f, I improved btreeto handle ScalarArrayOpExpr quals natively, so that constructs like"indexedcol IN (list)" could be supported by index-only scans. Usingsuch 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 indexorder ... but I failed to recognize that if a higher-order index columnis lacking an equality constraint, rescans can produce out-of-orderdata from that column. Tweak the planner to not expect sorted outputin that case. Per trouble report from Robert McGehee.
1 parent3f828fa commit807a40c

File tree

3 files changed

+83
-1
lines changed

3 files changed

+83
-1
lines changed

‎src/backend/optimizer/path/indxpath.c

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -787,6 +787,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
787787
List*index_pathkeys;
788788
List*useful_pathkeys;
789789
boolfound_clause;
790+
boolfound_lower_saop_clause;
790791
boolpathkeys_possibly_useful;
791792
boolindex_is_ordered;
792793
boolindex_only_scan;
@@ -824,13 +825,21 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
824825
* if saop_control is SAOP_REQUIRE, it has to be a ScalarArrayOpExpr
825826
* clause.
826827
*
828+
* found_lower_saop_clause is set true if there's a ScalarArrayOpExpr
829+
* index clause for a non-first index column. This prevents us from
830+
* assuming that the scan result is ordered. (Actually, the result is
831+
* still ordered if there are equality constraints for all earlier
832+
* columns, but it seems too expensive and non-modular for this code to be
833+
* aware of that refinement.)
834+
*
827835
* We also build a Relids set showing which outer rels are required by the
828836
* selected clauses. Any lateral_relids are included in that, but not
829837
* otherwise accounted for.
830838
*/
831839
index_clauses=NIL;
832840
clause_columns=NIL;
833841
found_clause= false;
842+
found_lower_saop_clause= false;
834843
outer_relids=bms_copy(rel->lateral_relids);
835844
for (indexcol=0;indexcol<index->ncolumns;indexcol++)
836845
{
@@ -846,6 +855,8 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
846855
if (saop_control==SAOP_PER_AM&& !index->amsearcharray)
847856
continue;
848857
found_clause= true;
858+
if (indexcol>0)
859+
found_lower_saop_clause= true;
849860
}
850861
else
851862
{
@@ -882,9 +893,11 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
882893
/*
883894
* 2. Compute pathkeys describing index's ordering, if any, then see how
884895
* many of them are actually useful for this query. This is not relevant
885-
* if we are only trying to build bitmap indexscans.
896+
* if we are only trying to build bitmap indexscans, nor if we have to
897+
* assume the scan is unordered.
886898
*/
887899
pathkeys_possibly_useful= (scantype!=ST_BITMAPSCAN&&
900+
!found_lower_saop_clause&&
888901
has_useful_pathkeys(root,rel));
889902
index_is_ordered= (index->sortopfamily!=NULL);
890903
if (index_is_ordered&&pathkeys_possibly_useful)

‎src/test/regress/expected/create_index.out

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2676,3 +2676,48 @@ SELECT count(*) FROM dupindexcols
26762676
97
26772677
(1 row)
26782678

2679+
--
2680+
-- Check ordering of =ANY indexqual results (bug in 9.2.0)
2681+
--
2682+
vacuum analyze tenk1;-- ensure we get consistent plans here
2683+
explain (costs off)
2684+
SELECT unique1 FROM tenk1
2685+
WHERE unique1 IN (1,42,7)
2686+
ORDER BY unique1;
2687+
QUERY PLAN
2688+
-------------------------------------------------------
2689+
Index Only Scan using tenk1_unique1 on tenk1
2690+
Index Cond: (unique1 = ANY ('{1,42,7}'::integer[]))
2691+
(2 rows)
2692+
2693+
SELECT unique1 FROM tenk1
2694+
WHERE unique1 IN (1,42,7)
2695+
ORDER BY unique1;
2696+
unique1
2697+
---------
2698+
1
2699+
7
2700+
42
2701+
(3 rows)
2702+
2703+
explain (costs off)
2704+
SELECT thousand, tenthous FROM tenk1
2705+
WHERE thousand < 2 AND tenthous IN (1001,3000)
2706+
ORDER BY thousand;
2707+
QUERY PLAN
2708+
--------------------------------------------------------------------------------------
2709+
Sort
2710+
Sort Key: thousand
2711+
-> Index Only Scan using tenk1_thous_tenthous on tenk1
2712+
Index Cond: ((thousand < 2) AND (tenthous = ANY ('{1001,3000}'::integer[])))
2713+
(4 rows)
2714+
2715+
SELECT thousand, tenthous FROM tenk1
2716+
WHERE thousand < 2 AND tenthous IN (1001,3000)
2717+
ORDER BY thousand;
2718+
thousand | tenthous
2719+
----------+----------
2720+
0 | 3000
2721+
1 | 1001
2722+
(2 rows)
2723+

‎src/test/regress/sql/create_index.sql

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -888,3 +888,27 @@ EXPLAIN (COSTS OFF)
888888
WHERE f1>'WA'and id<1000and f1 ~<~'YX';
889889
SELECTcount(*)FROM dupindexcols
890890
WHERE f1>'WA'and id<1000and f1 ~<~'YX';
891+
892+
--
893+
-- Check ordering of =ANY indexqual results (bug in 9.2.0)
894+
--
895+
896+
vacuum analyze tenk1;-- ensure we get consistent plans here
897+
898+
explain (costs off)
899+
SELECT unique1FROM tenk1
900+
WHERE unique1IN (1,42,7)
901+
ORDER BY unique1;
902+
903+
SELECT unique1FROM tenk1
904+
WHERE unique1IN (1,42,7)
905+
ORDER BY unique1;
906+
907+
explain (costs off)
908+
SELECT thousand, tenthousFROM tenk1
909+
WHERE thousand<2AND tenthousIN (1001,3000)
910+
ORDER BY thousand;
911+
912+
SELECT thousand, tenthousFROM tenk1
913+
WHERE thousand<2AND tenthousIN (1001,3000)
914+
ORDER BY thousand;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp