On Sat, Nov 14, 2020 at 2:44 PM Jesse Zhang <sbjesse@gmail.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]
