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

Commit5f677af

Browse files
committed
Adjust subquery qual pushdown rules so that we can push down a qual
into a UNION that has some type coercions applied to the componentqueries, so long as the qual itself does not reference any columns thathave such coercions. Per example from Jonathan Bartlett 24-Apr-03.
1 parent555fe9d commit5f677af

File tree

3 files changed

+89
-34
lines changed

3 files changed

+89
-34
lines changed

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

Lines changed: 85 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.101 2003/03/22 17:11:25 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.102 2003/04/24 23:43:09 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -49,9 +49,14 @@ static void set_function_pathlist(Query *root, RelOptInfo *rel,
4949
RangeTblEntry*rte);
5050
staticRelOptInfo*make_one_rel_by_joins(Query*root,intlevels_needed,
5151
List*initial_rels);
52-
staticboolsubquery_is_pushdown_safe(Query*subquery,Query*topquery);
53-
staticboolrecurse_pushdown_safe(Node*setOp,Query*topquery);
54-
staticboolqual_is_pushdown_safe(Query*subquery,Indexrti,Node*qual);
52+
staticboolsubquery_is_pushdown_safe(Query*subquery,Query*topquery,
53+
bool*differentTypes);
54+
staticboolrecurse_pushdown_safe(Node*setOp,Query*topquery,
55+
bool*differentTypes);
56+
staticvoidcompare_tlist_datatypes(List*tlist,List*colTypes,
57+
bool*differentTypes);
58+
staticboolqual_is_pushdown_safe(Query*subquery,Indexrti,Node*qual,
59+
bool*differentTypes);
5560
staticvoidsubquery_push_qual(Query*subquery,Indexrti,Node*qual);
5661
staticvoidrecurse_push_qual(Node*setOp,Query*topquery,
5762
Indexrti,Node*qual);
@@ -294,8 +299,13 @@ set_subquery_pathlist(Query *root, RelOptInfo *rel,
294299
Indexrti,RangeTblEntry*rte)
295300
{
296301
Query*subquery=rte->subquery;
302+
bool*differentTypes;
297303
List*pathkeys;
298304

305+
/* We need a workspace for keeping track of set-op type coercions */
306+
differentTypes= (bool*)
307+
palloc0((length(subquery->targetList)+1)*sizeof(bool));
308+
299309
/*
300310
* If there are any restriction clauses that have been attached to the
301311
* subquery relation, consider pushing them down to become HAVING
@@ -318,7 +328,7 @@ set_subquery_pathlist(Query *root, RelOptInfo *rel,
318328
* push down a pushable qual, because it'd result in a worse plan?
319329
*/
320330
if (rel->baserestrictinfo!=NIL&&
321-
subquery_is_pushdown_safe(subquery,subquery))
331+
subquery_is_pushdown_safe(subquery,subquery,differentTypes))
322332
{
323333
/* OK to consider pushing down individual quals */
324334
List*upperrestrictlist=NIL;
@@ -329,7 +339,7 @@ set_subquery_pathlist(Query *root, RelOptInfo *rel,
329339
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lst);
330340
Node*clause= (Node*)rinfo->clause;
331341

332-
if (qual_is_pushdown_safe(subquery,rti,clause))
342+
if (qual_is_pushdown_safe(subquery,rti,clause,differentTypes))
333343
{
334344
/* Push it down */
335345
subquery_push_qual(subquery,rti,clause);
@@ -343,6 +353,8 @@ set_subquery_pathlist(Query *root, RelOptInfo *rel,
343353
rel->baserestrictinfo=upperrestrictlist;
344354
}
345355

356+
pfree(differentTypes);
357+
346358
/* Generate the plan for the subquery */
347359
rel->subplan=subquery_planner(subquery,0.0/* default case */ );
348360

@@ -532,15 +544,19 @@ make_one_rel_by_joins(Query *root, int levels_needed, List *initial_rels)
532544
* since that could change the set of rows returned.
533545
*
534546
* 2. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
535-
* quals into it, because that would change the results. For subqueries
536-
* using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can push the quals
537-
* into each component query, so long as all the component queries share
538-
* identical output types.(That restriction could probably be relaxed,
539-
* but it would take much more code to include type coercion code into
540-
* the quals, and I'm also concerned about possible semantic gotchas.)
547+
* quals into it, because that would change the results.
548+
*
549+
* 3. For subqueries using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can
550+
* push quals into each component query, but the quals can only reference
551+
* subquery columns that suffer no type coercions in the set operation.
552+
* Otherwise there are possible semantic gotchas. So, we check the
553+
* component queries to see if any of them have different output types;
554+
* differentTypes[k] is set true if column k has different type in any
555+
* component.
541556
*/
542557
staticbool
543-
subquery_is_pushdown_safe(Query*subquery,Query*topquery)
558+
subquery_is_pushdown_safe(Query*subquery,Query*topquery,
559+
bool*differentTypes)
544560
{
545561
SetOperationStmt*topop;
546562

@@ -553,22 +569,21 @@ subquery_is_pushdown_safe(Query *subquery, Query *topquery)
553569
{
554570
/* Top level, so check any component queries */
555571
if (subquery->setOperations!=NULL)
556-
if (!recurse_pushdown_safe(subquery->setOperations,topquery))
572+
if (!recurse_pushdown_safe(subquery->setOperations,topquery,
573+
differentTypes))
557574
return false;
558575
}
559576
else
560577
{
561578
/* Setop component must not have more components (too weird) */
562579
if (subquery->setOperations!=NULL)
563580
return false;
564-
/*Setopcomponent output types must match top level */
581+
/*Check whether setopcomponent output types match top level */
565582
topop= (SetOperationStmt*)topquery->setOperations;
566583
Assert(topop&&IsA(topop,SetOperationStmt));
567-
if (!tlist_same_datatypes(subquery->targetList,
568-
topop->colTypes,
569-
true))
570-
return false;
571-
584+
compare_tlist_datatypes(subquery->targetList,
585+
topop->colTypes,
586+
differentTypes);
572587
}
573588
return true;
574589
}
@@ -577,7 +592,8 @@ subquery_is_pushdown_safe(Query *subquery, Query *topquery)
577592
* Helper routine to recurse through setOperations tree
578593
*/
579594
staticbool
580-
recurse_pushdown_safe(Node*setOp,Query*topquery)
595+
recurse_pushdown_safe(Node*setOp,Query*topquery,
596+
bool*differentTypes)
581597
{
582598
if (IsA(setOp,RangeTblRef))
583599
{
@@ -586,7 +602,7 @@ recurse_pushdown_safe(Node *setOp, Query *topquery)
586602
Query*subquery=rte->subquery;
587603

588604
Assert(subquery!=NULL);
589-
returnsubquery_is_pushdown_safe(subquery,topquery);
605+
returnsubquery_is_pushdown_safe(subquery,topquery,differentTypes);
590606
}
591607
elseif (IsA(setOp,SetOperationStmt))
592608
{
@@ -596,9 +612,9 @@ recurse_pushdown_safe(Node *setOp, Query *topquery)
596612
if (op->op==SETOP_EXCEPT)
597613
return false;
598614
/* Else recurse */
599-
if (!recurse_pushdown_safe(op->larg,topquery))
615+
if (!recurse_pushdown_safe(op->larg,topquery,differentTypes))
600616
return false;
601-
if (!recurse_pushdown_safe(op->rarg,topquery))
617+
if (!recurse_pushdown_safe(op->rarg,topquery,differentTypes))
602618
return false;
603619
}
604620
else
@@ -609,6 +625,33 @@ recurse_pushdown_safe(Node *setOp, Query *topquery)
609625
return true;
610626
}
611627

628+
/*
629+
* Compare tlist's datatypes against the list of set-operation result types.
630+
* For any items that are different, mark the appropriate element of
631+
* differentTypes[] to show that this column will have type conversions.
632+
*/
633+
staticvoid
634+
compare_tlist_datatypes(List*tlist,List*colTypes,
635+
bool*differentTypes)
636+
{
637+
List*i;
638+
639+
foreach(i,tlist)
640+
{
641+
TargetEntry*tle= (TargetEntry*)lfirst(i);
642+
643+
if (tle->resdom->resjunk)
644+
continue;/* ignore resjunk columns */
645+
if (colTypes==NIL)
646+
elog(ERROR,"wrong number of tlist entries");
647+
if (tle->resdom->restype!=lfirsto(colTypes))
648+
differentTypes[tle->resdom->resno]= true;
649+
colTypes=lnext(colTypes);
650+
}
651+
if (colTypes!=NIL)
652+
elog(ERROR,"wrong number of tlist entries");
653+
}
654+
612655
/*
613656
* qual_is_pushdown_safe - is a particular qual safe to push down?
614657
*
@@ -621,20 +664,25 @@ recurse_pushdown_safe(Node *setOp, Query *topquery)
621664
* it will work correctly: sublinks will already have been transformed into
622665
* subplans in the qual, but not in the subquery).
623666
*
624-
* 2. If the subquery uses DISTINCT ON, we must not push down any quals that
667+
* 2. The qual must not refer to any subquery output columns that were
668+
* found to have inconsistent types across a set operation tree by
669+
* subquery_is_pushdown_safe().
670+
*
671+
* 3. If the subquery uses DISTINCT ON, we must not push down any quals that
625672
* refer to non-DISTINCT output columns, because that could change the set
626673
* of rows returned. This condition is vacuous for DISTINCT, because then
627674
* there are no non-DISTINCT output columns, but unfortunately it's fairly
628675
* expensive to tell the difference between DISTINCT and DISTINCT ON in the
629676
* parsetree representation. It's cheaper to just make sure all the Vars
630677
* in the qual refer to DISTINCT columns.
631678
*
632-
*3. We must not push down any quals that refer to subselect outputs that
679+
*4. We must not push down any quals that refer to subselect outputs that
633680
* return sets, else we'd introduce functions-returning-sets into the
634681
* subquery's WHERE/HAVING quals.
635682
*/
636683
staticbool
637-
qual_is_pushdown_safe(Query*subquery,Indexrti,Node*qual)
684+
qual_is_pushdown_safe(Query*subquery,Indexrti,Node*qual,
685+
bool*differentTypes)
638686
{
639687
boolsafe= true;
640688
List*vars;
@@ -666,6 +714,14 @@ qual_is_pushdown_safe(Query *subquery, Index rti, Node *qual)
666714
continue;
667715
tested=bms_add_member(tested,var->varattno);
668716

717+
/* Check point 2 */
718+
if (differentTypes[var->varattno])
719+
{
720+
safe= false;
721+
break;
722+
}
723+
724+
/* Must find the tlist element referenced by the Var */
669725
foreach(tl,subquery->targetList)
670726
{
671727
tle= (TargetEntry*)lfirst(tl);
@@ -675,7 +731,7 @@ qual_is_pushdown_safe(Query *subquery, Index rti, Node *qual)
675731
Assert(tl!=NIL);
676732
Assert(!tle->resdom->resjunk);
677733

678-
/* If subquery uses DISTINCT or DISTINCT ON, check point2 */
734+
/* If subquery uses DISTINCT or DISTINCT ON, check point3 */
679735
if (subquery->distinctClause!=NIL&&
680736
!targetIsInSortList(tle,subquery->distinctClause))
681737
{
@@ -684,7 +740,7 @@ qual_is_pushdown_safe(Query *subquery, Index rti, Node *qual)
684740
break;
685741
}
686742

687-
/* Refuse functions returning sets (point3) */
743+
/* Refuse functions returning sets (point4) */
688744
if (expression_returns_set((Node*)tle->expr))
689745
{
690746
safe= false;

‎src/backend/optimizer/prep/prepunion.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
*
1515
*
1616
* IDENTIFICATION
17-
* $Header: /cvsroot/pgsql/src/backend/optimizer/prep/prepunion.c,v 1.92 2003/03/10 03:53:50 tgl Exp $
17+
* $Header: /cvsroot/pgsql/src/backend/optimizer/prep/prepunion.c,v 1.93 2003/04/24 23:43:09 tgl Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -60,6 +60,7 @@ static List *generate_setop_tlist(List *colTypes, int flag,
6060
staticList*generate_append_tlist(List*colTypes,boolflag,
6161
List*input_plans,
6262
List*refnames_tlist);
63+
staticbooltlist_same_datatypes(List*tlist,List*colTypes,booljunkOK);
6364
staticNode*adjust_inherited_attrs_mutator(Node*node,
6465
adjust_inherited_attrs_context*context);
6566
staticRelidsadjust_relid_set(Relidsrelids,Indexoldrelid,Indexnewrelid);
@@ -572,7 +573,7 @@ generate_append_tlist(List *colTypes, bool flag,
572573
* Resjunk columns are ignored if junkOK is true; otherwise presence of
573574
* a resjunk column will always cause a 'false' result.
574575
*/
575-
bool
576+
staticbool
576577
tlist_same_datatypes(List*tlist,List*colTypes,booljunkOK)
577578
{
578579
List*i;

‎src/include/optimizer/prep.h

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: prep.h,v 1.38 2003/03/05 20:01:04 tgl Exp $
10+
* $Id: prep.h,v 1.39 2003/04/24 23:43:09 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -59,6 +59,4 @@ extern Node *adjust_inherited_attrs(Node *node,
5959
Indexold_rt_index,Oidold_relid,
6060
Indexnew_rt_index,Oidnew_relid);
6161

62-
externbooltlist_same_datatypes(List*tlist,List*colTypes,booljunkOK);
63-
6462
#endif/* PREP_H */

0 commit comments

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