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

Commitc291203

Browse files
committed
Fix EquivalenceClass code to handle volatile sort expressions in a more
predictable manner; in particular that if you say ORDER BY output-column-ref,it will in fact sort by that specific column even if there are multiplesyntactic matches. An example isSELECT random() AS a, random() AS b FROM ... ORDER BY b, a;While the use-case for this might be a bit debatable, it worked as expectedin earlier releases, so we should preserve the behavior for 8.3. Per myrecent proposal.While at it, fix convert_subquery_pathkeys() to handle RelabelType strippingin both directions; it needs this for the same reasons make_sort_from_pathkeysdoes.
1 parent1be0601 commitc291203

File tree

8 files changed

+300
-183
lines changed

8 files changed

+300
-183
lines changed

‎src/backend/nodes/outfuncs.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.315 2007/10/11 18:05:27 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.316 2007/11/08 21:49:47 tgl Exp $
1212
*
1313
* NOTES
1414
* Every node type that can appear in stored rules' parsetrees *must*
@@ -1405,6 +1405,7 @@ _outEquivalenceClass(StringInfo str, EquivalenceClass *node)
14051405
WRITE_BOOL_FIELD(ec_has_volatile);
14061406
WRITE_BOOL_FIELD(ec_below_outer_join);
14071407
WRITE_BOOL_FIELD(ec_broken);
1408+
WRITE_UINT_FIELD(ec_sortref);
14081409
}
14091410

14101411
staticvoid

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

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Portions Copyright (c) 1994, Regents of the University of California
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/optimizer/path/equivclass.c,v 1.3 2007/07/07 20:46:45 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/optimizer/path/equivclass.c,v 1.4 2007/11/08 21:49:47 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -294,6 +294,7 @@ process_equivalence(PlannerInfo *root, RestrictInfo *restrictinfo,
294294
ec->ec_has_volatile= false;
295295
ec->ec_below_outer_join=below_outer_join;
296296
ec->ec_broken= false;
297+
ec->ec_sortref=0;
297298
ec->ec_merged=NULL;
298299
em1=add_eq_member(ec,item1,item1_relids, false,item1_type);
299300
em2=add_eq_member(ec,item2,item2_relids, false,item2_type);
@@ -354,6 +355,9 @@ add_eq_member(EquivalenceClass *ec, Expr *expr, Relids relids,
354355
* class it is a member of; if none, build a new single-member
355356
* EquivalenceClass for it.
356357
*
358+
* sortref is the SortGroupRef of the originating SortClause, if any,
359+
* or zero if not.
360+
*
357361
* This can be used safely both before and after EquivalenceClass merging;
358362
* since it never causes merging it does not invalidate any existing ECs
359363
* or PathKeys.
@@ -367,7 +371,8 @@ EquivalenceClass *
367371
get_eclass_for_sort_expr(PlannerInfo*root,
368372
Expr*expr,
369373
Oidexpr_datatype,
370-
List*opfamilies)
374+
List*opfamilies,
375+
Indexsortref)
371376
{
372377
EquivalenceClass*newec;
373378
EquivalenceMember*newem;
@@ -382,7 +387,9 @@ get_eclass_for_sort_expr(PlannerInfo *root,
382387
EquivalenceClass*cur_ec= (EquivalenceClass*)lfirst(lc1);
383388
ListCell*lc2;
384389

385-
/* we allow matching to a volatile EC here */
390+
/* Never match to a volatile EC */
391+
if (cur_ec->ec_has_volatile)
392+
continue;
386393

387394
if (!equal(opfamilies,cur_ec->ec_opfamilies))
388395
continue;
@@ -423,6 +430,7 @@ get_eclass_for_sort_expr(PlannerInfo *root,
423430
newec->ec_has_volatile=contain_volatile_functions((Node*)expr);
424431
newec->ec_below_outer_join= false;
425432
newec->ec_broken= false;
433+
newec->ec_sortref=sortref;
426434
newec->ec_merged=NULL;
427435
newem=add_eq_member(newec,expr,pull_varnos((Node*)expr),
428436
false,expr_datatype);

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

Lines changed: 158 additions & 89 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
* Portions Copyright (c) 1994, Regents of the University of California
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.88 2007/11/0819:25:37 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.89 2007/11/0821:49:47 tgl Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -46,6 +46,7 @@ static bool pathkey_is_redundant(PathKey *new_pathkey, List *pathkeys);
4646
staticPathKey*make_pathkey_from_sortinfo(PlannerInfo*root,
4747
Expr*expr,Oidordering_op,
4848
boolnulls_first,
49+
Indexsortref,
4950
boolcanonicalize);
5051
staticVar*find_indexkey_var(PlannerInfo*root,RelOptInfo*rel,
5152
AttrNumbervarattno);
@@ -233,13 +234,17 @@ canonicalize_pathkeys(PlannerInfo *root, List *pathkeys)
233234
* a PathKey. If canonicalize = true, the result is a "canonical"
234235
* PathKey, otherwise not. (But note it might be redundant anyway.)
235236
*
237+
* If the PathKey is being generated from a SortClause, sortref should be
238+
* the SortClause's SortGroupRef; otherwise zero.
239+
*
236240
* canonicalize should always be TRUE after EquivalenceClass merging has
237241
* been performed, but FALSE if we haven't done EquivalenceClass merging yet.
238242
*/
239243
staticPathKey*
240244
make_pathkey_from_sortinfo(PlannerInfo*root,
241245
Expr*expr,Oidordering_op,
242246
boolnulls_first,
247+
Indexsortref,
243248
boolcanonicalize)
244249
{
245250
Oidopfamily,
@@ -303,7 +308,8 @@ make_pathkey_from_sortinfo(PlannerInfo *root,
303308
}
304309

305310
/* Now find or create a matching EquivalenceClass */
306-
eclass=get_eclass_for_sort_expr(root,expr,opcintype,opfamilies);
311+
eclass=get_eclass_for_sort_expr(root,expr,opcintype,opfamilies,
312+
sortref);
307313

308314
/* And finally we can find or create a PathKey node */
309315
if (canonicalize)
@@ -525,6 +531,7 @@ build_index_pathkeys(PlannerInfo *root,
525531
indexkey,
526532
sortop,
527533
nulls_first,
534+
0,
528535
true);
529536

530537
/* Add to list unless redundant */
@@ -597,102 +604,161 @@ convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
597604
PathKey*sub_pathkey= (PathKey*)lfirst(i);
598605
EquivalenceClass*sub_eclass=sub_pathkey->pk_eclass;
599606
PathKey*best_pathkey=NULL;
600-
intbest_score=-1;
601-
ListCell*j;
602607

603-
/*
604-
* The sub_pathkey's EquivalenceClass could contain multiple elements
605-
* (representing knowledge that multiple items are effectively equal).
606-
* Each element might match none, one, or more of the output columns
607-
* that are visible to the outer query. This means we may have
608-
* multiple possible representations of the sub_pathkey in the context
609-
* of the outer query. Ideally we would generate them all and put
610-
* them all into an EC of the outer query, thereby propagating
611-
* equality knowledge up to the outer query. Right now we cannot do
612-
* so, because the outer query's EquivalenceClasses are already frozen
613-
* when this is called. Instead we prefer the one that has the highest
614-
* "score" (number of EC peers, plus one if it matches the outer
615-
* query_pathkeys). This is the most likely to be useful in the outer
616-
* query.
617-
*/
618-
foreach(j,sub_eclass->ec_members)
608+
if (sub_eclass->ec_has_volatile)
619609
{
620-
EquivalenceMember*sub_member= (EquivalenceMember*)lfirst(j);
621-
Expr*sub_expr=sub_member->em_expr;
622-
Expr*rtarg;
623-
ListCell*k;
624-
625610
/*
626-
* We handle two cases: the sub_pathkey key can be either an exact
627-
* match for a targetlist entry, or a RelabelType of a targetlist
628-
* entry. (The latter case is worth extra code because it arises
629-
* frequently in connection with varchar fields.)
611+
* If the sub_pathkey's EquivalenceClass is volatile, then it must
612+
* have come from an ORDER BY clause, and we have to match it to
613+
* that same targetlist entry.
630614
*/
631-
if (IsA(sub_expr,RelabelType))
632-
rtarg= ((RelabelType*)sub_expr)->arg;
633-
else
634-
rtarg=NULL;
635-
636-
foreach(k,sub_tlist)
615+
TargetEntry*tle;
616+
617+
if (sub_eclass->ec_sortref==0)/* can't happen */
618+
elog(ERROR,"volatile EquivalenceClass has no sortref");
619+
tle=get_sortgroupref_tle(sub_eclass->ec_sortref,sub_tlist);
620+
Assert(tle);
621+
/* resjunk items aren't visible to outer query */
622+
if (!tle->resjunk)
637623
{
638-
TargetEntry*tle= (TargetEntry*)lfirst(k);
624+
/* We can represent this sub_pathkey */
625+
EquivalenceMember*sub_member;
639626
Expr*outer_expr;
640627
EquivalenceClass*outer_ec;
641-
PathKey*outer_pk;
642-
intscore;
643628

644-
/* resjunk items aren't visible to outer query */
645-
if (tle->resjunk)
646-
continue;
629+
Assert(list_length(sub_eclass->ec_members)==1);
630+
sub_member= (EquivalenceMember*)linitial(sub_eclass->ec_members);
631+
outer_expr= (Expr*)
632+
makeVar(rel->relid,
633+
tle->resno,
634+
exprType((Node*)tle->expr),
635+
exprTypmod((Node*)tle->expr),
636+
0);
637+
outer_ec=
638+
get_eclass_for_sort_expr(root,
639+
outer_expr,
640+
sub_member->em_datatype,
641+
sub_eclass->ec_opfamilies,
642+
0);
643+
best_pathkey=
644+
make_canonical_pathkey(root,
645+
outer_ec,
646+
sub_pathkey->pk_opfamily,
647+
sub_pathkey->pk_strategy,
648+
sub_pathkey->pk_nulls_first);
649+
}
650+
}
651+
else
652+
{
653+
/*
654+
* Otherwise, the sub_pathkey's EquivalenceClass could contain
655+
* multiple elements (representing knowledge that multiple items
656+
* are effectively equal). Each element might match none, one, or
657+
* more of the output columns that are visible to the outer
658+
* query. This means we may have multiple possible representations
659+
* of the sub_pathkey in the context of the outer query. Ideally
660+
* we would generate them all and put them all into an EC of the
661+
* outer query, thereby propagating equality knowledge up to the
662+
* outer query. Right now we cannot do so, because the outer
663+
* query's EquivalenceClasses are already frozen when this is
664+
* called. Instead we prefer the one that has the highest "score"
665+
* (number of EC peers, plus one if it matches the outer
666+
* query_pathkeys). This is the most likely to be useful in the
667+
* outer query.
668+
*/
669+
intbest_score=-1;
670+
ListCell*j;
647671

648-
if (equal(tle->expr,sub_expr))
649-
{
650-
/* Exact match */
651-
outer_expr= (Expr*)
652-
makeVar(rel->relid,
653-
tle->resno,
654-
exprType((Node*)tle->expr),
655-
exprTypmod((Node*)tle->expr),
656-
0);
657-
}
658-
elseif (rtarg&&equal(tle->expr,rtarg))
672+
foreach(j,sub_eclass->ec_members)
673+
{
674+
EquivalenceMember*sub_member= (EquivalenceMember*)lfirst(j);
675+
Expr*sub_expr=sub_member->em_expr;
676+
Expr*sub_stripped;
677+
ListCell*k;
678+
679+
/*
680+
* We handle two cases: the sub_pathkey key can be either an
681+
* exact match for a targetlist entry, or it could match after
682+
* stripping RelabelType nodes. (We need that case since
683+
* make_pathkey_from_sortinfo could add or remove RelabelType.)
684+
*/
685+
sub_stripped=sub_expr;
686+
while (sub_stripped&&IsA(sub_stripped,RelabelType))
687+
sub_stripped= ((RelabelType*)sub_stripped)->arg;
688+
689+
foreach(k,sub_tlist)
659690
{
660-
/* Match after discarding RelabelType */
661-
outer_expr= (Expr*)
662-
makeVar(rel->relid,
663-
tle->resno,
664-
exprType((Node*)tle->expr),
665-
exprTypmod((Node*)tle->expr),
666-
0);
667-
outer_expr= (Expr*)
668-
makeRelabelType((Expr*)outer_expr,
669-
((RelabelType*)sub_expr)->resulttype,
670-
((RelabelType*)sub_expr)->resulttypmod,
671-
((RelabelType*)sub_expr)->relabelformat);
672-
}
673-
else
674-
continue;
691+
TargetEntry*tle= (TargetEntry*)lfirst(k);
692+
Expr*outer_expr;
693+
EquivalenceClass*outer_ec;
694+
PathKey*outer_pk;
695+
intscore;
675696

676-
/* Found a representation for this sub_pathkey */
677-
outer_ec=get_eclass_for_sort_expr(root,
678-
outer_expr,
679-
sub_member->em_datatype,
680-
sub_eclass->ec_opfamilies);
681-
outer_pk=make_canonical_pathkey(root,
682-
outer_ec,
683-
sub_pathkey->pk_opfamily,
684-
sub_pathkey->pk_strategy,
685-
sub_pathkey->pk_nulls_first);
686-
/* score = # of equivalence peers */
687-
score=list_length(outer_ec->ec_members)-1;
688-
/* +1 if it matches the proper query_pathkeys item */
689-
if (retvallen<outer_query_keys&&
690-
list_nth(root->query_pathkeys,retvallen)==outer_pk)
691-
score++;
692-
if (score>best_score)
693-
{
694-
best_pathkey=outer_pk;
695-
best_score=score;
697+
/* resjunk items aren't visible to outer query */
698+
if (tle->resjunk)
699+
continue;
700+
701+
if (equal(tle->expr,sub_expr))
702+
{
703+
/* Exact match */
704+
outer_expr= (Expr*)
705+
makeVar(rel->relid,
706+
tle->resno,
707+
exprType((Node*)tle->expr),
708+
exprTypmod((Node*)tle->expr),
709+
0);
710+
}
711+
else
712+
{
713+
Expr*tle_stripped;
714+
715+
tle_stripped=tle->expr;
716+
while (tle_stripped&&IsA(tle_stripped,RelabelType))
717+
tle_stripped= ((RelabelType*)tle_stripped)->arg;
718+
719+
if (equal(tle_stripped,sub_stripped))
720+
{
721+
/* Match after discarding RelabelType */
722+
outer_expr= (Expr*)
723+
makeVar(rel->relid,
724+
tle->resno,
725+
exprType((Node*)tle->expr),
726+
exprTypmod((Node*)tle->expr),
727+
0);
728+
if (exprType((Node*)outer_expr)!=
729+
exprType((Node*)sub_expr))
730+
outer_expr= (Expr*)
731+
makeRelabelType(outer_expr,
732+
exprType((Node*)sub_expr),
733+
-1,
734+
COERCE_DONTCARE);
735+
}
736+
else
737+
continue;
738+
}
739+
740+
/* Found a representation for this sub_pathkey */
741+
outer_ec=get_eclass_for_sort_expr(root,
742+
outer_expr,
743+
sub_member->em_datatype,
744+
sub_eclass->ec_opfamilies,
745+
0);
746+
outer_pk=make_canonical_pathkey(root,
747+
outer_ec,
748+
sub_pathkey->pk_opfamily,
749+
sub_pathkey->pk_strategy,
750+
sub_pathkey->pk_nulls_first);
751+
/* score = # of equivalence peers */
752+
score=list_length(outer_ec->ec_members)-1;
753+
/* +1 if it matches the proper query_pathkeys item */
754+
if (retvallen<outer_query_keys&&
755+
list_nth(root->query_pathkeys,retvallen)==outer_pk)
756+
score++;
757+
if (score>best_score)
758+
{
759+
best_pathkey=outer_pk;
760+
best_score=score;
761+
}
696762
}
697763
}
698764
}
@@ -795,6 +861,7 @@ make_pathkeys_for_sortclauses(PlannerInfo *root,
795861
sortkey,
796862
sortcl->sortop,
797863
sortcl->nulls_first,
864+
sortcl->tleSortGroupRef,
798865
canonicalize);
799866

800867
/* Canonical form eliminates redundant ordering keys */
@@ -843,12 +910,14 @@ cache_mergeclause_eclasses(PlannerInfo *root, RestrictInfo *restrictinfo)
843910
get_eclass_for_sort_expr(root,
844911
(Expr*)get_leftop(clause),
845912
lefttype,
846-
restrictinfo->mergeopfamilies);
913+
restrictinfo->mergeopfamilies,
914+
0);
847915
restrictinfo->right_ec=
848916
get_eclass_for_sort_expr(root,
849917
(Expr*)get_rightop(clause),
850918
righttype,
851-
restrictinfo->mergeopfamilies);
919+
restrictinfo->mergeopfamilies,
920+
0);
852921
}
853922
else
854923
Assert(restrictinfo->right_ec!=NULL);

0 commit comments

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