Re: order of clauses

Peter Eisentraut <peter_e@gmx.net> writes:
> Subselects in FROM are currently not the most performance-encouraging way
> to write a query (explicit JOINs might use more efficient plans), but
> setting up a view is going to buy you zero because a query from a view is
> just going to end up being processed like a subselect in FROM.

Actually there's more to it than that.  A view will indeed be expanded
into something that looks exactly like a subselect-in-FROM (think of the
view as a macro that gets expanded).  But downstream of that, the
planner will try to "pull up" the subselect into the main query if it's
simple enough.  If the pullup is successful, then there's no performance
penalty to having written a view rather than an explicit join.  What's
more, because it's done that way, the same applies to explicitly written
subselect-in-FROM.

For example, consider this literal join:

regression=# explain select * from int8_tbl a, int8_tbl b where a.q1=b.q2;
NOTICE:  QUERY PLAN:

Merge Join  (cost=2.22..2.34 rows=5 width=32)
  ->  Sort  (cost=1.11..1.11 rows=5 width=16)
        ->  Seq Scan on int8_tbl a  (cost=0.00..1.05 rows=5 width=16)
  ->  Sort  (cost=1.11..1.11 rows=5 width=16)
        ->  Seq Scan on int8_tbl b  (cost=0.00..1.05 rows=5 width=16)

EXPLAIN

If you recast this with a subselect, it still gets the same plan because
the subselect is absorbed into the upper query:

regression=# explain select * from int8_tbl a,
regression-# (select * from int8_tbl) b where a.q1=b.q2;
NOTICE:  QUERY PLAN:

Merge Join  (cost=2.22..2.34 rows=5 width=32)
  ->  Sort  (cost=1.11..1.11 rows=5 width=16)
        ->  Seq Scan on int8_tbl a  (cost=0.00..1.05 rows=5 width=16)
  ->  Sort  (cost=1.11..1.11 rows=5 width=16)
        ->  Seq Scan on int8_tbl  (cost=0.00..1.05 rows=5 width=16)

EXPLAIN

However the planner is not currently bright enough to pull up a subquery
with, say, an ORDER BY:

regression=# explain select * from int8_tbl a,
regression-# (select * from int8_tbl order by q1) b where a.q1=b.q2;
NOTICE:  QUERY PLAN:

Merge Join  (cost=2.27..2.40 rows=5 width=32)
  ->  Sort  (cost=1.11..1.11 rows=5 width=16)
        ->  Seq Scan on int8_tbl a  (cost=0.00..1.05 rows=5 width=16)
  ->  Sort  (cost=1.17..1.17 rows=5 width=16)
        ->  Subquery Scan b  (cost=1.11..1.11 rows=5 width=16)
              ->  Sort  (cost=1.11..1.11 rows=5 width=16)
                    ->  Seq Scan on int8_tbl  (cost=0.00..1.05 rows=5 width=16)

EXPLAIN
regression=#

In this case the outer join plan doesn't change, but it might if we were
considering something where a nestloop with inner indexscan would have
been the best plan.  An indexscan has to be on a base relation, not on a
SubqueryScan node, so the planner would fail to discover the best plan
without the pullup.

The results would be the same if I'd done CREATE VIEWS rather than
writing explicit subselect-in-FROM.

            regards, tom lane