10000 Fix matching of boolean index columns to sort ordering. · postgrespro/postgres_cluster@0777f7a · GitHub
[go: up one dir, main page]

Skip to content
< 8000 style data-styled="true" data-styled-version="5.3.11">.hEHvLI{min-width:0;-webkit-align-items:center;-webkit-box-align:center;-ms-flex-align:center;align-items:center;}/*!sc*/ .bmcJak{min-width:0;}/*!sc*/ .fyKNMY[data-size="medium"]{color:var(--fgColor-default,var(--color-fg-default,#1F2328));}/*!sc*/ .gUkoLg{-webkit-box-pack:center;-webkit-justify-content:center;-ms-flex-pack:center;justify-content:center;}/*!sc*/ .dpBUfI{display:-webkit-box;display:-webkit-flex;display:-ms-flexbox;display:flex;-webkit-flex-direction:row;-ms-flex-direction:row;flex-direction:row;-webkit-flex-wrap:wrap;-ms-flex-wrap:wrap;flex-wrap:wrap;-webkit-align-items:center;-webkit-box-align:center;-ms-flex-align:center;align-items:center;}/*!sc*/ @media screen and (min-width:544px){.dpBUfI{-webkit-flex-wrap:wrap;-ms-flex-wrap:wrap;flex-wrap:wrap;}}/*!sc*/ @media screen and (min-width:768px){.dpBUfI{-webkit-flex-wrap:wrap;-ms-flex-wrap:wrap;flex-wrap:wrap;}}/*!sc*/ @media screen and (min-width:1012px){.dpBUfI{-webkit-flex-wrap:nowrap;-ms-flex-wrap:nowrap;flex-wrap:nowrap;}}/*!sc*/ .hKWjvQ{display:-webkit-box;display:-webkit-flex;display:-ms-flexbox;display:flex;-webkit-flex-direction:row;-ms-flex-direction:row;flex-direction:row;-webkit-align-items:center;-webkit-box-align:center;-ms-flex-align:center;align-items:center;}/*!sc*/ .cvdqJW{width:20px;height:20px;margin-right:8px;margin-top:-1px;margin-left:1px;}/*!sc*/ .dkaFxu{font-weight:600;white-space:nowrap;color:var(--fgColor-default,var(--color-fg-default,#1F2328));}/*!sc*/ .dkaFxu:hover{color:var(--fgColor-default,var(--color-fg-default,#1F2328));-webkit-text-decoration:underline;text-decoration:underline;}/*!sc*/ .irPhWZ{width:60px;}/*!sc*/ .dNbsEP{width:62px;}/*!sc*/ .kHfwUD{width:60px;height:22px;}/*!sc*/ .bHLmSv{position:absolute;inset:0 -2px;cursor:col-resize;background-color:transparent;-webkit-transition-delay:0.1s;transition-delay:0.1s;}/*!sc*/ .bHLmSv:hover{background-color:var(--bgColor-neutral-muted,var(--color-neutral-muted,rgba(175,184,193,0.2)));}/*!sc*/ .hqtbbn{bottom:0 !important;-webkit-clip:rect(1px,1px,1px,1px);clip:rect(1px,1px,1px,1px);-webkit-clip-path:inset(50%);clip-path:inset(50%);height:84px;position:absolute;width:320px;}/*!sc*/ data-styled.g1[id="Box-sc-g0xbh4-0"]{content:"hEHvLI,bmcJak,fyKNMY,gUkoLg,dpBUfI,hKWjvQ,cvdqJW,dkaFxu,irPhWZ,dNbsEP,kHfwUD,bHLmSv,hqtbbn,"}/*!sc*/ .brGdpi{position:absolute;width:1px;height:1px;padding:0;margin:-1px;overflow:hidden;-webkit-clip:rect(0,0,0,0);clip:rect(0,0,0,0);white-space:nowrap;border-width:0;}/*!sc*/ data-styled.g3[id="_VisuallyHidden__VisuallyHidden-sc-11jhm7a-0"]{content:"brGdpi,"}/*!sc*/ .jjwhNb{position:relative;display:inline-block;display:-webkit-box;display:-webkit-flex;display:-ms-flexbox;display:flex;}/*!sc*/ .jjwhNb::after{position:absolute;z-index:1000000;display:none;padding:0.5em 0.75em;font:normal normal 11px/1.5 -apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";-webkit-font-smoothing:subpixel-antialiased;color:var(--tooltip-fgColor,var(--fgColor-onEmphasis,var(--color-fg-on-emphasis,#ffffff)));text-align:center;-webkit-text-decoration:none;text-decoration:none;text-shadow:none;text-transform:none;-webkit-letter-spacing:normal;-moz-letter-spacing:normal;-ms-letter-spacing:normal;letter-spacing:normal;word-wrap:break-word;white-space:pre;pointer-events:none;content:attr(aria-label);background:var(--tooltip-bgColor,var(--bgColor-emphasis,var(--color-neutral-emphasis-plus,#24292f)));border-radius:6px;opacity:0;}/*!sc*/ @-webkit-keyframes tooltip-appear{from{opacity:0;}to{opacity:1;}}/*!sc*/ @keyframes tooltip-appear{from{opacity:0;}to{opacity:1;}}/*!sc*/ .jjwhNb:hover::after,.jjwhNb:active::after,.jjwhNb:focus::after,.jjwhNb:focus-within::after{display:inline-block;-webkit-text-decoration:none;text-decoration:none;-webkit-animation-name:tooltip-appear;animation-name:tooltip-appear;-webkit-animation-duration:0.1s;animation-duration:0.1s;-webkit-animation-fill-mode:forwards;animation-fill-mode:forwards;-webkit-animation-timing-function:ease-in;animation-timing-function:ease-in;-webkit-animation-delay:0s;animation-delay:0s;}/*!sc*/ .jjwhNb.tooltipped-no-delay:hover::after,.jjwhNb.tooltipped-no-delay:active::after,.jjwhNb.tooltipped-no-delay:focus::after,.jjwhNb.tooltipped-no-delay:focus-within::after{-webkit-animation-delay:0s;animation-delay:0s;}/*!sc*/ .jjwhNb.tooltipped-multiline:hover::after,.jjwhNb.tooltipped-multiline:active::after,.jjwhNb.tooltipped-multiline:focus::after,.jjwhNb.tooltipped-multiline:focus-within::after{display:table-cell;}/*!sc*/ .jjwhNb.tooltipped-s::after,.jjwhNb.tooltipped-se::after,.jjwhNb.tooltipped-sw::after{top:100%;right:50%;margin-top:6px;}/*!sc*/ .jjwhNb.tooltipped-se::after{right:auto;left:50%;margin-left:-16px;}/*!sc*/ .jjwhNb.tooltipped-sw::after{margin-right:-16px;}/*!sc*/ .jjwhNb.tooltipped-n::after,.jjwhNb.tooltipped-ne::after,.jjwhNb.tooltipped-nw::after{right:50%;bottom:100%;margin-bottom:6px;}/*!sc*/ .jjwhNb.tooltipped-ne::after{right:auto;left:50%;margin-left:-16px;}/*!sc*/ .jjwhNb.tooltipped-nw::after{margin-right:-16px;}/*!sc*/ .jjwhNb.tooltipped-s::after,.jjwhNb.tooltipped-n::after{-webkit-transform:translateX(50%);-ms-transform:translateX(50%);transform:translateX(50%);}/*!sc*/ .jjwhNb.tooltipped-w::after{right:100%;bottom:50%;margin-right:6px;-webkit-transform:translateY(50%);-ms-transform:translateY(50%);transform:translateY(50%);}/*!sc*/ .jjwhNb.tooltipped-e::after{bottom:50%;left:100%;margin-left:6px;-webkit-transform:translateY(50%);-ms-transform:translateY(50%);transform:translateY(50%);}/*!sc*/ .jjwhNb.tooltipped-multiline::after{width:-webkit-max-content;width:-moz-max-content;width:max-content;max-width:250px;word-wrap:break-word;white-space:pre-line;border-collapse:separate;}/*!sc*/ .jjwhNb.tooltipped-multiline.tooltipped-s::after,.jjwhNb.tooltipped-multiline.tooltipped-n::after{right:auto;left:50%;-webkit-transform:translateX(-50%);-ms-transform:translateX(-50%);transform:translateX(-50%);}/*!sc*/ .jjwhNb.tooltipped-multiline.tooltipped-w::after,.jjwhNb.tooltipped-multiline.tooltipped-e::after{right:100%;}/*!sc*/ .jjwhNb.tooltipped-align-right-2::after{right:0;margin-right:0;}/*!sc*/ .jjwhNb.tooltipped-align-left-2::after{left:0;margin-left:0;}/*!sc*/ data-styled.g6[id="Tooltip__TooltipBase-sc-17tf59c-0"]{content:"jjwhNb,"}/*!sc*/ .irithh{position:relative;overflow:hidden;-webkit-mask-image:radial-gradient(white,black);mask-image:radial-gradient(white,black);background-color:var(--bgColor-neutral-muted,var(--color-neutral-subtle,rgba(234,238,242,0.5)));border-radius:3px;display:block;height:1.2em;width:60px;}/*!sc*/ .irithh::after{-webkit-animation:crVFvv 1.5s infinite linear;animation:crVFvv 1.5s infinite linear;background:linear-gradient(90deg,transparent,var(--bgColor-neutral-muted,var(--color-neutral-subtle,rgba(234,238,242,0.5))),transparent);content:'';position:absolute;-webkit-transform:translateX(-100%);-ms-transform:translateX(-100%);transform:translateX(-100%);bottom:0;left:0;right:0;top:0;}/*!sc*/ .ihfxfT{position:relative;overflow:hidden;-webkit-mask-image:radial-gradient(white,black);mask-image:radial-gradient(white,black);background-color:var(--bgColor-neutral-muted,var(--color-neutral-subtle,rgba(234,238,242,0.5)));border-radius:3px;display:block;height:1.2em;width:62px;}/*!sc*/ .ihfxfT::after{-webkit-animation:crVFvv 1.5s infinite linear;animation:crVFvv 1.5s infinite linear;background:linear-gradient(90deg,transparent,var(--bgColor-neutral-muted,var(--color-neutral-subtle,rgba(234,238,242,0.5))),transparent);content:'';position:absolute;-webkit-transform:translateX(-100%);-ms-transform:translateX(-100%);transform:translateX(-100%);bottom:0;left:0;right:0;top:0;}/*!sc*/ .kRBfod{position:relative;overflow:hidden;-webkit-mask-image:radial-gradient(white,black);mask-image:radial-gradient(white,black);background-color:var(--bgColor-neutral-muted,var(--color-neutral-subtle,rgba(234,238,242,0.5)));border-radius:3px;display:block;height:1.2em;width:60px;height:22px;}/*!sc*/ .kRBfod::after{-webkit-animation:crVFvv 1.5s infinite linear;animation:crVFvv 1.5s infinite linear;background:linear-gradient(90deg,transparent,var(--bgColor-neutral-muted,var(--color-neutral-subtle,rgba(234,238,242,0.5))),transparent);content:'';position:absolute;-webkit-transform:translateX(-100%);-ms-transform:translateX(-100%);transform:translateX(-100%);bottom:0;left:0;right:0;top:0;}/*!sc*/ data-styled.g27[id="LoadingSkeleton-sc-695d630a-0"]{content:"irithh,ihfxfT,kRBfod,"}/*!sc*/ @-webkit-keyframes crVFvv{0%{-webkit-transform:translateX(-100%);-ms-transform:translateX(-100%);transform:translateX(-100%);}50%{-webkit-transform:translateX(100%);-ms-transform:translateX(100%);transform:translateX(100%);}100%{-webkit-transform:translateX(100%);-ms-transform:translateX(100%);transform:translateX(100%);}}/*!sc*/ @keyframes crVFvv{0%{-webkit-transform:translateX(-100%);-ms-transform:translateX(-100%);transform:translateX(-100%);}50%{-webkit-transform:translateX(100%);-ms-transform:translateX(100%);transform:translateX(100%);}100%{-webkit-transform:translateX(100%);-ms-transform:translateX(100%);transform:translateX(100%);}}/*!sc*/ data-styled.g53[id="sc-keyframes-crVFvv"]{content:"crVFvv,"}/*!sc*/

Commit 0777f7a

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 ignored when determining whether the index has a desired sort ordering. But this failed to work for boolean index columns, because a condition like "boolcol = true" is canonicalized to just "boolcol" which does not give rise to an EquivalenceClass. Add a check to allow 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 the limited impact and the small number of complaints so far, I won't risk destabilizing plans in stable branches by back-patching. Patch by me, reviewed by Michael Paquier Discussion: https://postgr.es/m/1788.1481605684@sss.pgh.pa.us
1 parent 83f2061 commit 0777f7a

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, int indexcol)
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
extern bool relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
6767
List *restrictlist,
6868
List *exprlist, List *oprlist);
69+
extern bool indexcol_is_bool_constant_for_query(IndexOptInfo *index,
70+
int indexcol);
6971
extern bool match_index_to_operand(Node *operand, int indexcol,
7072
IndexOptInfo *index);
7173
extern void expand_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 tenk1 where (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, i int, unique(b, i), junk float);
1018+
1019+
explain (costs off)
1020+
select * from boolindex order by b, i limit 10;
1021+
explain (costs off)
1022+
select * from boolindex where b order by i limit 10;
1023+
explain (costs off)
1024+
select * from boolindex where b = true order by i desc limit 10;
1025+
explain (costs off)
1026+
select * from boolindex where not b order by i limit 10;
1027+
10131028
--
10141029
-- REINDEX (VERBOSE)
10151030
--

0 commit comments

Comments
 (0)
0