Andy Fan <zhihui.fan1...@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
