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

Commit0201dac

Browse files
committed
Push down outer qualification clauses into UNION and INTERSECT subqueries.
Per pghackers discussion from back around 1-August.
1 parentd223680 commit0201dac

File tree

3 files changed

+196
-54
lines changed

3 files changed

+196
-54
lines changed

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

Lines changed: 191 additions & 50 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.86 2002/06/20 20:29:29 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.87 2002/08/29 16:03:48 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -46,6 +46,11 @@ static void set_function_pathlist(Query *root, RelOptInfo *rel,
4646
RangeTblEntry*rte);
4747
staticRelOptInfo*make_one_rel_by_joins(Query*root,intlevels_needed,
4848
List*initial_rels);
49+
staticboolsubquery_is_pushdown_safe(Query*subquery,Query*topquery);
50+
staticboolrecurse_pushdown_safe(Node*setOp,Query*topquery);
51+
staticvoidsubquery_push_qual(Query*subquery,Indexrti,Node*qual);
52+
staticvoidrecurse_push_qual(Node*setOp,Query*topquery,
53+
Indexrti,Node*qual);
4954

5055

5156
/*
@@ -297,43 +302,20 @@ set_subquery_pathlist(Query *root, RelOptInfo *rel,
297302
* generate a better plan for the subquery than evaluating all the
298303
* subquery output rows and then filtering them.
299304
*
300-
* There are several cases where we cannot push down clauses:
301-
*
302-
* 1. If the subquery contains set ops (UNION/INTERSECT/EXCEPT) we do not
303-
* push down any qual clauses, since the planner doesn't support quals
304-
* at the top level of a setop. (With suitable analysis we could try
305-
* to push the quals down into the component queries of the setop, but
306-
* getting it right seems nontrivial. Work on this later.)
307-
*
308-
* 2. If the subquery has a LIMIT clause or a DISTINCT ON clause, we must
309-
* not push down any quals, since that could change the set of rows
310-
* returned. (Actually, we could push down quals into a DISTINCT ON
311-
* subquery if they refer only to DISTINCT-ed output columns, but
312-
* checking that seems more work than it's worth. In any case, a
313-
* plain DISTINCT is safe to push down past.)
314-
*
315-
* 3. If the subquery has any functions returning sets in its target list,
316-
* we do not push down any quals, since the quals
317-
* might refer to those tlist items, which would mean we'd introduce
318-
* functions-returning-sets into the subquery's WHERE/HAVING quals.
319-
* (It'd be sufficient to not push down quals that refer to those
320-
* particular tlist items, but that's much clumsier to check.)
321-
*
322-
* 4. We do not push down clauses that contain subselects, mainly because
323-
* I'm not sure it will work correctly (the subplan hasn't yet
324-
* transformed sublinks to subselects).
305+
* There are several cases where we cannot push down clauses.
306+
* Restrictions involving the subquery are checked by
307+
* subquery_is_pushdown_safe(). Also, we do not push down clauses that
308+
* contain subselects, mainly because I'm not sure it will work correctly
309+
* (the subplan hasn't yet transformed sublinks to subselects).
325310
*
326311
* Non-pushed-down clauses will get evaluated as qpquals of the
327312
* SubqueryScan node.
328313
*
329314
* XXX Are there any cases where we want to make a policy decision not to
330315
* push down, because it'd result in a worse plan?
331316
*/
332-
if (subquery->setOperations==NULL&&
333-
subquery->limitOffset==NULL&&
334-
subquery->limitCount==NULL&&
335-
!has_distinct_on_clause(subquery)&&
336-
!expression_returns_set((Node*)subquery->targetList))
317+
if (rel->baserestrictinfo!=NIL&&
318+
subquery_is_pushdown_safe(subquery,subquery))
337319
{
338320
/* OK to consider pushing down individual quals */
339321
List*upperrestrictlist=NIL;
@@ -351,25 +333,8 @@ set_subquery_pathlist(Query *root, RelOptInfo *rel,
351333
}
352334
else
353335
{
354-
/*
355-
* We need to replace Vars in the clause (which must refer
356-
* to outputs of the subquery) with copies of the
357-
* subquery's targetlist expressions. Note that at this
358-
* point, any uplevel Vars in the clause should have been
359-
* replaced with Params, so they need no work.
360-
*/
361-
clause=ResolveNew(clause,rti,0,
362-
subquery->targetList,
363-
CMD_SELECT,0);
364-
subquery->havingQual=make_and_qual(subquery->havingQual,
365-
clause);
366-
367-
/*
368-
* We need not change the subquery's hasAggs or
369-
* hasSublinks flags, since we can't be pushing down any
370-
* aggregates that weren't there before, and we don't push
371-
* down subselects at all.
372-
*/
336+
/* Push it down */
337+
subquery_push_qual(subquery,rti,clause);
373338
}
374339
}
375340
rel->baserestrictinfo=upperrestrictlist;
@@ -547,7 +512,183 @@ make_one_rel_by_joins(Query *root, int levels_needed, List *initial_rels)
547512
}
548513

549514
/*****************************************************************************
515+
*PUSHING QUALS DOWN INTO SUBQUERIES
516+
*****************************************************************************/
517+
518+
/*
519+
* subquery_is_pushdown_safe - is a subquery safe for pushing down quals?
520+
*
521+
* subquery is the particular component query being checked. topquery
522+
* is the top component of a set-operations tree (the same Query if no
523+
* set-op is involved).
524+
*
525+
* Conditions checked here:
550526
*
527+
* 1. If the subquery has a LIMIT clause or a DISTINCT ON clause, we must
528+
* not push down any quals, since that could change the set of rows
529+
* returned. (Actually, we could push down quals into a DISTINCT ON
530+
* subquery if they refer only to DISTINCT-ed output columns, but
531+
* checking that seems more work than it's worth. In any case, a
532+
* plain DISTINCT is safe to push down past.)
533+
*
534+
* 2. If the subquery has any functions returning sets in its target list,
535+
* we do not push down any quals, since the quals
536+
* might refer to those tlist items, which would mean we'd introduce
537+
* functions-returning-sets into the subquery's WHERE/HAVING quals.
538+
* (It'd be sufficient to not push down quals that refer to those
539+
* particular tlist items, but that's much clumsier to check.)
540+
*
541+
* 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
542+
* quals into it, because that would change the results. For subqueries
543+
* using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can push the quals
544+
* into each component query, so long as all the component queries share
545+
* identical output types. (That restriction could probably be relaxed,
546+
* but it would take much more code to include type coercion code into
547+
* the quals, and I'm also concerned about possible semantic gotchas.)
548+
*/
549+
staticbool
550+
subquery_is_pushdown_safe(Query*subquery,Query*topquery)
551+
{
552+
SetOperationStmt*topop;
553+
554+
/* Check points 1 and 2 */
555+
if (subquery->limitOffset!=NULL||
556+
subquery->limitCount!=NULL||
557+
has_distinct_on_clause(subquery)||
558+
expression_returns_set((Node*)subquery->targetList))
559+
return false;
560+
561+
/* Are we at top level, or looking at a setop component? */
562+
if (subquery==topquery)
563+
{
564+
/* Top level, so check any component queries */
565+
if (subquery->setOperations!=NULL)
566+
if (!recurse_pushdown_safe(subquery->setOperations,topquery))
567+
return false;
568+
}
569+
else
570+
{
571+
/* Setop component must not have more components (too weird) */
572+
if (subquery->setOperations!=NULL)
573+
return false;
574+
/* Setop component output types must match top level */
575+
topop= (SetOperationStmt*)topquery->setOperations;
576+
Assert(topop&&IsA(topop,SetOperationStmt));
577+
if (!tlist_same_datatypes(subquery->targetList,
578+
topop->colTypes,
579+
true))
580+
return false;
581+
582+
}
583+
return true;
584+
}
585+
586+
/*
587+
* Helper routine to recurse through setOperations tree
588+
*/
589+
staticbool
590+
recurse_pushdown_safe(Node*setOp,Query*topquery)
591+
{
592+
if (IsA(setOp,RangeTblRef))
593+
{
594+
RangeTblRef*rtr= (RangeTblRef*)setOp;
595+
RangeTblEntry*rte=rt_fetch(rtr->rtindex,topquery->rtable);
596+
Query*subquery=rte->subquery;
597+
598+
Assert(subquery!=NULL);
599+
returnsubquery_is_pushdown_safe(subquery,topquery);
600+
}
601+
elseif (IsA(setOp,SetOperationStmt))
602+
{
603+
SetOperationStmt*op= (SetOperationStmt*)setOp;
604+
605+
/* EXCEPT is no good */
606+
if (op->op==SETOP_EXCEPT)
607+
return false;
608+
/* Else recurse */
609+
if (!recurse_pushdown_safe(op->larg,topquery))
610+
return false;
611+
if (!recurse_pushdown_safe(op->rarg,topquery))
612+
return false;
613+
}
614+
else
615+
{
616+
elog(ERROR,"recurse_pushdown_safe: unexpected node %d",
617+
(int)nodeTag(setOp));
618+
}
619+
return true;
620+
}
621+
622+
/*
623+
* subquery_push_qual - push down a qual that we have determined is safe
624+
*/
625+
staticvoid
626+
subquery_push_qual(Query*subquery,Indexrti,Node*qual)
627+
{
628+
if (subquery->setOperations!=NULL)
629+
{
630+
/* Recurse to push it separately to each component query */
631+
recurse_push_qual(subquery->setOperations,subquery,rti,qual);
632+
}
633+
else
634+
{
635+
/*
636+
* We need to replace Vars in the qual (which must refer
637+
* to outputs of the subquery) with copies of the
638+
* subquery's targetlist expressions. Note that at this
639+
* point, any uplevel Vars in the qual should have been
640+
* replaced with Params, so they need no work.
641+
*
642+
* This step also ensures that when we are pushing into a setop
643+
* tree, each component query gets its own copy of the qual.
644+
*/
645+
qual=ResolveNew(qual,rti,0,
646+
subquery->targetList,
647+
CMD_SELECT,0);
648+
subquery->havingQual=make_and_qual(subquery->havingQual,
649+
qual);
650+
651+
/*
652+
* We need not change the subquery's hasAggs or
653+
* hasSublinks flags, since we can't be pushing down any
654+
* aggregates that weren't there before, and we don't push
655+
* down subselects at all.
656+
*/
657+
}
658+
}
659+
660+
/*
661+
* Helper routine to recurse through setOperations tree
662+
*/
663+
staticvoid
664+
recurse_push_qual(Node*setOp,Query*topquery,
665+
Indexrti,Node*qual)
666+
{
667+
if (IsA(setOp,RangeTblRef))
668+
{
669+
RangeTblRef*rtr= (RangeTblRef*)setOp;
670+
RangeTblEntry*rte=rt_fetch(rtr->rtindex,topquery->rtable);
671+
Query*subquery=rte->subquery;
672+
673+
Assert(subquery!=NULL);
674+
subquery_push_qual(subquery,rti,qual);
675+
}
676+
elseif (IsA(setOp,SetOperationStmt))
677+
{
678+
SetOperationStmt*op= (SetOperationStmt*)setOp;
679+
680+
recurse_push_qual(op->larg,topquery,rti,qual);
681+
recurse_push_qual(op->rarg,topquery,rti,qual);
682+
}
683+
else
684+
{
685+
elog(ERROR,"recurse_push_qual: unexpected node %d",
686+
(int)nodeTag(setOp));
687+
}
688+
}
689+
690+
/*****************************************************************************
691+
*DEBUG SUPPORT
551692
*****************************************************************************/
552693

553694
#ifdefOPTIMIZER_DEBUG

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

Lines changed: 2 additions & 3 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.75 2002/08/02 18:15:06 tgl Exp $
17+
* $Header: /cvsroot/pgsql/src/backend/optimizer/prep/prepunion.c,v 1.76 2002/08/29 16:03:48 tgl Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -66,7 +66,6 @@ static List *generate_setop_tlist(List *colTypes, int flag,
6666
staticList*generate_append_tlist(List*colTypes,boolflag,
6767
List*input_plans,
6868
List*refnames_tlist);
69-
staticbooltlist_same_datatypes(List*tlist,List*colTypes,booljunkOK);
7069
staticNode*adjust_inherited_attrs_mutator(Node*node,
7170
adjust_inherited_attrs_context*context);
7271

@@ -579,7 +578,7 @@ generate_append_tlist(List *colTypes, bool flag,
579578
* Resjunk columns are ignored if junkOK is true; otherwise presence of
580579
* a resjunk column will always cause a 'false' result.
581580
*/
582-
staticbool
581+
bool
583582
tlist_same_datatypes(List*tlist,List*colTypes,booljunkOK)
584583
{
585584
List*i;

‎src/include/optimizer/prep.h

Lines changed: 3 additions & 1 deletion
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.32 2002/06/20 20:29:51 momjian Exp $
10+
* $Id: prep.h,v 1.33 2002/08/29 16:03:49 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -43,4 +43,6 @@ extern Node *adjust_inherited_attrs(Node *node,
4343
Indexold_rt_index,Oidold_relid,
4444
Indexnew_rt_index,Oidnew_relid);
4545

46+
externbooltlist_same_datatypes(List*tlist,List*colTypes,booljunkOK);
47+
4648
#endif/* PREP_H */

0 commit comments

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