Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vEBiw-006tE9-9h for pgsql-general@arkaria.postgresql.org; Wed, 29 Oct 2025 19:20:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vEBiv-003KIf-9I for pgsql-general@arkaria.postgresql.org; Wed, 29 Oct 2025 19:20:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vEBiu-003KIV-Sr for pgsql-general@lists.postgresql.org; Wed, 29 Oct 2025 19:20:43 +0000 Received: from mail-ot1-x32b.google.com ([2607:f8b0:4864:20::32b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vEBio-004w5z-0c for pgsql-general@lists.postgresql.org; Wed, 29 Oct 2025 19:20:43 +0000 Received: by mail-ot1-x32b.google.com with SMTP id 46e09a7af769-7c29466eabaso60443a34.0 for ; Wed, 29 Oct 2025 12:20:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761765635; x=1762370435; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=uaUnlQCk96SJSO6K0psmeSWjlPe75YgRDa5dkeCBpJU=; b=Hvm14XPTkz3E+Y/WzSkFRGwPCexAtVe6bLQ9p9wg/7YYXWrfY+oIon4NmTq3m6d1Nw bS8pUMPaMZy+5agPDLHrmbKcUbGJvsjOGYrDCbMelYhkKHJr8lhjOATsItBYcY1kyZ27 V90lM1n73C9HO3Tt85K6aicMXAGZoothsIzzCeZkKTwDEye5NJSLyEz+zwVoaHtQ8P7/ FaEc3DUv1iEZSbruq3r5sDx8NujBA+01e2H4afB70Nx2sE6CTq1fs7uToLNFdEsDXDyR Ea3a7r58tiywVz2/N9X5LOR4I9Q8I9G8eZ7AFg1V0I74ga2uLuvLoRsRFa7xMEZQpAiB 0Nyg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761765635; x=1762370435; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=uaUnlQCk96SJSO6K0psmeSWjlPe75YgRDa5dkeCBpJU=; b=dMkXsZ4NHgVyh/HP2LnNdp2mEkemG2mZPTwTRYK4Ak8wKLi/gzBxUcW/YTTuascbUd +qb06DwPyyhpFqpw/bHDIPJYWZuExlcIAJCM6YPbK4X37YS7lbSCzi/jf49ESmNpalYv KzsdjyHXHQjM77Iyoospc0aF8u7Sbi6PTT0zLAb/yy1dX2+0quBD6prY19N/v0YRS83+ u2u+y4/Xw9traXrLba42h6pwgVCQfjV5fjZq13qYrIOW9kggZt+rbNXNOQV/Y1baQHX0 VwIvM0JN7gOdzFWlGS3w1twtoyubgjkh6zD/2mDxason7X4aVFKIFQHrNrV4YXSvSNyf inDQ== X-Gm-Message-State: AOJu0YxpNurbcWp2nXa8O6nfsTIXLFE7bOL/nfVfad4ng+TugHE89eOy X8V3WrWtAp07xntqTn9FPJZNXEepAQ+vUabJQNlMMMbjOPmB/SJMl8gl3PN2sq1sq5ZSwviZoZR vB1vG1FHIv5c1g8BuBKmUOr7HuB8QBvHT/rdD X-Gm-Gg: ASbGncvn718/jzdR9L28xBtyODjWvYj63086bj56OtGZs/8iXy3t6NS2nlOb6ImifjA ZLydN9VSpI6b51t1fBH4b8moAjlfqeOeqM04r5ZHler1qGyxCNpnF5Igza22vhOyxQhgLXYwLp7 fzN1ak0g85Giuj4CAqPwclgS/iwgPttWKqWegNJi0uBaBHMSoIHm3sEhnzKVLccE+ODSaad3f7h toaJhjPOGwHJL3XLvJLqKgyUnvNj0IkoMw4WcsHzVrleUPnY2Nhp398/ukVYLpTO7tThME4nw== X-Google-Smtp-Source: AGHT+IFYtVmiGwqz8Y6/dVuVT/00Z8wxpH3B81YpsZq+rhk78q7c9rWJRJuOdsLGHtKNjZC3hlU44MxrCrY/Wn4oPcw= X-Received: by 2002:a05:6808:4fcc:b0:44f:6ab9:4b14 with SMTP id 5614622812f47-44f7a5781b0mr2153196b6e.62.1761765635496; Wed, 29 Oct 2025 12:20:35 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Wed, 29 Oct 2025 15:20:23 -0400 X-Gm-Features: AWmQ_bnsGbeqSMXaJgNL1Ox0IgtKt8xu3M0wezDW0YJaEKSa8gJ5lhF7n6Q_Ab8 Message-ID: Subject: Re: Postgres memoizing inner loop of join when outer loop join key is guaranteed unique? To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000e19e4b06425106e3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e19e4b06425106e3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable What's the actual problem? Does enable_memoize=3Don return incorrect resul= ts? Because a 45 microsecond (yes, 45 microseconds: 0.138 milliseconds =3D 138 microseconds; same for the others) slowdown isn't something I'd get too worked up about. On Wed, Oct 29, 2025 at 2:29=E2=80=AFPM Jacob Jackson wrote: > Hello. I was looking at some query plans recently and noticed something > that didn't make sense. I have a query that joins a table of questions wi= th > results for each question (using a table with a composite primary key of > question id and a user id), filtered by user id. The question IDs and the > combined question-userIds are guaranteed unique due to being primary keys= , > and yet Postgres still memoizes the inner loop results. Any ideas why? Is > this just a failure of the query planner (I would be happy to explore > creating a PR), did I not properly guarantee uniqueness, or is there > another reason for memoization? The memoized version is consistently > slightly slower in my testing, despite the calculated cost being lower. > Here are the query plans: > > enable_memoize=3Don: > > EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM questions LEFT JOIN > "QuestionUserStatus" ON questions.id =3D "QuestionUserStatus".question > WHERE "QuestionUserStatus".user =3D 0; > > Nested Loop (cost=3D0.71..514.09 rows=3D277 width=3D1381) (actual > time=3D0.021..0.520 rows=3D231 loops=3D1) > =E2=94=9C Buffers: shared hit=3D859 > =E2=94=9C Index Scan using "QuestionUserStatus_user_question_pk" on > "QuestionUserStatus" (cost=3D0.42..178.88 rows=3D277 width=3D18) (actual > time=3D0.014..0.114 rows=3D231 loops=3D1) > =E2=94=82 =E2=94=9C Index Cond: ("user" =3D '0'::bigint) > =E2=94=82 =E2=94=94 Buffers: shared hit=3D166 > =E2=94=94 Memoize (cost=3D0.29..1.25 rows=3D1 width=3D1363) (actual time= =3D0.001..0.001 > rows=3D1 loops=3D231) > =E2=94=9C Cache Key: "QuestionUserStatus".question > =E2=94=9C Cache Mode: logical > =E2=94=9C Hits: 0 Misses: 231 Evictions: 0 Overflows: 0 Memory Usag= e: 320kB > =E2=94=9C Buffers: shared hit=3D693 > =E2=94=94 Index Scan using questions_pkey on questions (cost=3D0.28..1= .24 rows=3D1 > width=3D1363) (actual time=3D0.001..0.001 rows=3D1 loops=3D231) > =E2=94=9C Index Cond: (id =3D "QuestionUserStatus".question) > =E2=94=94 Buffers: shared hit=3D693 > Planning: > =E2=94=94 Buffers: shared hit=3D6 > Planning Time: 0.183 ms > Execution Time: 0.548 ms > > enable_memoize=3Doff: > > Nested Loop (cost=3D0.70..521.98 rows=3D277 width=3D1381) (actual > time=3D0.018..0.421 rows=3D231 loops=3D1) > =E2=94=9C Buffers: shared hit=3D859 > =E2=94=9C Index Scan using "QuestionUserStatus_user_question_pk" on > "QuestionUserStatus" (cost=3D0.42..178.88 rows=3D277 width=3D18) (actual > time=3D0.014..0.099 rows=3D231 loops=3D1) > =E2=94=82 =E2=94=9C Index Cond: ("user" =3D '3477145805513'::bigint) > =E2=94=82 =E2=94=94 Buffers: shared hit=3D166 > =E2=94=94 Index Scan using questions_pkey on questions (cost=3D0.28..1.2= 4 rows=3D1 > width=3D1363) (actual time=3D0.001..0.001 rows=3D1 loops=3D231) > =E2=94=9C Index Cond: (id =3D "QuestionUserStatus".question) > =E2=94=94 Buffers: shared hit=3D693 > Planning: > =E2=94=94 Buffers: shared hit=3D6 > Planning Time: 0.197 ms > Execution Time: 0.444 ms > > Thanks for the help, > Jacob > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000e19e4b06425106e3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
What's the actual problem?=C2=A0 Does=C2=A0enable_memoize=3Don=C2=A0return inco= rrect results?

Because a 45 microsecond (yes, 45 m= icroseconds: 0.138 milliseconds =3D 138 microseconds; same for the others) = slowdown isn't something I'd get too worked up about.
On Wed, Oct 29, 2025 at 2:29=E2=80=AFPM Jacob Jacks= on <jej.jackson.08@gmail.com= > wrote:
Hello. I was = looking at some query=C2=A0plans recently and noticed something that didn&#= 39;t make sense. I have a query that joins a table of questions with result= s for each question (using a table with a composite primary key of question= id and a user id), filtered by user id. The question IDs and the combined = question-userIds are guaranteed unique due to being primary keys, and yet P= ostgres still memoizes the inner loop results. Any ideas why? Is this just = a failure of the query planner (I would be happy to explore creating a PR),= did I not properly=C2=A0guarantee uniqueness, or is there another reason f= or memoization? The memoized=C2=A0version is consistently slightly slower i= n my testing, despite the calculated=C2=A0cost being lower. Here are the qu= ery plans:

enable_memoize=3Don:=

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM questions LEFT JOIN= "QuestionUserStatus" ON questions.id =3D "QuestionUserStatus".question WH= ERE "QuestionUserStatus".user =3D 0;

Nested Loop = =C2=A0(cost=3D0.71..514.09 rows=3D277 width=3D1381) (actual time=3D0.021..0= .520 rows=3D231 loops=3D1)
=E2=94=9C Buffers: shared hit=3D859
=E2=94= =9C Index Scan using "QuestionUserStatus_user_question_pk" on &qu= ot;QuestionUserStatus" =C2=A0(cost=3D0.42..178.88 rows=3D277 width=3D1= 8) (actual time=3D0.014..0.114 rows=3D231 loops=3D1)
=E2=94=82 =E2=94=9C= Index Cond: ("user" =3D '0'::bigint)
=E2=94=82 =E2=94= =94 Buffers: shared hit=3D166
=E2=94=94 Memoize =C2=A0(cost=3D0.29..1.25= rows=3D1 width=3D1363) (actual time=3D0.001..0.001 rows=3D1 loops=3D231)=C2=A0 =E2=94=9C Cache Key: "QuestionUserStatus".question
= =C2=A0 =E2=94=9C Cache Mode: logical
=C2=A0 =E2=94=9C Hits: 0 =C2=A0Miss= es: 231 =C2=A0Evictions: 0 =C2=A0Overflows: 0 =C2=A0Memory Usage: 320kB
= =C2=A0 =E2=94=9C Buffers: shared hit=3D693
=C2=A0 =E2=94=94 Index Scan u= sing questions_pkey on questions =C2=A0(cost=3D0.28..1.24 rows=3D1 width=3D= 1363) (actual time=3D0.001..0.001 rows=3D1 loops=3D231)
=C2=A0 =C2=A0 = =E2=94=9C Index Cond: (id =3D "QuestionUserStatus".question)
= =C2=A0 =C2=A0 =E2=94=94 Buffers: shared hit=3D693
Planning:
=E2=94=94= Buffers: shared hit=3D6
Planning Time: 0.183 ms
Execution Time: 0.54= 8 ms

enable_memoize=3Doff:

Nested Loop =C2=A0(c= ost=3D0.70..521.98 rows=3D277 width=3D1381) (actual time=3D0.018..0.421 row= s=3D231 loops=3D1)
=E2=94=9C Buffers: shared hit=3D859
=E2=94=9C Inde= x Scan using "QuestionUserStatus_user_question_pk" on "Quest= ionUserStatus" =C2=A0(cost=3D0.42..178.88 rows=3D277 width=3D18) (actu= al time=3D0.014..0.099 rows=3D231 loops=3D1)
=E2=94=82 =E2=94=9C Index C= ond: ("user" =3D '3477145805513'::bigint)
=E2=94=82 = =E2=94=94 Buffers: shared hit=3D166
=E2=94=94 Index Scan using questions= _pkey on questions =C2=A0(cost=3D0.28..1.24 rows=3D1 width=3D1363) (actual = time=3D0.001..0.001 rows=3D1 loops=3D231)
=C2=A0 =E2=94=9C Index Cond: (= id =3D "QuestionUserStatus".question)
=C2=A0 =E2=94=94 Buffers= : shared hit=3D693
Planning:
=E2=94=94 Buffers: shared hit=3D6
Pla= nning Time: 0.197 ms
Execution Time: 0.444 ms

Than= ks for the help,
Jacob


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--000000000000e19e4b06425106e3--