Re: Have we tried to treat CTE as SubQuery in planner?

Andy Fan <zhihui(dot)fan1213(at)gmail(dot)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