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

On Sat, Nov 14, 2020 at 2:44 PM Jesse Zhang <sbjesse(at)gmail(dot)com> wrote:

> Hi,
>
> On Fri, Nov 13, 2020 at 10:04 PM Andy Fan 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;
> >
>
> ITYM:
>
> EXPLAIN
> WITH c AS (SELECT * FROM cte1)
> SELECT * FROM c WHERE a = 1;
>
> I'm also guessing your table DDL is:
>
> CREATE TABLE cte1 (a int, b int);
>
> > 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;
>
> Does it? On HEAD, I got the following plan:
>
>
You understand me correctly, just too busy recently and make
me make mistakes like this. Sorry about that:(

> (without stats):
> Bitmap Heap Scan on foo
> Recheck Cond: (a = 1)
> -> Bitmap Index Scan on foo_a_idx
> Index Cond: (a = 1)
>
> (with stats):
> Index Scan using foo_a_idx on foo
> Index Cond: (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?
>
> This was brought up a few times, the most recent one I can recall was a
> little bit over two years ago [1]
>
> [1] https://postgr.es/m/87sh48ffhb.fsf@news-spur.riddles.org.uk

And I should have searched "CTE" at least for a while..

--
Best Regards
Andy Fan