On Sat, Nov 14, 2020 at 2:14 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > 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 > > 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) > > regards, tom lane > -- Best Regards Andy Fan
