User-Agent: Cyrus-JMAP/3.3.0-dev0-413-g750b809-fmstable-20200507v1
Mime-Version: 1.0
Message-Id: <677075a1-50bf-48b7-b7b6-ecebb8786741@www.fastmail.com>
Date: Wed, 13 May 2020 22:44:56 -0700
From: "Matthew Nelson" <postgres@binarykeep.com>
To: pgsql-performance@lists.postgresql.org
Subject: Plan not skipping unnecessary inner join
Content-Type: text/plain
Precedence: bulk

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)