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 1vEDoK-007m8T-SU for pgsql-general@arkaria.postgresql.org; Wed, 29 Oct 2025 21:34:28 +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 1vEDoJ-003quL-Ny for pgsql-general@arkaria.postgresql.org; Wed, 29 Oct 2025 21:34:26 +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 1vEDoJ-003quD-Bw for pgsql-general@lists.postgresql.org; Wed, 29 Oct 2025 21:34:26 +0000 Received: from mail-ed1-x534.google.com ([2a00:1450:4864:20::534]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vEDoC-004xBE-1v for pgsql-general@lists.postgresql.org; Wed, 29 Oct 2025 21:34:26 +0000 Received: by mail-ed1-x534.google.com with SMTP id 4fb4d7f45d1cf-63c4b5a1b70so591245a12.1 for ; Wed, 29 Oct 2025 14:34:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761773660; x=1762378460; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=In31K6sfTvuqNxkzr51piqIOaxQnCEaTh42Ez0jQzss=; b=VzCnT45q9jwclrUjNQS5fDU+qfnpU2VaMA8KDclyxdtsREYaVLoDz4hTKwAhVkTS9W 6ZVP6XGB/ES4Vn5MAJQNfcEgM9zSLXfdlNwENTeGDhpLfw/idrDT2S5xNrvwBlp1h8Gt jOZ9PwvWpisRxwprzVf7iWzpRVyR42Y/Rl39NwqD4aUf2CuPeWPgVS6QyKEbHo6O0SlJ xrIqHRfOkjw+afd/2YeatHB7o7fsdBH5wQV86SnTV75uPaIO9ebQ5PmnvhjqG87g1vfn NN+CbZT9EI6DIyfTY9AX2bKVm822CuyQi72l+58SPlYxTsaNwSnbg0l2rzsnnvuqePyW WmSg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761773660; x=1762378460; h=cc: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=In31K6sfTvuqNxkzr51piqIOaxQnCEaTh42Ez0jQzss=; b=viLZwMUcWlSOdZiDF0ECAx5jBaovTdYjCBGO6IQUagWx2zTRG1Q9+FUlbaW3vILCom iiYU9K0718aV0VI0Au/5TY0FKvJn7S8pF446Ghdc6AqdhyYApWftYfgt7JwEHCiThHKI 1G12z5iXyRS8S5HgC7VOiWDeyCkyq8GQYyOgZDlfWZ4Qdi7O32XQ+HeDK53Q2A+2Ry1c MPolesfWmug1HOJHtctl+aNlRBrhXLr1YYBrEMoTk3jsrO2fig1MvnII2shI9uKTZ9L1 jB8nKoFym1Ct8emdp8ufIRFkxiskY6IdfwmRUZZMiV2wB6XcFKoMK1zBAtScpx452p0/ mpPw== X-Gm-Message-State: AOJu0YzHCNdpX3v8C1uWds4ZsbtesDMtlD6WS9pZidwclLobbgXb94ph pV+05KvJL1H32Gh4DWNvM9jYOidWBtB8fA0jKztq1EVDtooG+4KMfZnNosBOA4AK7wfLiWSTLmW pQBUuzvPe/7+1S/Wt7LE5QObMYpYkAm4= X-Gm-Gg: ASbGnctsr0f6ZYEA64dxti3oC7XgXRZcC/cK0mksbGZYR0SdrryUxAFk9yfKbMpLFiO xr4V/KtFSfjrdpv0FT2Nwh95snzh6Q98Ot7W3qX80YZsU+zkePZbVzLmePpNZcqlWHoH64HKM/0 LTe7XYa5YkPo074VmTjqe3cMIsP2Ot+6ymJhOlyaLn/+b7DPn9dG3Nu1wY6qH51aH7A0g8nG4hJ W29srFnV4rM0oEguXXfRcOrvr8HqJRwERVcKYky1QMr4ndtiMq9ZMKLzHf+Zw== X-Google-Smtp-Source: AGHT+IHc9WO/HlQfqz+yeXbJ7xUWVF57jhbZxcOVkZNKSleGnIgqfjWJuuK1CeX46HoBkrGZ+VsC+2tpgZu7JptHZvY= X-Received: by 2002:a05:6402:42cf:b0:63c:5d27:7ed7 with SMTP id 4fb4d7f45d1cf-640443afec1mr3103118a12.30.1761773659516; Wed, 29 Oct 2025 14:34:19 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Jacob Jackson Date: Wed, 29 Oct 2025 17:34:08 -0400 X-Gm-Features: AWmQ_bm_4lkMFo7Kf5N4aelH7EJex725yNt5ap4MRmsyG4RQ_jQ27z813m0BGMo Message-ID: Subject: Re: Postgres memoizing inner loop of join when outer loop join key is guaranteed unique? To: Ron Johnson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000026732d064252e566" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000026732d064252e566 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I was curious to see whether there was any reason I wasn't seeing for Postgres to decide the memoized version was lower cost and try to memoize these operations. On Wed, Oct 29, 2025 at 3:20=E2=80=AFPM Ron Johnson wrote: > What's the actual problem? Does enable_memoize=3Don return incorrect > results? > > Because a 45 microsecond (yes, 45 microseconds: 0.138 milliseconds =3D 13= 8 > 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 w= ith >> 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 th= e >> combined question-userIds are guaranteed unique due to being primary key= s, >> and yet Postgres still memoizes the inner loop results. Any ideas why? I= s >> 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) (actua= l >> 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 tim= e=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 Usa= ge: 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) (actua= l >> 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.= 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.197 ms >> Execution Time: 0.444 ms >> >> Thanks for the help, >> Jacob >> > > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --00000000000026732d064252e566 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I was curious to see whether there was any reason I wasn&#= 39;t seeing for Postgres to decide the memoized version was lower cost and = try to memoize these operations.

On Wed, Oct 29, 2025 = at 3:20=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
What's the actual probl= em?=C2=A0 Does=C2=A0enable_memoize=3D= on=C2=A0return incorrect results?

Because a= 45 microsecond (yes, 45 microseconds: 0.138 milliseconds =3D 138 microseco= nds; same for the others) slowdown isn't something I'd get too work= ed up about.

On Wed, Oct 29, 2025 at 2= :29=E2=80=AFPM Jacob Jackson <jej.jackson.08@gmail.com> wrote:
Hello. I was looking at some query=C2=A0plans recently and noticed= something that didn't make sense. I have a query that joins a table of= questions with results for each question (using a table with a composite p= rimary 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 p= rimary keys, and yet Postgres still memoizes the inner loop results. Any id= eas why? Is this just a failure of the query planner (I would be happy to e= xplore creating a PR), did I not properly=C2=A0guarantee uniqueness, or is = there another reason for memoization? The memoized=C2=A0version is consiste= ntly slightly slower in my testing, despite the calculated=C2=A0cost being = lower. Here are the query plans:

enable_memoize=3Don:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FR= OM questions LEFT JOIN "QuestionUserStatus" ON questions.id =3D "QuestionUserStat= us".question WHERE "QuestionUserStatus".user =3D 0;

Nested Loop =C2=A0(cost=3D0.71..514.09 rows=3D277 width=3D1381) (actu= al time=3D0.021..0.520 rows=3D231 loops=3D1)
=E2=94=9C Buffers: shared h= it=3D859
=E2=94=9C Index Scan using "QuestionUserStatus_user_questi= on_pk" on "QuestionUserStatus" =C2=A0(cost=3D0.42..178.88 ro= ws=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)<= br>=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 ro= ws=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=A0Misses: 231 =C2=A0Evictions: 0 =C2=A0Overflows: 0 =C2=A0Memo= ry Usage: 320kB
=C2=A0 =E2=94=9C Buffers: shared hit=3D693
=C2=A0 =E2= =94=94 Index Scan using questions_pkey on questions =C2=A0(cost=3D0.28..1.2= 4 rows=3D1 width=3D1363) (actual time=3D0.001..0.001 rows=3D1 loops=3D231)<= br>=C2=A0 =C2=A0 =E2=94=9C Index Cond: (id =3D "QuestionUserStatus&quo= t;.question)
=C2=A0 =C2=A0 =E2=94=94 Buffers: shared hit=3D693
Planni= ng:
=E2=94=94 Buffers: shared hit=3D6
Planning Time: 0.183 ms
Exec= ution Time: 0.548 ms

<= /div>
enable_memoize=3Doff:
<= font face=3D"monospace">
Nest= ed Loop =C2=A0(cost=3D0.70..521.98 rows=3D277 width=3D1381) (actual time=3D= 0.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" =C2=A0(cost=3D0.42..178.88 rows=3D277 w= idth=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 =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: shar= ed hit=3D6
Planning Time: 0.197 ms
Execution Time: 0.444 ms

Thanks for the help,
Jacob


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