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

Commitf6abf8f

Browse files
committed
Improve planning of btree index scans using ScalarArrayOpExpr quals.
Since we taught btree to handle ScalarArrayOpExpr quals natively (commit9e8da0f), the planner has always includedScalarArrayOpExpr quals in index conditions if possible. However, if thequal is for a non-first index column, this could result in an inferior planbecause we can no longer take advantage of index ordering (cf. commit807a40c). It can be better to omit theScalarArrayOpExpr qual from the index condition and let it be done as afilter, so that the output doesn't need to get sorted. Indeed, this istrue for the query introduced as a test case by the latter commit.To fix, restructure get_index_paths and build_index_paths so that weconsider paths both with and without ScalarArrayOpExpr quals in non-firstindex columns. Redesign the API of build_index_paths so that it reportswhat it found, saving useless second or third calls.Report and patch by Andrew Gierth (though rather heavily modified by me).Back-patch to 9.2 where this code was introduced, since the issue canresult in significant performance regressions compared to plans producedby 9.1 and earlier.
1 parent9945f4e commitf6abf8f

File tree

3 files changed

+112
-41
lines changed

3 files changed

+112
-41
lines changed

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

Lines changed: 76 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -45,14 +45,6 @@
4545
#defineIndexCollMatchesExprColl(idxcollation,exprcollation) \
4646
((idxcollation) == InvalidOid || (idxcollation) == (exprcollation))
4747

48-
/* Whether to use ScalarArrayOpExpr to build index qualifications */
49-
typedefenum
50-
{
51-
SAOP_PER_AM,/* Use ScalarArrayOpExpr if amsearcharray */
52-
SAOP_ALLOW,/* Use ScalarArrayOpExpr for all indexes */
53-
SAOP_REQUIRE/* Require ScalarArrayOpExpr to be used */
54-
}SaOpControl;
55-
5648
/* Whether we are looking for plain indexscan, bitmap scan, or either */
5749
typedefenum
5850
{
@@ -118,7 +110,9 @@ static void get_index_paths(PlannerInfo *root, RelOptInfo *rel,
118110
staticList*build_index_paths(PlannerInfo*root,RelOptInfo*rel,
119111
IndexOptInfo*index,IndexClauseSet*clauses,
120112
booluseful_predicate,
121-
SaOpControlsaop_control,ScanTypeControlscantype);
113+
ScanTypeControlscantype,
114+
bool*skip_nonnative_saop,
115+
bool*skip_lower_saop);
122116
staticList*build_paths_for_OR(PlannerInfo*root,RelOptInfo*rel,
123117
List*clauses,List*other_clauses);
124118
staticList*drop_indexable_join_clauses(RelOptInfo*rel,List*clauses);
@@ -727,23 +721,47 @@ bms_equal_any(Relids relids, List *relids_list)
727721
* index AM supports them natively, we should just include them in simple
728722
* index paths. If not, we should exclude them while building simple index
729723
* paths, and then make a separate attempt to include them in bitmap paths.
724+
* Furthermore, we should consider excluding lower-order ScalarArrayOpExpr
725+
* quals so as to create ordered paths.
730726
*/
731727
staticvoid
732728
get_index_paths(PlannerInfo*root,RelOptInfo*rel,
733729
IndexOptInfo*index,IndexClauseSet*clauses,
734730
List**bitindexpaths)
735731
{
736732
List*indexpaths;
733+
boolskip_nonnative_saop= false;
734+
boolskip_lower_saop= false;
737735
ListCell*lc;
738736

739737
/*
740738
* Build simple index paths using the clauses. Allow ScalarArrayOpExpr
741-
* clauses only if the index AM supports them natively.
739+
* clauses only if the index AM supports them natively, and skip any such
740+
* clauses for index columns after the first (so that we produce ordered
741+
* paths if possible).
742742
*/
743743
indexpaths=build_index_paths(root,rel,
744744
index,clauses,
745745
index->predOK,
746-
SAOP_PER_AM,ST_ANYSCAN);
746+
ST_ANYSCAN,
747+
&skip_nonnative_saop,
748+
&skip_lower_saop);
749+
750+
/*
751+
* If we skipped any lower-order ScalarArrayOpExprs on an index with an AM
752+
* that supports them, then try again including those clauses. This will
753+
* produce paths with more selectivity but no ordering.
754+
*/
755+
if (skip_lower_saop)
756+
{
757+
indexpaths=list_concat(indexpaths,
758+
build_index_paths(root,rel,
759+
index,clauses,
760+
index->predOK,
761+
ST_ANYSCAN,
762+
&skip_nonnative_saop,
763+
NULL));
764+
}
747765

748766
/*
749767
* Submit all the ones that can form plain IndexScan plans to add_path. (A
@@ -771,16 +789,18 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
771789
}
772790

773791
/*
774-
* If the indexdoesn't handle ScalarArrayOpExpr clauses natively, check
775-
*to see if there are any such clauses, and if sogenerate bitmap scan
776-
*paths relying on executor-managedScalarArrayOpExpr.
792+
* Ifthere were ScalarArrayOpExpr clauses thatthe indexcan't handle
793+
*natively,generate bitmap scan paths relying on executor-managed
794+
* ScalarArrayOpExpr.
777795
*/
778-
if (!index->amsearcharray)
796+
if (skip_nonnative_saop)
779797
{
780798
indexpaths=build_index_paths(root,rel,
781799
index,clauses,
782800
false,
783-
SAOP_REQUIRE,ST_BITMAPSCAN);
801+
ST_BITMAPSCAN,
802+
NULL,
803+
NULL);
784804
*bitindexpaths=list_concat(*bitindexpaths,indexpaths);
785805
}
786806
}
@@ -803,26 +823,36 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
803823
* Note that this routine should never be called at all if the index has an
804824
* unprovable predicate.
805825
*
806-
* saop_control indicates whether ScalarArrayOpExpr clauses can be used.
807-
* When it's SAOP_REQUIRE, index paths are created only if we found at least
808-
* one ScalarArrayOpExpr clause.
809-
*
810826
* scantype indicates whether we want to create plain indexscans, bitmap
811827
* indexscans, or both. When it's ST_BITMAPSCAN, we will not consider
812828
* index ordering while deciding if a Path is worth generating.
813829
*
830+
* If skip_nonnative_saop is non-NULL, we ignore ScalarArrayOpExpr clauses
831+
* unless the index AM supports them directly, and we set *skip_nonnative_saop
832+
* to TRUE if we found any such clauses (caller must initialize the variable
833+
* to FALSE). If it's NULL, we do not ignore ScalarArrayOpExpr clauses.
834+
*
835+
* If skip_lower_saop is non-NULL, we ignore ScalarArrayOpExpr clauses for
836+
* non-first index columns, and we set *skip_lower_saop to TRUE if we found
837+
* any such clauses (caller must initialize the variable to FALSE). If it's
838+
* NULL, we do not ignore non-first ScalarArrayOpExpr clauses, but they will
839+
* result in considering the scan's output to be unordered.
840+
*
814841
* 'rel' is the index's heap relation
815842
* 'index' is the index for which we want to generate paths
816843
* 'clauses' is the collection of indexable clauses (RestrictInfo nodes)
817844
* 'useful_predicate' indicates whether the index has a useful predicate
818-
* 'saop_control' indicates whether ScalarArrayOpExpr clauses can be used
819845
* 'scantype' indicates whether we need plain or bitmap scan support
846+
* 'skip_nonnative_saop' indicates whether to accept SAOP if index AM doesn't
847+
* 'skip_lower_saop' indicates whether to accept non-first-column SAOP
820848
*/
821849
staticList*
822850
build_index_paths(PlannerInfo*root,RelOptInfo*rel,
823851
IndexOptInfo*index,IndexClauseSet*clauses,
824852
booluseful_predicate,
825-
SaOpControlsaop_control,ScanTypeControlscantype)
853+
ScanTypeControlscantype,
854+
bool*skip_nonnative_saop,
855+
bool*skip_lower_saop)
826856
{
827857
List*result=NIL;
828858
IndexPath*ipath;
@@ -834,7 +864,6 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
834864
List*orderbyclausecols;
835865
List*index_pathkeys;
836866
List*useful_pathkeys;
837-
boolfound_clause;
838867
boolfound_lower_saop_clause;
839868
boolpathkeys_possibly_useful;
840869
boolindex_is_ordered;
@@ -869,11 +898,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
869898
* (This order is depended on by btree and possibly other places.)The
870899
* lists can be empty, if the index AM allows that.
871900
*
872-
* found_clause is set true only if there's at least one index clause; and
873-
* if saop_control is SAOP_REQUIRE, it has to be a ScalarArrayOpExpr
874-
* clause.
875-
*
876-
* found_lower_saop_clause is set true if there's a ScalarArrayOpExpr
901+
* found_lower_saop_clause is set true if we accept a ScalarArrayOpExpr
877902
* index clause for a non-first index column. This prevents us from
878903
* assuming that the scan result is ordered. (Actually, the result is
879904
* still ordered if there are equality constraints for all earlier
@@ -886,7 +911,6 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
886911
*/
887912
index_clauses=NIL;
888913
clause_columns=NIL;
889-
found_clause= false;
890914
found_lower_saop_clause= false;
891915
outer_relids=bms_copy(rel->lateral_relids);
892916
for (indexcol=0;indexcol<index->ncolumns;indexcol++)
@@ -899,17 +923,27 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
899923

900924
if (IsA(rinfo->clause,ScalarArrayOpExpr))
901925
{
902-
/* Ignore if not supported by index */
903-
if (saop_control==SAOP_PER_AM&& !index->amsearcharray)
904-
continue;
905-
found_clause= true;
926+
if (!index->amsearcharray)
927+
{
928+
if (skip_nonnative_saop)
929+
{
930+
/* Ignore because not supported by index */
931+
*skip_nonnative_saop= true;
932+
continue;
933+
}
934+
/* Caller had better intend this only for bitmap scan */
935+
Assert(scantype==ST_BITMAPSCAN);
936+
}
906937
if (indexcol>0)
938+
{
939+
if (skip_lower_saop)
940+
{
941+
/* Caller doesn't want to lose index ordering */
942+
*skip_lower_saop= true;
943+
continue;
944+
}
907945
found_lower_saop_clause= true;
908-
}
909-
else
910-
{
911-
if (saop_control!=SAOP_REQUIRE)
912-
found_clause= true;
946+
}
913947
}
914948
index_clauses=lappend(index_clauses,rinfo);
915949
clause_columns=lappend_int(clause_columns,indexcol);
@@ -989,7 +1023,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
9891023
* later merging or final output ordering, OR the index has a useful
9901024
* predicate, OR an index-only scan is possible.
9911025
*/
992-
if (found_clause||useful_pathkeys!=NIL||useful_predicate||
1026+
if (index_clauses!=NIL||useful_pathkeys!=NIL||useful_predicate||
9931027
index_only_scan)
9941028
{
9951029
ipath=create_index_path(root,index,
@@ -1138,7 +1172,9 @@ build_paths_for_OR(PlannerInfo *root, RelOptInfo *rel,
11381172
indexpaths=build_index_paths(root,rel,
11391173
index,&clauseset,
11401174
useful_predicate,
1141-
SAOP_ALLOW,ST_BITMAPSCAN);
1175+
ST_BITMAPSCAN,
1176+
NULL,
1177+
NULL);
11421178
result=list_concat(result,indexpaths);
11431179
}
11441180

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

Lines changed: 23 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2720,6 +2720,27 @@ ORDER BY unique1;
27202720
42
27212721
(3 rows)
27222722

2723+
explain (costs off)
2724+
SELECT thousand, tenthous FROM tenk1
2725+
WHERE thousand < 2 AND tenthous IN (1001,3000)
2726+
ORDER BY thousand;
2727+
QUERY PLAN
2728+
-------------------------------------------------------
2729+
Index Only Scan using tenk1_thous_tenthous on tenk1
2730+
Index Cond: (thousand < 2)
2731+
Filter: (tenthous = ANY ('{1001,3000}'::integer[]))
2732+
(3 rows)
2733+
2734+
SELECT thousand, tenthous FROM tenk1
2735+
WHERE thousand < 2 AND tenthous IN (1001,3000)
2736+
ORDER BY thousand;
2737+
thousand | tenthous
2738+
----------+----------
2739+
0 | 3000
2740+
1 | 1001
2741+
(2 rows)
2742+
2743+
SET enable_indexonlyscan = OFF;
27232744
explain (costs off)
27242745
SELECT thousand, tenthous FROM tenk1
27252746
WHERE thousand < 2 AND tenthous IN (1001,3000)
@@ -2728,7 +2749,7 @@ ORDER BY thousand;
27282749
--------------------------------------------------------------------------------------
27292750
Sort
27302751
Sort Key: thousand
2731-
-> IndexOnlyScan using tenk1_thous_tenthous on tenk1
2752+
-> Index Scan using tenk1_thous_tenthous on tenk1
27322753
Index Cond: ((thousand < 2) AND (tenthous = ANY ('{1001,3000}'::integer[])))
27332754
(4 rows)
27342755

@@ -2741,6 +2762,7 @@ ORDER BY thousand;
27412762
1 | 1001
27422763
(2 rows)
27432764

2765+
RESET enable_indexscan;
27442766
--
27452767
-- Check elimination of constant-NULL subexpressions
27462768
--

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -916,6 +916,19 @@ SELECT thousand, tenthous FROM tenk1
916916
WHERE thousand<2AND tenthousIN (1001,3000)
917917
ORDER BY thousand;
918918

919+
SET enable_indexonlyscan= OFF;
920+
921+
explain (costs off)
922+
SELECT thousand, tenthousFROM tenk1
923+
WHERE thousand<2AND tenthousIN (1001,3000)
924+
ORDER BY thousand;
925+
926+
SELECT thousand, tenthousFROM tenk1
927+
WHERE thousand<2AND tenthousIN (1001,3000)
928+
ORDER BY thousand;
929+
930+
RESET enable_indexscan;
931+
919932
--
920933
-- Check elimination of constant-NULL subexpressions
921934
--

0 commit comments

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