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.248rows=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.001rows=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
keysthat 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.coutu@zeyos.com
ZeyOS GmbH & Co. KG
http://www.zeyos.com
