CTE Scan

Another new PostgreSQL 8.4 feature, Common Table Expressions (CTE) add new efficient ways to execute queries that even let you put recursion into SQL. One way they can be used is as a sort of in-line view, which makes for an easy way to demonstrate the feature and the resulting type of plan node:

EXPLAIN ANALYZE WITH monthlysales AS 
  (SELECT EXTRACT(year FROM orderdate) AS year, 
   EXTRACT(month FROM orderdate) AS month,
   sum(netamount) AS sales 
   FROM orders GROUP BY year,month)
SELECT year,SUM(sales) AS sales FROM monthlysales GROUP BY year;
QUERY PLAN
----------
 HashAggregate  (cost=447.34..449.84 rows=200 width=40) (actual time=122.002..122.004 rows=1 loops=1)
   CTE monthlysales
     ->  HashAggregate  (cost=430.00..438.21 rows=365 width=12) (actual time=121.872..121.893 rows=12 loops=1)
           ->  Seq Scan on orders  (cost=0.00..340.00 rows=12000 width=12) (actual time=0.026..56.812 rows=12000 loops=1)
   ->  CTE Scan on monthlysales  (cost=0.00...