Date: Tue, 03 May 2022 13:05:30 +0200
Subject: Useless memoize path generated for unique join on primary keys
From: Benjamin Coutu <ben.coutu@zeyos.com>
To: pgsql-performance@postgresql.org
Cc: David Rowley <dgrowleyml@gmail.com>
Message-ID: <34e2bc3e1eaa52429a11@zeyos.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/34e2bc3e1eaa52429a11%40zeyos.com>
Precedence: bulk

Hello,=0A=0AI have come across a plan that should never get generated IMHO:=
=0A=0ASELECT 1=0AFROM extdataregular e1=0AINNER JOIN extdataempty e2 ON e1.=
field =3D e2.field AND e1.index =3D e2.index=0A=0Agenerates the following p=
lan:=0A=0ANested Loop  (cost=3D1.13..528540.89 rows=3D607604 width=3D4) (ac=
tual time=3D9298.504..9298.506 rows=3D0 loops=3D1)=0A  ->  Index Only Scan =
using pk_extdataempty on extdataempty e2  (cost=3D0.56..157969.52 rows=3D40=
78988 width=3D16) (actual time=3D0.026..641.248 rows=3D4067215 loops=3D1)=
=0A        Heap Fetches: 268828=0A  ->  Memoize  (cost=3D0.58..0.67 rows=3D=
1 width=3D16) (actual time=3D0.002..0.002 rows=3D0 loops=3D4067215)=0A     =
   Cache Key: e2.field, e2.index=0A        Cache Mode: logical=0A        Hi=
ts: 0  Misses: 4067215  Evictions: 3228355  Overflows: 0  Memory Usage: 655=
37kB=0A        Buffers: shared hit=3D16268863=0A        ->  Index Only Scan=
 using pk_extdataregular on extdataregular e1  (cost=3D0.57..0.66 rows=3D1 =
width=3D16) (actual time=3D0.001..0.001 rows=3D0 loops=3D4067215)=0A       =
       Index Cond: ((field =3D e2.field) AND (index =3D e2.index))=0A      =
        Heap Fetches: 2=0A=0APlease note that the memoize node has no cache=
 hits, which is not surprising given that we are joining on two primary key=
s that are unique by definition ("field" and "index" make up the primary ke=
y of both tables).=0AWhy would it ever make sense to generate a memoize pla=
n for a unique join?=0A=0AI think this issue might tie in with the current =
discussion over on the hackers mailing list [1]=0A=0ACheers, Ben=0A=0A[1] h=
ttps://www.postgresql.org/message-id/flat/CAApHDvpFsSJAThNLtqaWvA7axQd-VOFc=
t%3DFYQN5muJV-sYtXjw%40mail.gmail.com=0A=0A-- =0A=0ABejamin Coutu=0Aben.cou=
tu@zeyos.com=0A=0AZeyOS GmbH & Co. KG=0Ahttp://www.zeyos.com