PostgreSQL 10 High Performance

CTE scan

Another new PostgreSQL 8.4 feature, Common Table Expressions (CTEs) 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) ...