User-Agent: Cyrus-JMAP/3.3.0-dev0-413-g750b809-fmstable-20200507v1
Mime-Version: 1.0
Message-Id: <808d494b-2d04-4811-a6f7-4fa3beab412e@www.fastmail.com>
In-Reply-To: <9FFFC18C-C0C0-48A0-A61C-54381D322617@gmail.com>
References: <677075a1-50bf-48b7-b7b6-ecebb8786741@www.fastmail.com>
 <9FFFC18C-C0C0-48A0-A61C-54381D322617@gmail.com>
Date: Fri, 15 May 2020 10:36:58 -0700
From: "Matthew Nelson" <postgres@binarykeep.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Plan not skipping unnecessary inner join
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

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 ca=
n take a shortcut when it finds a unique constraint, it does not check f=
or foreign key constraints matching the join condition? So the LEFT JOIN=
 simplification is possible 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?
>=20
> Yes it will impact the number of rows in the result. for example if fo=
o=20
> is empty then postgres is required to return no results, regardless of=
=20
> how many rows are in bar. This is why it can ignore the table in the=20=

> left join
>=20
> =E2=80=94 David
>=20
> > On 14 May 2020, at 1:44 pm, Matthew Nelson <postgres@binarykeep.com>=
 wrote:
> >=20
> > I noticed something peculiar while optimizing complex views today. T=
he query planner does not skip inner joins that, to my understanding, ca=
n have no impact on the result. Am I missing a situation where these joi=
ns could impact the result?
> >=20
> > The following demonstrates the problem without the complex views. It=
 also demonstrates how the planner simplifies a LEFT JOIN in the same si=
tuation. The left and right sides of an inner join could be swapped, obv=
iously, but here I kept the unique constraint on the right.
> >=20
> >=20
> >=20
> > CREATE TABLE foo (
> >        id INTEGER PRIMARY KEY
> > );
> >=20
> > CREATE TABLE bar (
> >        foo_id INTEGER NOT NULL REFERENCES foo
> > );
> >=20
> > -- This simplifies to SELECT COUNT(*) FROM bar;
> > EXPLAIN SELECT COUNT(*)
> > FROM bar
> > LEFT JOIN foo ON bar.foo_id =3D foo.id;
> >=20
> > -- 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 =3D foo.id;
> >=20
> >=20
> >=20
> >                         QUERY PLAN                         =20
> > -------------------------------------------------------------
> > Aggregate  (cost=3D38.25..38.26 rows=3D1 width=3D8)
> >   ->  Seq Scan on bar  (cost=3D0.00..32.60 rows=3D2260 width=3D0)
> > (2 rows)
> >=20
> >                               QUERY PLAN                            =
   =20
> > --------------------------------------------------------------------=
-----
> > Aggregate  (cost=3D111.57..111.58 rows=3D1 width=3D8)
> >   ->  Hash Join  (cost=3D67.38..105.92 rows=3D2260 width=3D0)
> >         Hash Cond: (bar.foo_id =3D foo.id)
> >         ->  Seq Scan on bar  (cost=3D0.00..32.60 rows=3D2260 width=3D=
4)
> >         ->  Hash  (cost=3D35.50..35.50 rows=3D2550 width=3D4)
> >               ->  Seq Scan on foo  (cost=3D0.00..35.50 rows=3D2550 w=
idth=3D4)
> > (6 rows)
> >=20
> >                                                      version        =
                                             =20
> > --------------------------------------------------------------------=
-----------------------------------------------
> > PostgreSQL 12.2 on x86_64-apple-darwin19.4.0, compiled by Apple clan=
g version 11.0.3 (clang-1103.0.32.59), 64-bit
> > (1 row)
> >=20
> >=20
>=20
>