On Sat, Nov 14, 2020 at 2:04 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
>
> Hi:
>
> 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;
>
> I know how we treat cte and subqueries differently currently,
> I just don't know why we can't treat cte as a subquery, so lots of
> subquery related technology can apply to it. Do we have any
> discussion about this?
See https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=608b167f9f9c4553c35bb1ec0eab9ddae643989b