Skip to content

Commit 3cccc69

Browse files
tglsfdctglsfdc
committed
Fix planning of btree index scans using ScalarArrayOpExpr quals.
In commit 9e8da0f, 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.
1 parent 66762ce commit 3cccc69

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
@@ -756,6 +756,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
756756
List *index_pathkeys;
757757
List *useful_pathkeys;
758758
bool found_clause;
759+
bool found_lower_saop_clause;
759760
bool pathkeys_possibly_useful;
760761
bool index_is_ordered;
761762
bool index_only_scan;
@@ -793,12 +794,20 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
793794
* if saop_control is SAOP_REQUIRE, it has to be a ScalarArrayOpExpr
794795
* clause.
795796
*
797+
* found_lower_saop_clause is set true if there's a ScalarArrayOpExpr
798+
* index clause for a non-first index column. This prevents us from
799+
* assuming that the scan result is ordered. (Actually, the result is
800+
* still ordered if there are equality constraints for all earlier
801+
* columns, but it seems too expensive and non-modular for this code to be
802+
* aware of that refinement.)
803+
*
796804
* We also build a Relids set showing which outer rels are required by the
797805
* selected clauses.
798806
*/
799807
index_clauses = NIL;
800808
clause_columns = NIL;
801809
found_clause = false;
810+
found_lower_saop_clause = false;
802811
outer_relids = NULL;
803812
for (indexcol = 0; indexcol < index->ncolumns; indexcol++)
804813
{
@@ -814,6 +823,8 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
814823
if (saop_control == SAOP_PER_AM && !index->amsearcharray)
815824
continue;
816825
found_clause = true;
826+
if (indexcol > 0)
827+
found_lower_saop_clause = true;
817828
}
818829
else
819830
{
@@ -850,9 +861,11 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
850861
/*
851862
* 2. Compute pathkeys describing index's ordering, if any, then see how
852863
* many of them are actually useful for this query. This is not relevant
853-
* if we are only trying to build bitmap indexscans.
864+
* if we are only trying to build bitmap indexscans, nor if we have to
865+
* assume the scan is unordered.
854866
*/
855867
pathkeys_possibly_useful = (scantype != ST_BITMAPSCAN &&
868+
!found_lower_saop_clause &&
856869
has_useful_pathkeys(root, rel));
857870
index_is_ordered = (index->sortopfamily != NULL);
858871
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 < 1000 and f1 ~<~ 'YX';
889889
SELECT count(*) FROM dupindexcols
890890
WHERE f1 > 'WA' and id < 1000 and 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 unique1 FROM tenk1
900+
WHERE unique1 IN (1,42,7)
901+
ORDER BY unique1;
902+
903+
SELECT unique1 FROM tenk1
904+
WHERE unique1 IN (1,42,7)
905+
ORDER BY unique1;
906+
907+
explain (costs off)
908+
SELECT thousand, tenthous FROM tenk1
909+
WHERE thousand < 2 AND tenthous IN (1001,3000)
910+
ORDER BY thousand;
911+
912+
SELECT thousand, tenthous FROM tenk1
913+
WHERE thousand < 2 AND tenthous IN (1001,3000)
914+
ORDER BY thousand;

0 commit comments

Comments
 (0)