Skip to content

Commit 0201dac

Browse files
tglsfdctglsfdc
committed
Push down outer qualification clauses into UNION and INTERSECT subqueries.
Per pghackers discussion from back around 1-August.
1 parent d223680 commit 0201dac

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
static RelOptInfo *make_one_rel_by_joins(Query *root, int levels_needed,
4848
List *initial_rels);
49+
static bool subquery_is_pushdown_safe(Query *subquery, Query *topquery);
50+
static bool recurse_pushdown_safe(Node *setOp, Query *topquery);
51+
static void subquery_push_qual(Query *subquery, Index rti, Node *qual);
52+
static void recurse_push_qual(Node *setOp, Query *topquery,
53+
Index rti, 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+
static bool
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+
static bool
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+
return subquery_is_pushdown_safe(subquery, topquery);
600+
}
601+
else if (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+
static void
626+
subquery_push_qual(Query *subquery, Index rti, 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+
static void
664+
recurse_push_qual(Node *setOp, Query *topquery,
665+
Index rti, 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+
else if (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
#ifdef OPTIMIZER_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
static List *generate_append_tlist(List *colTypes, bool flag,
6767
List *input_plans,
6868
List *refnames_tlist);
69-
static bool tlist_same_datatypes(List *tlist, List *colTypes, bool junkOK);
7069
static Node *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-
static bool
581+
bool
583582
tlist_same_datatypes(List *tlist, List *colTypes, bool junkOK)
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
Index old_rt_index, Oid old_relid,
4444
Index new_rt_index, Oid new_relid);
4545

46+
extern bool tlist_same_datatypes(List *tlist, List *colTypes, bool junkOK);
47+
4648
#endif /* PREP_H */

0 commit comments

Comments
 (0)