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

Commita4523c5

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 parent17009fb commita4523c5

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*generate_bitmap_or_paths(PlannerInfo*root,RelOptInfo*rel,
@@ -726,23 +720,47 @@ bms_equal_any(Relids relids, List *relids_list)
726720
* index AM supports them natively, we should just include them in simple
727721
* index paths. If not, we should exclude them while building simple index
728722
* paths, and then make a separate attempt to include them in bitmap paths.
723+
* Furthermore, we should consider excluding lower-order ScalarArrayOpExpr
724+
* quals so as to create ordered paths.
729725
*/
730726
staticvoid
731727
get_index_paths(PlannerInfo*root,RelOptInfo*rel,
732728
IndexOptInfo*index,IndexClauseSet*clauses,
733729
List**bitindexpaths)
734730
{
735731
List*indexpaths;
732+
boolskip_nonnative_saop= false;
733+
boolskip_lower_saop= false;
736734
ListCell*lc;
737735

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

747765
/*
748766
* Submit all the ones that can form plain IndexScan plans to add_path. (A
@@ -770,16 +788,18 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
770788
}
771789

772790
/*
773-
* If the indexdoesn't handle ScalarArrayOpExpr clauses natively, check
774-
*to see if there are any such clauses, and if sogenerate bitmap scan
775-
*paths relying on executor-managedScalarArrayOpExpr.
791+
* Ifthere were ScalarArrayOpExpr clauses thatthe indexcan't handle
792+
*natively,generate bitmap scan paths relying on executor-managed
793+
* ScalarArrayOpExpr.
776794
*/
777-
if (!index->amsearcharray)
795+
if (skip_nonnative_saop)
778796
{
779797
indexpaths=build_index_paths(root,rel,
780798
index,clauses,
781799
false,
782-
SAOP_REQUIRE,ST_BITMAPSCAN);
800+
ST_BITMAPSCAN,
801+
NULL,
802+
NULL);
783803
*bitindexpaths=list_concat(*bitindexpaths,indexpaths);
784804
}
785805
}
@@ -802,26 +822,36 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
802822
* Note that this routine should never be called at all if the index has an
803823
* unprovable predicate.
804824
*
805-
* saop_control indicates whether ScalarArrayOpExpr clauses can be used.
806-
* When it's SAOP_REQUIRE, index paths are created only if we found at least
807-
* one ScalarArrayOpExpr clause.
808-
*
809825
* scantype indicates whether we want to create plain indexscans, bitmap
810826
* indexscans, or both. When it's ST_BITMAPSCAN, we will not consider
811827
* index ordering while deciding if a Path is worth generating.
812828
*
829+
* If skip_nonnative_saop is non-NULL, we ignore ScalarArrayOpExpr clauses
830+
* unless the index AM supports them directly, and we set *skip_nonnative_saop
831+
* to TRUE if we found any such clauses (caller must initialize the variable
832+
* to FALSE). If it's NULL, we do not ignore ScalarArrayOpExpr clauses.
833+
*
834+
* If skip_lower_saop is non-NULL, we ignore ScalarArrayOpExpr clauses for
835+
* non-first index columns, and we set *skip_lower_saop to TRUE if we found
836+
* any such clauses (caller must initialize the variable to FALSE). If it's
837+
* NULL, we do not ignore non-first ScalarArrayOpExpr clauses, but they will
838+
* result in considering the scan's output to be unordered.
839+
*
813840
* 'rel' is the index's heap relation
814841
* 'index' is the index for which we want to generate paths
815842
* 'clauses' is the collection of indexable clauses (RestrictInfo nodes)
816843
* 'useful_predicate' indicates whether the index has a useful predicate
817-
* 'saop_control' indicates whether ScalarArrayOpExpr clauses can be used
818844
* 'scantype' indicates whether we need plain or bitmap scan support
845+
* 'skip_nonnative_saop' indicates whether to accept SAOP if index AM doesn't
846+
* 'skip_lower_saop' indicates whether to accept non-first-column SAOP
819847
*/
820848
staticList*
821849
build_index_paths(PlannerInfo*root,RelOptInfo*rel,
822850
IndexOptInfo*index,IndexClauseSet*clauses,
823851
booluseful_predicate,
824-
SaOpControlsaop_control,ScanTypeControlscantype)
852+
ScanTypeControlscantype,
853+
bool*skip_nonnative_saop,
854+
bool*skip_lower_saop)
825855
{
826856
List*result=NIL;
827857
IndexPath*ipath;
@@ -833,7 +863,6 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
833863
List*orderbyclausecols;
834864
List*index_pathkeys;
835865
List*useful_pathkeys;
836-
boolfound_clause;
837866
boolfound_lower_saop_clause;
838867
boolpathkeys_possibly_useful;
839868
boolindex_is_ordered;
@@ -868,11 +897,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
868897
* (This order is depended on by btree and possibly other places.)The
869898
* lists can be empty, if the index AM allows that.
870899
*
871-
* found_clause is set true only if there's at least one index clause; and
872-
* if saop_control is SAOP_REQUIRE, it has to be a ScalarArrayOpExpr
873-
* clause.
874-
*
875-
* found_lower_saop_clause is set true if there's a ScalarArrayOpExpr
900+
* found_lower_saop_clause is set true if we accept a ScalarArrayOpExpr
876901
* index clause for a non-first index column. This prevents us from
877902
* assuming that the scan result is ordered. (Actually, the result is
878903
* still ordered if there are equality constraints for all earlier
@@ -885,7 +910,6 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
885910
*/
886911
index_clauses=NIL;
887912
clause_columns=NIL;
888-
found_clause= false;
889913
found_lower_saop_clause= false;
890914
outer_relids=bms_copy(rel->lateral_relids);
891915
for (indexcol=0;indexcol<index->ncolumns;indexcol++)
@@ -898,17 +922,27 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
898922

899923
if (IsA(rinfo->clause,ScalarArrayOpExpr))
900924
{
901-
/* Ignore if not supported by index */
902-
if (saop_control==SAOP_PER_AM&& !index->amsearcharray)
903-
continue;
904-
found_clause= true;
925+
if (!index->amsearcharray)
926+
{
927+
if (skip_nonnative_saop)
928+
{
929+
/* Ignore because not supported by index */
930+
*skip_nonnative_saop= true;
931+
continue;
932+
}
933+
/* Caller had better intend this only for bitmap scan */
934+
Assert(scantype==ST_BITMAPSCAN);
935+
}
905936
if (indexcol>0)
937+
{
938+
if (skip_lower_saop)
939+
{
940+
/* Caller doesn't want to lose index ordering */
941+
*skip_lower_saop= true;
942+
continue;
943+
}
906944
found_lower_saop_clause= true;
907-
}
908-
else
909-
{
910-
if (saop_control!=SAOP_REQUIRE)
911-
found_clause= true;
945+
}
912946
}
913947
index_clauses=lappend(index_clauses,rinfo);
914948
clause_columns=lappend_int(clause_columns,indexcol);
@@ -988,7 +1022,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
9881022
* later merging or final output ordering, OR the index has a useful
9891023
* predicate, OR an index-only scan is possible.
9901024
*/
991-
if (found_clause||useful_pathkeys!=NIL||useful_predicate||
1025+
if (index_clauses!=NIL||useful_pathkeys!=NIL||useful_predicate||
9921026
index_only_scan)
9931027
{
9941028
ipath=create_index_path(root,index,
@@ -1137,7 +1171,9 @@ build_paths_for_OR(PlannerInfo *root, RelOptInfo *rel,
11371171
indexpaths=build_index_paths(root,rel,
11381172
index,&clauseset,
11391173
useful_predicate,
1140-
SAOP_ALLOW,ST_BITMAPSCAN);
1174+
ST_BITMAPSCAN,
1175+
NULL,
1176+
NULL);
11411177
result=list_concat(result,indexpaths);
11421178
}
11431179

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

Lines changed: 23 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2754,6 +2754,27 @@ ORDER BY unique1;
27542754
42
27552755
(3 rows)
27562756

2757+
explain (costs off)
2758+
SELECT thousand, tenthous FROM tenk1
2759+
WHERE thousand < 2 AND tenthous IN (1001,3000)
2760+
ORDER BY thousand;
2761+
QUERY PLAN
2762+
-------------------------------------------------------
2763+
Index Only Scan using tenk1_thous_tenthous on tenk1
2764+
Index Cond: (thousand < 2)
2765+
Filter: (tenthous = ANY ('{1001,3000}'::integer[]))
2766+
(3 rows)
2767+
2768+
SELECT thousand, tenthous FROM tenk1
2769+
WHERE thousand < 2 AND tenthous IN (1001,3000)
2770+
ORDER BY thousand;
2771+
thousand | tenthous
2772+
----------+----------
2773+
0 | 3000
2774+
1 | 1001
2775+
(2 rows)
2776+
2777+
SET enable_indexonlyscan = OFF;
27572778
explain (costs off)
27582779
SELECT thousand, tenthous FROM tenk1
27592780
WHERE thousand < 2 AND tenthous IN (1001,3000)
@@ -2762,7 +2783,7 @@ ORDER BY thousand;
27622783
--------------------------------------------------------------------------------------
27632784
Sort
27642785
Sort Key: thousand
2765-
-> IndexOnlyScan using tenk1_thous_tenthous on tenk1
2786+
-> Index Scan using tenk1_thous_tenthous on tenk1
27662787
Index Cond: ((thousand < 2) AND (tenthous = ANY ('{1001,3000}'::integer[])))
27672788
(4 rows)
27682789

@@ -2775,6 +2796,7 @@ ORDER BY thousand;
27752796
1 | 1001
27762797
(2 rows)
27772798

2799+
RESET enable_indexscan;
27782800
--
27792801
-- Check elimination of constant-NULL subexpressions
27802802
--

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

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

935+
SET enable_indexonlyscan= OFF;
936+
937+
explain (costs off)
938+
SELECT thousand, tenthousFROM tenk1
939+
WHERE thousand<2AND tenthousIN (1001,3000)
940+
ORDER BY thousand;
941+
942+
SELECT thousand, tenthousFROM tenk1
943+
WHERE thousand<2AND tenthousIN (1001,3000)
944+
ORDER BY thousand;
945+
946+
RESET enable_indexscan;
947+
935948
--
936949
-- Check elimination of constant-NULL subexpressions
937950
--

0 commit comments

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