I noticed something peculiar while optimizing complex views today. The query
planner does not skip inner joins that, to my understanding, can have no impact
on the result. Am I missing a situation where these joins could impact the
result?
The following demonstrates the problem without the complex views. It also
demonstrates how the planner simplifies a LEFT JOIN in the same situation. The
left and right sides of an inner join could be swapped, obviously, but here I
kept the unique constraint on the right.
CREATE TABLE foo (
id INTEGER PRIMARY KEY
);
CREATE TABLE bar (
foo_id INTEGER NOT NULL REFERENCES foo
);
-- This simplifies to SELECT COUNT(*) FROM bar;
EXPLAIN SELECT COUNT(*)
FROM bar
LEFT JOIN foo ON bar.foo_id = foo.id;
-- This should simplify to SELECT COUNT(*) FROM bar WHERE foo_id IS NOT NULL;
-- The presence of a NOT NULL constraint on foo_id has no effect.
EXPLAIN SELECT COUNT(*)
FROM bar
INNER JOIN foo ON bar.foo_id = foo.id;
QUERY PLAN
-------------------------------------------------------------
Aggregate (cost=38.25..38.26 rows=1 width=8)
-> Seq Scan on bar (cost=0.00..32.60 rows=2260 width=0)
(2 rows)
QUERY PLAN
-------------------------------------------------------------------------
Aggregate (cost=111.57..111.58 rows=1 width=8)
-> Hash Join (cost=67.38..105.92 rows=2260 width=0)
Hash Cond: (bar.foo_id_not_null = foo.id)
-> Seq Scan on bar (cost=0.00..32.60 rows=2260 width=4)
-> Hash (cost=35.50..35.50 rows=2550 width=4)
-> Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4)
(6 rows)
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.2 on x86_64-apple-darwin19.4.0, compiled by Apple clang version
11.0.3 (clang-1103.0.32.59), 64-bit
(1 row)
