PostgreSQL and CTEs

gistfile1.md

Why doesn't PostgreSQL optimize Common Table Expressions?

PostgreSQL does not seem to optimize common table expressions (CTE) very well. At the same time, is seems to optimize properly when views are involved. Any idea why? Is that accidental or is it a technical/theoretical/essential reason? Examples below.

With CTE, restriction after join

Does not seem optimized.

sap=# explain with j as (select * from suppliers natural join shipments) select * from j where status > 20;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 CTE Scan on j  (cost=80.85..119.10 rows=567 width=70)
   Filter: (status > 20)
   CTE j
     ->  Hash Join  (cost=30.48..80.85 rows=1700 width=70)
           Hash Cond: ((shipments.sid)::text = (suppliers.sid)::text)
           ->  Seq Scan on shipments  (cost=0.00..27.00 rows=1700 width=18)
           ->  Hash  (cost=19.10..19.10 rows=910 width=59)
                 ->  Seq Scan on suppliers  (cost=0.00..19.10 rows=910 width=59)
(8 rows)

Manually inlined

Seems optimized.

sap=# explain select * from suppliers natural join shipments where status > 20;
                               QUERY PLAN
-------------------------------------------------------------------------
 Hash Join  (cost=25.16..64.20 rows=566 width=70)
   Hash Cond: ((shipments.sid)::text = (suppliers.sid)::text)
   ->  Seq Scan on shipments  (cost=0.00..27.00 rows=1700 width=18)
   ->  Hash  (cost=21.38..21.38 rows=303 width=59)
         ->  Seq Scan on suppliers  (cost=0.00..21.38 rows=303 width=59)
               Filter: (status > 20)
(6 rows)

With a view

Seems optimized.

sap=# create view j as select * from suppliers natural join shipments;
CREATE VIEW
sap=# select * from j where status > 20;
 sid | name  | status | city  | pid | qty
-----+-------+--------+-------+-----+-----
 S3  | Blake |     30 | Paris | P2  | 200
(1 row)

sap=# explain select * from j where status > 20;
                               QUERY PLAN
-------------------------------------------------------------------------
 Hash Join  (cost=25.16..64.20 rows=566 width=70)
   Hash Cond: ((shipments.sid)::text = (suppliers.sid)::text)
   ->  Seq Scan on shipments  (cost=0.00..27.00 rows=1700 width=18)
   ->  Hash  (cost=21.38..21.38 rows=303 width=59)
         ->  Seq Scan on suppliers  (cost=0.00..21.38 rows=303 width=59)
               Filter: (status > 20)
(6 rows)

This can be a good thing sometimes.