Useless memoize path generated for unique join on primary keys

From: Benjamin Coutu <ben(dot)coutu(at)zeyos(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: David Rowley <dgrowleyml(at)gmail(dot)com>
Subject: Useless memoize path generated for unique join on primary keys
Date: 2022-05-03 11:05:30
Message-ID: 34e2bc3e1eaa52429a11@zeyos.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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%40mail.gmail.com

--

Bejamin Coutu
ben(dot)coutu(at)zeyos(dot)com

ZeyOS GmbH & Co. KG
http://www.zeyos.com

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message David Rowley 2022-05-03 11:43:09 Re: Useless memoize path generated for unique join on primary keys
Previous Message aditya desai 2022-04-29 11:23:30 Fwd: FATAL: canceling authentication due to timeout