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

On Sat, Nov 14, 2020 at 2:14 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>
>
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