From: | Dmytro Astapov <dastapov(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Surprising SeqScan of appendRel that can't contribute any rows to the result |
Date: | 2024-12-17 22:15:44 |
Message-ID: | CAFQUnFh662SNr8pmJt2BDimTwXvMnv-cRBAECAHP5sG7U+6rLw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi!
OS: Debian, Rock Linux
Postgres versions: 13.6, 15.6, 17.0
Setup:
create table partA(id bigint not null, payload bigint);
insert into partA select s, s from generate_series(1,10000) s;
analyze partA;
create index on partA(id);
create index on partA(payload);
create table partB(id bigint not null);
insert into partB select s from generate_series(1,10000) s;
analyze partB;
create index on partB(id);
create view vw as
select id, payload from partA
union all
select id, NULL as payload from partB;
As you can see, we have a view that UNION ALLs two tables with different
number of columns. Missing column from partB is stubbed out with a constant
NULL.
Now we want to join this view with a small table that has some numbers that
we want to find in the `payload` column:
create table some_ids(id bigint not null);
insert into some_ids select s from generate_series(1,2) s;
analyze some_ids;
explain select * from some_ids i join vw on (vw.payload = i.id);
Surprisingly, this does SeqScan on partB in NestedLoops over some_ids with
a filter `some_ids.id = NULL::bigint`:
Nested Loop (cost=0.29..359.16 rows=200 width=24)
-> Seq Scan on some_ids i (cost=0.00..1.02 rows=2 width=8)
-> Append (cost=0.29..178.56 rows=51 width=16)
-> Index Scan using parta_payload_idx on parta (cost=0.29..8.30
rows=1 width=16)
Index Cond: (payload = i.id)
-> Seq Scan on partb (cost=0.00..170.00 rows=50 width=16)
Filter: (i.id = NULL::bigint)
At the same time `explain select * from vw where payload = 1` correctly
skips over partB entirely (the node is eliminated from execution plan), and
so does:
explain select * from vw where payload in (1,2);
However, any query that does not use explicit literal values still leads to
SeqScan access on partB, such as:
explain select * from vw where payload in (select id from some_ids);
explain select * from vw where payload = ANY(ARRAY(select id from
some_ids));
or various forms of joins
Do you know if this is expected/documented, or is this a bug?
Same setup in db-fiddle if you want to give it a quick spin:
https://www.db-fiddle.com/f/hNLCR9wou9TYzcLG57q9kj/3 or
https://dbfiddle.uk/5o5LQlEB
Best regards, Dmytro
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2024-12-18 00:08:10 | Re: Surprising SeqScan of appendRel that can't contribute any rows to the result |
Previous Message | Tomasz Szypowski | 2024-12-17 20:29:20 | Not able to restore database - error: could not decompress data: Allocation error : not enough memory |