From: Matthew Nelson <postgres@binarykeep.com>
To: pgsql-performance@lists.postgresql.org
Subject: Plan not skipping unnecessary inner join
Date: Wed, 13 May 2020 22:44:56 -0700
Message-ID: <677075a1-50bf-48b7-b7b6-ecebb8786741@www.fastmail.com> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: postgres@binarykeep.com, pgsql-performance@lists.postgresql.org
Subject: Re: Plan not skipping unnecessary inner join
In-Reply-To: <677075a1-50bf-48b7-b7b6-ecebb8786741@www.fastmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox