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

Commit0777f7a

Browse files
committed
Fix matching of boolean index columns to sort ordering.
Normally, if we have a WHERE clause like "indexcol = constant",the planner will figure out that that index column can be ignoredwhen determining whether the index has a desired sort ordering.But this failed to work for boolean index columns, because acondition like "boolcol = true" is canonicalized to just "boolcol"which does not give rise to an EquivalenceClass. Add a check toallow the same type of deduction to be made in this case too.Per a complaint from Dima Pavlov. Arguably this is a bug, but given thelimited impact and the small number of complaints so far, I won't riskdestabilizing plans in stable branches by back-patching.Patch by me, reviewed by Michael PaquierDiscussion:https://postgr.es/m/1788.1481605684@sss.pgh.pa.us
1 parent83f2061 commit0777f7a

File tree

5 files changed

+129
-11
lines changed

5 files changed

+129
-11
lines changed

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

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3025,6 +3025,52 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
30253025
return false;
30263026
}
30273027

3028+
/*
3029+
* indexcol_is_bool_constant_for_query
3030+
*
3031+
* If an index column is constrained to have a constant value by the query's
3032+
* WHERE conditions, then it's irrelevant for sort-order considerations.
3033+
* Usually that means we have a restriction clause WHERE indexcol = constant,
3034+
* which gets turned into an EquivalenceClass containing a constant, which
3035+
* is recognized as redundant by build_index_pathkeys(). But if the index
3036+
* column is a boolean variable (or expression), then we are not going to
3037+
* see WHERE indexcol = constant, because expression preprocessing will have
3038+
* simplified that to "WHERE indexcol" or "WHERE NOT indexcol". So we are not
3039+
* going to have a matching EquivalenceClass (unless the query also contains
3040+
* "ORDER BY indexcol"). To allow such cases to work the same as they would
3041+
* for non-boolean values, this function is provided to detect whether the
3042+
* specified index column matches a boolean restriction clause.
3043+
*/
3044+
bool
3045+
indexcol_is_bool_constant_for_query(IndexOptInfo*index,intindexcol)
3046+
{
3047+
ListCell*lc;
3048+
3049+
/* If the index isn't boolean, we can't possibly get a match */
3050+
if (!IsBooleanOpfamily(index->opfamily[indexcol]))
3051+
return false;
3052+
3053+
/* Check each restriction clause for the index's rel */
3054+
foreach(lc,index->rel->baserestrictinfo)
3055+
{
3056+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lc);
3057+
3058+
/*
3059+
* As in match_clause_to_indexcol, never match pseudoconstants to
3060+
* indexes. (It might be semantically okay to do so here, but the
3061+
* odds of getting a match are negligible, so don't waste the cycles.)
3062+
*/
3063+
if (rinfo->pseudoconstant)
3064+
continue;
3065+
3066+
/* See if we can match the clause's expression to the index column */
3067+
if (match_boolean_index_clause((Node*)rinfo->clause,indexcol,index))
3068+
return true;
3069+
}
3070+
3071+
return false;
3072+
}
3073+
30283074

30293075
/****************************************************************************
30303076
*---- ROUTINES TO CHECK OPERANDS ----

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

Lines changed: 24 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -480,17 +480,30 @@ build_index_pathkeys(PlannerInfo *root,
480480
index->rel->relids,
481481
false);
482482

483-
/*
484-
* If the sort key isn't already present in any EquivalenceClass, then
485-
* it's not an interesting sort order for this query. So we can stop
486-
* now --- lower-order sort keys aren't useful either.
487-
*/
488-
if (!cpathkey)
489-
break;
490-
491-
/* Add to list unless redundant */
492-
if (!pathkey_is_redundant(cpathkey,retval))
493-
retval=lappend(retval,cpathkey);
483+
if (cpathkey)
484+
{
485+
/*
486+
* We found the sort key in an EquivalenceClass, so it's relevant
487+
* for this query. Add it to list, unless it's redundant.
488+
*/
489+
if (!pathkey_is_redundant(cpathkey,retval))
490+
retval=lappend(retval,cpathkey);
491+
}
492+
else
493+
{
494+
/*
495+
* Boolean index keys might be redundant even if they do not
496+
* appear in an EquivalenceClass, because of our special treatment
497+
* of boolean equality conditions --- see the comment for
498+
* indexcol_is_bool_constant_for_query(). If that applies, we can
499+
* continue to examine lower-order index columns. Otherwise, the
500+
* sort key is not an interesting sort order for this query, so we
501+
* should stop considering index columns; any lower-order sort
502+
* keys won't be useful either.
503+
*/
504+
if (!indexcol_is_bool_constant_for_query(index,i))
505+
break;
506+
}
494507

495508
i++;
496509
}

‎src/include/optimizer/paths.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -66,6 +66,8 @@ extern void create_index_paths(PlannerInfo *root, RelOptInfo *rel);
6666
externboolrelation_has_unique_index_for(PlannerInfo*root,RelOptInfo*rel,
6767
List*restrictlist,
6868
List*exprlist,List*oprlist);
69+
externboolindexcol_is_bool_constant_for_query(IndexOptInfo*index,
70+
intindexcol);
6971
externboolmatch_index_to_operand(Node*operand,intindexcol,
7072
IndexOptInfo*index);
7173
externvoidexpand_indexqual_conditions(IndexOptInfo*index,

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

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2952,6 +2952,48 @@ explain (costs off)
29522952
Index Cond: ((thousand = 1) AND (tenthous = 1001))
29532953
(2 rows)
29542954

2955+
--
2956+
-- Check matching of boolean index columns to WHERE conditions and sort keys
2957+
--
2958+
create temp table boolindex (b bool, i int, unique(b, i), junk float);
2959+
explain (costs off)
2960+
select * from boolindex order by b, i limit 10;
2961+
QUERY PLAN
2962+
-------------------------------------------------------
2963+
Limit
2964+
-> Index Scan using boolindex_b_i_key on boolindex
2965+
(2 rows)
2966+
2967+
explain (costs off)
2968+
select * from boolindex where b order by i limit 10;
2969+
QUERY PLAN
2970+
-------------------------------------------------------
2971+
Limit
2972+
-> Index Scan using boolindex_b_i_key on boolindex
2973+
Index Cond: (b = true)
2974+
Filter: b
2975+
(4 rows)
2976+
2977+
explain (costs off)
2978+
select * from boolindex where b = true order by i desc limit 10;
2979+
QUERY PLAN
2980+
----------------------------------------------------------------
2981+
Limit
2982+
-> Index Scan Backward using boolindex_b_i_key on boolindex
2983+
Index Cond: (b = true)
2984+
Filter: b
2985+
(4 rows)
2986+
2987+
explain (costs off)
2988+
select * from boolindex where not b order by i limit 10;
2989+
QUERY PLAN
2990+
-------------------------------------------------------
2991+
Limit
2992+
-> Index Scan using boolindex_b_i_key on boolindex
2993+
Index Cond: (b = false)
2994+
Filter: (NOT b)
2995+
(4 rows)
2996+
29552997
--
29562998
-- REINDEX (VERBOSE)
29572999
--

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1010,6 +1010,21 @@ RESET enable_indexscan;
10101010
explain (costs off)
10111011
select*from tenk1where (thousand, tenthous)in ((1,1001), (null,null));
10121012

1013+
--
1014+
-- Check matching of boolean index columns to WHERE conditions and sort keys
1015+
--
1016+
1017+
create temp table boolindex (b bool, iint, unique(b, i), junk float);
1018+
1019+
explain (costs off)
1020+
select*from boolindexorder by b, ilimit10;
1021+
explain (costs off)
1022+
select*from boolindexwhere border by ilimit10;
1023+
explain (costs off)
1024+
select*from boolindexwhere b= trueorder by idesclimit10;
1025+
explain (costs off)
1026+
select*from boolindexwhere not border by ilimit10;
1027+
10131028
--
10141029
-- REINDEX (VERBOSE)
10151030
--

0 commit comments

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