Plan not skipping unnecessary inner join

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)

Responses

Browse pgsql-performance by date

  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