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