pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPlan not skipping unnecessary inner join
6+ messages / 4 participants
[nested] [flat]
* Plan not skipping unnecessary inner join
@ 2020-05-14 05:44 Matthew Nelson <postgres@binarykeep.com>
0 siblings, 1 reply; 6+ messages in thread
From: Matthew Nelson @ 2020-05-14 05:44 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Plan not skipping unnecessary inner join
@ 2020-05-14 06:45 David Wheeler <hippysoyboy@gmail.com>
parent: Matthew Nelson <postgres@binarykeep.com>
0 siblings, 1 reply; 6+ messages in thread
From: David Wheeler @ 2020-05-14 06:45 UTC (permalink / raw)
To: Matthew Nelson <postgres@binarykeep.com>; +Cc: pgsql-performance@lists.postgresql.org
> 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 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)
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Plan not skipping unnecessary inner join
@ 2020-05-15 17:36 Matthew Nelson <postgres@binarykeep.com>
parent: David Wheeler <hippysoyboy@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Matthew Nelson @ 2020-05-15 17:36 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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 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?
>
> 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 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 = 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)
> >
> >
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Plan not skipping unnecessary inner join
@ 2020-05-17 12:32 Ranier Vilela <ranier.vf@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ranier Vilela @ 2020-05-17 12:32 UTC (permalink / raw)
To: pgsql-performance; +Cc: hippysoyboy@gmail.com; postgres@binarykeep.com
I redid the same tests with vanila postgres and with empty tables.
I'm surprised, why does the plan have 2550 rows in explain?
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Plan not skipping unnecessary inner join
@ 2020-05-17 13:31 Justin Pryzby <pryzby@telsasoft.com>
parent: Ranier Vilela <ranier.vf@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Justin Pryzby @ 2020-05-17 13:31 UTC (permalink / raw)
To: Ranier Vilela <ranier.vf@gmail.com>; +Cc: pgsql-performance; hippysoyboy@gmail.com; postgres@binarykeep.com
On Sun, May 17, 2020 at 09:32:47AM -0300, Ranier Vilela wrote:
> I redid the same tests with vanila postgres and with empty tables.
> I'm surprised, why does the plan have 2550 rows in explain?
That's the *estimated* rowcount.
The planner tends to ignore table statistics which say the table is empty,
since that can lead to a terrible plan if it's not true (stats are out of date
or autovacuum threshold not hit).
See also here
https://www.postgresql.org/message-id/20171110204043.GS8563%40telsasoft.com
--
Justin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Plan not skipping unnecessary inner join
@ 2020-05-17 14:05 Ranier Vilela <ranier.vf@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 6+ messages in thread
From: Ranier Vilela @ 2020-05-17 14:05 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance; hippysoyboy@gmail.com; postgres@binarykeep.com
Em dom., 17 de mai. de 2020 às 10:31, Justin Pryzby <pryzby@telsasoft.com>
escreveu:
> On Sun, May 17, 2020 at 09:32:47AM -0300, Ranier Vilela wrote:
> > I redid the same tests with vanila postgres and with empty tables.
> > I'm surprised, why does the plan have 2550 rows in explain?
>
> That's the *estimated* rowcount.
>
> The planner tends to ignore table statistics which say the table is empty,
> since that can lead to a terrible plan if it's not true (stats are out of
> date
> or autovacuum threshold not hit).
>
Thanks for the explanation.
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-05-17 14:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-14 05:44 Plan not skipping unnecessary inner join Matthew Nelson <postgres@binarykeep.com>
2020-05-14 06:45 ` David Wheeler <hippysoyboy@gmail.com>
2020-05-15 17:36 ` Matthew Nelson <postgres@binarykeep.com>
2020-05-17 12:32 Re: Plan not skipping unnecessary inner join Ranier Vilela <ranier.vf@gmail.com>
2020-05-17 13:31 ` Justin Pryzby <pryzby@telsasoft.com>
2020-05-17 14:05 ` Ranier Vilela <ranier.vf@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox