Re: Have we tried to treat CTE as SubQuery in planner? - Mailing list pgsql-hackers

Andy Fan <zhihui.fan1213@gmail.com> writes:
> Take the following example:

> insert into cte1  select i, i from generate_series(1, 1000000)i;
> create index on cte1(a);

> explain
> with cte1 as  (select * from cte1)
> select * from c where a = 1;

> It needs to do seq scan on the above format, however it is pretty
> quick if we change the query to
> select * from (select * from cte1) c where a = 1;

This example seems both confused and out of date.  Since we changed
the rules on materializing CTEs (in 608b167f9), I get

regression=# create table c as select i as a, i from generate_series(1, 1000000)i;
SELECT 1000000
regression=# create index on c(a);
CREATE INDEX
regression=# explain
regression-# with cte1 as (select * from c)
regression-# select * from cte1 where a = 1;
                                QUERY PLAN
--------------------------------------------------------------------------
 Bitmap Heap Scan on c  (cost=95.17..4793.05 rows=5000 width=8)
   Recheck Cond: (a = 1)
   ->  Bitmap Index Scan on c_a_idx  (cost=0.00..93.92 rows=5000 width=0)
         Index Cond: (a = 1)
(4 rows)

            regards, tom lane