pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimising outer joins in the presence of non-nullable references
2+ messages / 2 participants
[nested] [flat]
* Optimising outer joins in the presence of non-nullable references
@ 2021-05-24 12:10 Philip Lykke Carlsen <philip@hasura.io>
2021-05-24 14:11 ` Re: Optimising outer joins in the presence of non-nullable references Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Philip Lykke Carlsen @ 2021-05-24 12:10 UTC (permalink / raw)
To: pgsql-performance
Hi list.
I have a question about the different plans produced by postgres for
an outer join versus an inner join.
(complete sql script attached)
Take these two tables:
CREATE TABLE album
( id SERIAL PRIMARY KEY,
title TEXT NOT NULL
);
CREATE INDEX album_title ON album (title);
CREATE TABLE track
( id SERIAL PRIMARY KEY,
album_id INT NOT NULL REFERENCES album(id),
title TEXT NOT NULL
);
CREATE INDEX track_album ON track(album_id);
CREATE INDEX track_title ON track(title);
where crucially `track` references `album(id)` with a `NOT NULL` reference.
Now, if we query both an inner and an outer join on `track` and
`album` (after having suitably filled-in data), we get very different
plans where only the inner join exploits indices.
That is:
EXPLAIN ANALYZE
SELECT t.id
FROM track t
INNER JOIN album a ON (t.album_id = a.id)
ORDER BY a.title ASC
LIMIT 10;
Produces this query plan:
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..3.29 rows=10 width=36) (actual time=0.038..0.052
rows=10 loops=1)
-> Nested Loop (cost=0.56..3606.93 rows=13200 width=36) (actual
time=0.036..0.046 rows=10 loops=1)
-> Index Scan using album_title on album a
(cost=0.28..113.23 rows=1397 width=36) (actual time=0.015..0.016
rows=1 loops=1)
-> Index Scan using track_album on track t (cost=0.29..1.84
rows=66 width=8) (actual time=0.012..0.018 rows=10 loops=1)
Index Cond: (album_id = a.id)
Planning Time: 0.473 ms
Execution Time: 0.096 ms
(7 rows)
While this:
EXPLAIN ANALYZE
SELECT t.id
FROM track t
LEFT JOIN album a ON (t.album_id = a.id)
ORDER BY a.title ASC
LIMIT 10;
Produces this query plan:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Limit (cost=604.43..604.45 rows=10 width=36) (actual
time=20.934..20.943 rows=10 loops=1)
-> Sort (cost=604.43..637.43 rows=13200 width=36) (actual
time=20.932..20.934 rows=10 loops=1)
Sort Key: a.title
Sort Method: top-N heapsort Memory: 27kB
-> Hash Left Join (cost=42.43..319.18 rows=13200 width=36)
(actual time=1.082..12.333 rows=10000 loops=1)
Hash Cond: (t.album_id = a.id)
-> Seq Scan on track t (cost=0.00..242.00 rows=13200
width=8) (actual time=0.031..3.919 rows=10000 loops=1)
-> Hash (cost=24.97..24.97 rows=1397 width=36)
(actual time=0.990..0.991 rows=1000 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 101kB
-> Seq Scan on album a (cost=0.00..24.97
rows=1397 width=36) (actual time=0.014..0.451 rows=1000 loops=1)
Planning Time: 0.251 ms
Execution Time: 20.999 ms
(12 rows)
My question then is, shouldn't the inner and outer join queries be
semantically equivalent when the columns we are joining on are
non-nullable foreign keys?
Is there some corner case I'm not considering?
Would it be a good addition to postgres if it could detect this and
produce a plan that exploits the indices?
(My root motivation for asking this question is this github issue:
https://github.com/hasura/graphql-engine/issues/5949)
Regards,
Philip
Attachments:
[application/sql] outer-join-indexes.sql (1.6K, ../../CAN-AqA4MTY34V9nt66YrHE75ZaCpFanqcoTyL3ESbEQMX_Gz-Q@mail.gmail.com/2-outer-join-indexes.sql)
download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Optimising outer joins in the presence of non-nullable references
2021-05-24 12:10 Optimising outer joins in the presence of non-nullable references Philip Lykke Carlsen <philip@hasura.io>
@ 2021-05-24 14:11 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2021-05-24 14:11 UTC (permalink / raw)
To: Philip Lykke Carlsen <philip@hasura.io>; +Cc: pgsql-performance
Philip Lykke Carlsen <philip@hasura.io> writes:
> My question then is, shouldn't the inner and outer join queries be
> semantically equivalent when the columns we are joining on are
> non-nullable foreign keys?
Maybe, but no such knowledge is built into the planner.
> Is there some corner case I'm not considering?
I'm a little suspicious whether it's actually a safe assumption to
make, in view of the fact that enforcement of FKs is delayed till
end-of-statement or even end-of-transaction. Thus, the relationship
isn't necessarily valid at every instant.
> Would it be a good addition to postgres if it could detect this and
> produce a plan that exploits the indices?
Maybe. Aside from semantic correctness issues, the big question
would be whether the detection could be made cheap enough to not
be a drag on the 99.99% of cases where it's not helpful.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-05-24 14:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-24 12:10 Optimising outer joins in the presence of non-nullable references Philip Lykke Carlsen <philip@hasura.io>
2021-05-24 14:11 ` Tom Lane <tgl@sss.pgh.pa.us>
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