From: | "Matthew Nelson" <postgres(at)binarykeep(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Plan not skipping unnecessary inner join |
Date: | 2020-05-14 05:44:56 |
Message-ID: | 677075a1-50bf-48b7-b7b6-ecebb8786741@www.fastmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | David Wheeler | 2020-05-14 06:45:23 | Re: Plan not skipping unnecessary inner join |
Previous Message | James Thompson | 2020-05-13 14:17:03 | Re: Please help! Query jumps from 1s -> 4m |