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

Commit4586572

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 parentd440c4b commit4586572

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
{
@@ -111,7 +103,9 @@ static void get_index_paths(PlannerInfo *root, RelOptInfo *rel,
111103
staticList*build_index_paths(PlannerInfo*root,RelOptInfo*rel,
112104
IndexOptInfo*index,IndexClauseSet*clauses,
113105
booluseful_predicate,
114-
SaOpControlsaop_control,ScanTypeControlscantype);
106+
ScanTypeControlscantype,
107+
bool*skip_nonnative_saop,
108+
bool*skip_lower_saop);
115109
staticList*build_paths_for_OR(PlannerInfo*root,RelOptInfo*rel,
116110
List*clauses,List*other_clauses);
117111
staticList*drop_indexable_join_clauses(RelOptInfo*rel,List*clauses);
@@ -706,23 +700,47 @@ bms_equal_any(Relids relids, List *relids_list)
706700
* index AM supports them natively, we should just include them in simple
707701
* index paths. If not, we should exclude them while building simple index
708702
* paths, and then make a separate attempt to include them in bitmap paths.
703+
* Furthermore, we should consider excluding lower-order ScalarArrayOpExpr
704+
* quals so as to create ordered paths.
709705
*/
710706
staticvoid
711707
get_index_paths(PlannerInfo*root,RelOptInfo*rel,
712708
IndexOptInfo*index,IndexClauseSet*clauses,
713709
List**bitindexpaths)
714710
{
715711
List*indexpaths;
712+
boolskip_nonnative_saop= false;
713+
boolskip_lower_saop= false;
716714
ListCell*lc;
717715

718716
/*
719717
* Build simple index paths using the clauses. Allow ScalarArrayOpExpr
720-
* clauses only if the index AM supports them natively.
718+
* clauses only if the index AM supports them natively, and skip any such
719+
* clauses for index columns after the first (so that we produce ordered
720+
* paths if possible).
721721
*/
722722
indexpaths=build_index_paths(root,rel,
723723
index,clauses,
724724
index->predOK,
725-
SAOP_PER_AM,ST_ANYSCAN);
725+
ST_ANYSCAN,
726+
&skip_nonnative_saop,
727+
&skip_lower_saop);
728+
729+
/*
730+
* If we skipped any lower-order ScalarArrayOpExprs on an index with an AM
731+
* that supports them, then try again including those clauses. This will
732+
* produce paths with more selectivity but no ordering.
733+
*/
734+
if (skip_lower_saop)
735+
{
736+
indexpaths=list_concat(indexpaths,
737+
build_index_paths(root,rel,
738+
index,clauses,
739+
index->predOK,
740+
ST_ANYSCAN,
741+
&skip_nonnative_saop,
742+
NULL));
743+
}
726744

727745
/*
728746
* Submit all the ones that can form plain IndexScan plans to add_path. (A
@@ -750,16 +768,18 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
750768
}
751769

752770
/*
753-
* If the indexdoesn't handle ScalarArrayOpExpr clauses natively, check
754-
*to see if there are any such clauses, and if sogenerate bitmap scan
755-
*paths relying on executor-managedScalarArrayOpExpr.
771+
* Ifthere were ScalarArrayOpExpr clauses thatthe indexcan't handle
772+
*natively,generate bitmap scan paths relying on executor-managed
773+
* ScalarArrayOpExpr.
756774
*/
757-
if (!index->amsearcharray)
775+
if (skip_nonnative_saop)
758776
{
759777
indexpaths=build_index_paths(root,rel,
760778
index,clauses,
761779
false,
762-
SAOP_REQUIRE,ST_BITMAPSCAN);
780+
ST_BITMAPSCAN,
781+
NULL,
782+
NULL);
763783
*bitindexpaths=list_concat(*bitindexpaths,indexpaths);
764784
}
765785
}
@@ -782,26 +802,36 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
782802
* Note that this routine should never be called at all if the index has an
783803
* unprovable predicate.
784804
*
785-
* saop_control indicates whether ScalarArrayOpExpr clauses can be used.
786-
* When it's SAOP_REQUIRE, index paths are created only if we found at least
787-
* one ScalarArrayOpExpr clause.
788-
*
789805
* scantype indicates whether we want to create plain indexscans, bitmap
790806
* indexscans, or both. When it's ST_BITMAPSCAN, we will not consider
791807
* index ordering while deciding if a Path is worth generating.
792808
*
809+
* If skip_nonnative_saop is non-NULL, we ignore ScalarArrayOpExpr clauses
810+
* unless the index AM supports them directly, and we set *skip_nonnative_saop
811+
* to TRUE if we found any such clauses (caller must initialize the variable
812+
* to FALSE). If it's NULL, we do not ignore ScalarArrayOpExpr clauses.
813+
*
814+
* If skip_lower_saop is non-NULL, we ignore ScalarArrayOpExpr clauses for
815+
* non-first index columns, and we set *skip_lower_saop to TRUE if we found
816+
* any such clauses (caller must initialize the variable to FALSE). If it's
817+
* NULL, we do not ignore non-first ScalarArrayOpExpr clauses, but they will
818+
* result in considering the scan's output to be unordered.
819+
*
793820
* 'rel' is the index's heap relation
794821
* 'index' is the index for which we want to generate paths
795822
* 'clauses' is the collection of indexable clauses (RestrictInfo nodes)
796823
* 'useful_predicate' indicates whether the index has a useful predicate
797-
* 'saop_control' indicates whether ScalarArrayOpExpr clauses can be used
798824
* 'scantype' indicates whether we need plain or bitmap scan support
825+
* 'skip_nonnative_saop' indicates whether to accept SAOP if index AM doesn't
826+
* 'skip_lower_saop' indicates whether to accept non-first-column SAOP
799827
*/
800828
staticList*
801829
build_index_paths(PlannerInfo*root,RelOptInfo*rel,
802830
IndexOptInfo*index,IndexClauseSet*clauses,
803831
booluseful_predicate,
804-
SaOpControlsaop_control,ScanTypeControlscantype)
832+
ScanTypeControlscantype,
833+
bool*skip_nonnative_saop,
834+
bool*skip_lower_saop)
805835
{
806836
List*result=NIL;
807837
IndexPath*ipath;
@@ -813,7 +843,6 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
813843
List*orderbyclausecols;
814844
List*index_pathkeys;
815845
List*useful_pathkeys;
816-
boolfound_clause;
817846
boolfound_lower_saop_clause;
818847
boolpathkeys_possibly_useful;
819848
boolindex_is_ordered;
@@ -848,11 +877,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
848877
* (This order is depended on by btree and possibly other places.)The
849878
* lists can be empty, if the index AM allows that.
850879
*
851-
* found_clause is set true only if there's at least one index clause; and
852-
* if saop_control is SAOP_REQUIRE, it has to be a ScalarArrayOpExpr
853-
* clause.
854-
*
855-
* found_lower_saop_clause is set true if there's a ScalarArrayOpExpr
880+
* found_lower_saop_clause is set true if we accept a ScalarArrayOpExpr
856881
* index clause for a non-first index column. This prevents us from
857882
* assuming that the scan result is ordered. (Actually, the result is
858883
* still ordered if there are equality constraints for all earlier
@@ -864,7 +889,6 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
864889
*/
865890
index_clauses=NIL;
866891
clause_columns=NIL;
867-
found_clause= false;
868892
found_lower_saop_clause= false;
869893
outer_relids=NULL;
870894
for (indexcol=0;indexcol<index->ncolumns;indexcol++)
@@ -877,17 +901,27 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
877901

878902
if (IsA(rinfo->clause,ScalarArrayOpExpr))
879903
{
880-
/* Ignore if not supported by index */
881-
if (saop_control==SAOP_PER_AM&& !index->amsearcharray)
882-
continue;
883-
found_clause= true;
904+
if (!index->amsearcharray)
905+
{
906+
if (skip_nonnative_saop)
907+
{
908+
/* Ignore because not supported by index */
909+
*skip_nonnative_saop= true;
910+
continue;
911+
}
912+
/* Caller had better intend this only for bitmap scan */
913+
Assert(scantype==ST_BITMAPSCAN);
914+
}
884915
if (indexcol>0)
916+
{
917+
if (skip_lower_saop)
918+
{
919+
/* Caller doesn't want to lose index ordering */
920+
*skip_lower_saop= true;
921+
continue;
922+
}
885923
found_lower_saop_clause= true;
886-
}
887-
else
888-
{
889-
if (saop_control!=SAOP_REQUIRE)
890-
found_clause= true;
924+
}
891925
}
892926
index_clauses=lappend(index_clauses,rinfo);
893927
clause_columns=lappend_int(clause_columns,indexcol);
@@ -967,7 +1001,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
9671001
* later merging or final output ordering, OR the index has a useful
9681002
* predicate, OR an index-only scan is possible.
9691003
*/
970-
if (found_clause||useful_pathkeys!=NIL||useful_predicate||
1004+
if (index_clauses!=NIL||useful_pathkeys!=NIL||useful_predicate||
9711005
index_only_scan)
9721006
{
9731007
ipath=create_index_path(root,index,
@@ -1116,7 +1150,9 @@ build_paths_for_OR(PlannerInfo *root, RelOptInfo *rel,
11161150
indexpaths=build_index_paths(root,rel,
11171151
index,&clauseset,
11181152
useful_predicate,
1119-
SAOP_ALLOW,ST_BITMAPSCAN);
1153+
ST_BITMAPSCAN,
1154+
NULL,
1155+
NULL);
11201156
result=list_concat(result,indexpaths);
11211157
}
11221158

‎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