Hi,
using `PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit`, I've observed the
following behavior:
– keep in mind that this example is as simplified as possible, the
original query involves foreign tables, and the failure to propagate /
push down the condition results in a query plan that basically tries to
download the complete foreign table, which is not a feasible execution
strategy:
Setup:
CREATE TABLE tbl1 (id INTEGER GENERATED ALWAYS AS IDENTITY, site_id
INTEGER NOT NULL, data TEXT);
CREATE TABLE tbl2 (id INTEGER GENERATED ALWAYS AS IDENTITY, site_id
INTEGER NOT NULL, data TEXT);
CREATE INDEX ON tbl1 (site_id);
CREATE INDEX ON tbl2 (site_id);
Working queries:
SELECT * FROM tbl1 WHERE tbl1.site_id = 1; -- "trivial condition"
SELECT * FROM tbl2 WHERE tbl2.site_id = 1;
SELECT * FROM tbl1 WHERE tbl1.site_id = 1 OR tbl1.site_id IS NULL; --
"non-trivial condition"
SELECT * FROM tbl2 WHERE tbl2.site_id = 1 OR tbl2.site_id IS NULL;
1) Exemplary Query Plan:
# EXPLAIN SELECT * FROM tbl2 WHERE tbl2.site_id = 1 OR tbl2.site_id IS NULL;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl2 (cost=8.40..19.08 rows=12 width=40)
Recheck Cond: ((site_id = 1) OR (site_id IS NULL))
-> BitmapOr (cost=8.40..8.40 rows=12 width=0)
-> Bitmap Index Scan on tbl2_site_id_idx (cost=0.00..4.20
rows=6 width=0)
Index Cond: (site_id = 1)
-> Bitmap Index Scan on tbl2_site_id_idx (cost=0.00..4.20
rows=6 width=0)
Index Cond: (site_id IS NULL)
(7 rows)
The key takeaway is, that the index can be used, because the condition
is propagated deep enough.
2) Still working example:
# EXPLAIN SELECT * FROM tbl1 LEFT JOIN tbl2 ON tbl2.site_id =
tbl1.site_id WHERE tbl1.site_id = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=8.40..27.80 rows=36 width=80)
-> Bitmap Heap Scan on tbl1 (cost=4.20..13.67 rows=6 width=40)
Recheck Cond: (site_id = 1)
-> Bitmap Index Scan on tbl1_site_id_idx (cost=0.00..4.20
rows=6 width=0)
Index Cond: (site_id = 1)
-> Materialize (cost=4.20..13.70 rows=6 width=40)
-> Bitmap Heap Scan on tbl2 (cost=4.20..13.67 rows=6 width=40)
Recheck Cond: (site_id = 1)
-> Bitmap Index Scan on tbl2_site_id_idx
(cost=0.00..4.20 rows=6 width=0)
Index Cond: (site_id = 1)
(10 rows)
The condition is propagated into BOTH branches of the join. The join
could also be an INNER join and might also be realized as a Merge Join
or Hash Join: they all behave the same.
3) Problematic example:
# EXPLAIN SELECT * FROM tbl1 JOIN tbl2 ON tbl2.site_id = tbl1.site_id
WHERE tbl1.site_id = 1 OR tbl1.site_id IS NULL;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Hash Join (cost=19.23..46.45 rows=72 width=80)
Hash Cond: (tbl2.site_id = tbl1.site_id)
-> Seq Scan on tbl2 (cost=0.00..22.00 rows=1200 width=40)
-> Hash (cost=19.08..19.08 rows=12 width=40)
-> Bitmap Heap Scan on tbl1 (cost=8.40..19.08 rows=12 width=40)
Recheck Cond: ((site_id = 1) OR (site_id IS NULL))
-> BitmapOr (cost=8.40..8.40 rows=12 width=0)
-> Bitmap Index Scan on tbl1_site_id_idx
(cost=0.00..4.20 rows=6 width=0)
Index Cond: (site_id = 1)
-> Bitmap Index Scan on tbl1_site_id_idx
(cost=0.00..4.20 rows=6 width=0)
Index Cond: (site_id IS NULL)
(11 rows)
Now, a full seq scan used for tbl2, the condition is only pushed down on
ONE side of the JOIN!
(with `WHERE tbl2.site_id = 1 OR tbl2.site_id IS NULL`, the Seq Scan
would have been on tbl1... [not so easily demostrated w/ LEFT JOINs]).
Also, `ON tbl1.site_id IS NOT DISTINCT FROM tbl2.site_id` does not help,
The weird thing is: The subqueries on both sides of the join are
perfectly capable of accepting/using the "non-trivial" condition, as
demonstrated in 1), and JOINs are generally able to propagate conditions
to both sides, as demonstrated in 2).
Is there a magic knob to force postgres to do the right thing, or is
this basically a bug in the query planner?
Tobias
