> 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
Sorry, I should have tested it again on the HEAD, and 608b167f9 is exactly
the thing I mean.
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)
