Re: Plan not skipping unnecessary inner join

> 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(at)binarykeep(dot)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 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)
>
>