Re: Plan not skipping unnecessary inner join - Mailing list pgsql-performance

But foo cannot be empty given the foreign key constraint on bar.foo_id.

I suppose that explains the planner's behavior. While today's planner can take a shortcut when it finds a unique
constraint,it does not check for foreign key constraints matching the join condition? So the LEFT JOIN simplification
ispossible but not the INNER JOIN simplification. 

Matthew

On Wed, May 13, 2020, at 11:45 PM, David Wheeler wrote:
> > Am I missing a situation where these joins could impact the result?
>
> Yes it will impact the number of rows in the result. for example if foo
> is empty then postgres is required to return no results, regardless of
> how many rows are in bar. This is why it can ignore the table in the
> left join
>
> — David
>
> > On 14 May 2020, at 1:44 pm, Matthew Nelson <postgres@binarykeep.com> wrote:
> >
> > 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
LEFTJOIN in the same situation. The left and right sides of an inner join could be swapped, obviously, but here I kept
theunique 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 = 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)
> >
> >
>
>