Re: Propogating conditions into a query

Phil Endecott <spam_from_postgresql_general@chezphil.org> writes:
> I have a number of complex views for which the typical use is to select
> exactly one row by id, e.g.  "select * from V where id=nnn".  Some of
> these selects run orders of magnitude faster than others.  Looking at
> the output of "explain analyse" it seems that in the fast cases the
> "id=nnn" condition is passed down to the lower-level operations, while
> in the slower cases the entire view is created and then filtered using
> the condition as a final step.

> I am trying to narrow down what types of query I can use in the views to
> avoid the poor performance.

When in doubt, use the source ;-) ... most sorts of things like this are
pretty well commented, if you can find the relevant code.  In this case
what you are wanting is that the view subquery either get "pulled up"
into the calling query, or that conditions from the calling query get
"pushed down" into the subquery.  The former transformation is done in
src/backend/optimizer/prep/prepjointree.c, and the principal conditions
are checked in is_simple_subquery():

    /*
     * Can't currently pull up a query with setops. Maybe after querytree
     * redesign...
     */
    if (subquery->setOperations)
        return false;

    /*
     * Can't pull up a subquery involving grouping, aggregation, sorting,
     * or limiting.
     */
    if (subquery->hasAggs ||
        subquery->groupClause ||
        subquery->havingQual ||
        subquery->sortClause ||
        subquery->distinctClause ||
        subquery->limitOffset ||
        subquery->limitCount)
        return false;

    /*
     * Don't pull up a subquery that has any set-returning functions in
     * its targetlist.    Otherwise we might well wind up inserting
     * set-returning functions into places where they mustn't go, such as
     * quals of higher queries.
     */
    if (expression_returns_set((Node *) subquery->targetList))
        return false;

    /*
     * Hack: don't try to pull up a subquery with an empty jointree.
     * query_planner() will correctly generate a Result plan for a
     * jointree that's totally empty, but I don't think the right things
     * happen if an empty FromExpr appears lower down in a jointree. Not
     * worth working hard on this, just to collapse SubqueryScan/Result
     * into Result...
     */
    if (subquery->jointree->fromlist == NIL)
        return false;

    return true;

The push-down optimization is done in
src/backend/optimizer/path/allpaths.c, and that makes tests on both the
subquery involved and the qualification condition to be pushed down:

/*
 * subquery_is_pushdown_safe - is a subquery safe for pushing down quals?
 *
 * subquery is the particular component query being checked.  topquery
 * is the top component of a set-operations tree (the same Query if no
 * set-op is involved).
 *
 * Conditions checked here:
 *
 * 1. If the subquery has a LIMIT clause, we must not push down any quals,
 * since that could change the set of rows returned.
 *
 * 2. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
 * quals into it, because that would change the results.
 *
 * 3. For subqueries using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can
 * push quals into each component query, but the quals can only reference
 * subquery columns that suffer no type coercions in the set operation.
 * Otherwise there are possible semantic gotchas.  So, we check the
 * component queries to see if any of them have different output types;
 * differentTypes[k] is set true if column k has different type in any
 * component.
 */

/*
 * qual_is_pushdown_safe - is a particular qual safe to push down?
 *
 * qual is a restriction clause applying to the given subquery (whose RTE
 * has index rti in the parent query).
 *
 * Conditions checked here:
 *
 * 1. The qual must not contain any subselects (mainly because I'm not sure
 * it will work correctly: sublinks will already have been transformed into
 * subplans in the qual, but not in the subquery).
 *
 * 2. The qual must not refer to any subquery output columns that were
 * found to have inconsistent types across a set operation tree by
 * subquery_is_pushdown_safe().
 *
 * 3. If the subquery uses DISTINCT ON, we must not push down any quals that
 * refer to non-DISTINCT output columns, because that could change the set
 * of rows returned.  This condition is vacuous for DISTINCT, because then
 * there are no non-DISTINCT output columns, but unfortunately it's fairly
 * expensive to tell the difference between DISTINCT and DISTINCT ON in the
 * parsetree representation.  It's cheaper to just make sure all the Vars
 * in the qual refer to DISTINCT columns.
 *
 * 4. We must not push down any quals that refer to subselect outputs that
 * return sets, else we'd introduce functions-returning-sets into the
 * subquery's WHERE/HAVING quals.
 */

            regards, tom lane