From: Benjamin Coutu <ben.coutu@zeyos.com>
To: pgsql-performance@postgresql.org
Cc: David Rowley <dgrowleyml@gmail.com>
Subject: Useless memoize path generated for unique join on primary keys
Date: Tue, 03 May 2022 13:05:30 +0200
Message-ID: <34e2bc3e1eaa52429a11@zeyos.com> (raw)
Hello,
I have come across a plan that should never get generated IMHO:
SELECT 1
FROM extdataregular e1
INNER JOIN extdataempty e2 ON e1.field = e2.field AND e1.index = e2.index
generates the following plan:
Nested Loop (cost=1.13..528540.89 rows=607604 width=4) (actual time=9298.504..9298.506 rows=0 loops=1)
-> Index Only Scan using pk_extdataempty on extdataempty e2 (cost=0.56..157969.52 rows=4078988 width=16) (actual time=0.026..641.248 rows=4067215 loops=1)
Heap Fetches: 268828
-> Memoize (cost=0.58..0.67 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4067215)
Cache Key: e2.field, e2.index
Cache Mode: logical
Hits: 0 Misses: 4067215 Evictions: 3228355 Overflows: 0 Memory Usage: 65537kB
Buffers: shared hit=16268863
-> Index Only Scan using pk_extdataregular on extdataregular e1 (cost=0.57..0.66 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4067215)
Index Cond: ((field = e2.field) AND (index = e2.index))
Heap Fetches: 2
Please note that the memoize node has no cache hits, which is not surprising given that we are joining on two primary keys that are unique by definition ("field" and "index" make up the primary key of both tables).
Why would it ever make sense to generate a memoize plan for a unique join?
I think this issue might tie in with the current discussion over on the hackers mailing list [1]
Cheers, Ben
[1] https://www.postgresql.org/message-id/flat/CAApHDvpFsSJAThNLtqaWvA7axQd-VOFct%3DFYQN5muJV-sYtXjw%40m...
--
Bejamin Coutu
ben.coutu@zeyos.com
ZeyOS GmbH & Co. KG
http://www.zeyos.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ben.coutu@zeyos.com, dgrowleyml@gmail.com
Subject: Re: Useless memoize path generated for unique join on primary keys
In-Reply-To: <34e2bc3e1eaa52429a11@zeyos.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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