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 1v6mO0-009bGL-Cu for pgsql-performance@arkaria.postgresql.org; Thu, 09 Oct 2025 08:52:32 +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 1v6mNy-002Hms-3x for pgsql-performance@arkaria.postgresql.org; Thu, 09 Oct 2025 08:52:31 +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 1v6mNx-002HmZ-Lr for pgsql-performance@lists.postgresql.org; Thu, 09 Oct 2025 08:52:30 +0000 Received: from mail-lf1-x12b.google.com ([2a00:1450:4864:20::12b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v6mNv-001CLy-2w for pgsql-performance@postgresql.org; Thu, 09 Oct 2025 08:52:29 +0000 Received: by mail-lf1-x12b.google.com with SMTP id 2adb3069b0e04-57db15eeb11so860042e87.2 for ; Thu, 09 Oct 2025 01:52:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=etc-to.20230601.gappssmtp.com; s=20230601; t=1759999945; x=1760604745; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=z/fUpfT52jEar4nzxjSI+7oeD18oiP0BIXBVCCVHTuU=; b=fue2KA+6uRI0bOJbbyfingEox4FhvkhHt4nF/lehjAiM0C6n4yR0/0vWiJfyzJjiPN aya6DvhYT53MxjAQzHfjvzmZLwGjCncM5moHznj1uGMdYqkGLAtP+oL6utxAo6PLk2EB SlO4OJFC3vo/qVjyE9ib20eKM+OMDvPvpl7xNgjdBhK1ahHsyAs8WLEPImzC6yfRKkJy JQKzePHtl50jWnbUkQd/RXbFW6/sBaofR9GQ/D7qtlVXM55QRfl+0+ZrEC5AdLQVjSbg JLxdrcmMxTM1kIW7J658orp/ePtJjTP4ZynYqpuaAdeB1Rb0fjEqQar70auBGWo8X8Lt /bVA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1759999945; x=1760604745; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=z/fUpfT52jEar4nzxjSI+7oeD18oiP0BIXBVCCVHTuU=; b=IwhDUkxmLEZ6l8Wtn/i42/msCpnePy5gvSCq2AjqCbOT1tRPxjRFpKKcqodkR6PRjE FaRDZNI3J5Bwq5NSbWaOV77HpmTvgHJ3DHfkv7SYU8fovCKCblbClMp33JpBd2HFbw12 1kTB7XbX25lsd/mClWxIORuY9GiCZ5DviTj+fELkdKPbCcjrWgmmlJhiNMr2An+B/MRE kqKXBB4VX4QYM6x/YOQA4OWM/AdbhPfSckyAcl+2PvUc/SEVQhRYI1IWT4kjmQAeIr6k GvdhF2B9Y37veX5OtcajFk/jBruneKIuvrS+UPDjgNvEZxvPmU9NZxiSf+aSlJAuJunP sQ+Q== X-Gm-Message-State: AOJu0YyY3FTFCLF5K/cTWFWGffeLcBmO/3x+wHisQwEm0VioBGcC1d82 zLW3p7vTZ4NNHjnBpKGYRC89Dtp5qbvzt7Ou3kc7NG3+Ans4TORSbqiJJwNEt0Lt3dKjZWcfCgZ tS6jKuolbhroaTLzjjtQ+BpgUmYS/X0yskyoFImfJ+q/y+if6nhN8bCod8YOo X-Gm-Gg: ASbGnctV9UJslpFSfQtopxQT1wYgKL7pP1v8u5jx0RdelW1TCPr9wS/n56EXgOGJsZ7 dt53qrZLKcR7k8MhF51x41p8p77Au4TBlsSpQvCn4a55j/UgFohfFXAfHTRYGdep/bEuWWcUQy0 bSjswufg+DRCQLvqS7ge5MXfwni9Qq1gskFqVfL4ZbWYz4f1TsxU3yNL+zG4hePyUb9oIZSFPkM CnrAskfs5SIn+MT/p0zgejHJXWJKSKQEu/vLidj X-Google-Smtp-Source: AGHT+IHMp0s3OnOfkysQ9mOT+H6gDiA6OXhZP0M/D6vCZtaMbzuG3N7ERPATU3vEoItyeoLavCwMwxgW8w01NPm0iz8= X-Received: by 2002:ac2:4c48:0:b0:58b:75:8fae with SMTP id 2adb3069b0e04-5906d8ef5a4mr1736259e87.26.1759999945180; Thu, 09 Oct 2025 01:52:25 -0700 (PDT) MIME-Version: 1.0 From: Frits Jalvingh Date: Thu, 9 Oct 2025 10:52:12 +0200 X-Gm-Features: AS18NWDdDNFz1kUehCz-YlrsvtcpIUK3gS5sgF06Ziqe2c0epxVfhl6R0ly4rUY Message-ID: Subject: Question about nested loops.. To: "pgsql-performa." Content-Type: multipart/alternative; boundary="000000000000899c390640b5ebbe" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000899c390640b5ebbe Content-Type: text/plain; charset="UTF-8" Dear list, We have databases with rather large tables, and we do bulk queries on them, i.e. those queries always have a large result set. We execute those queries with enable_nestloop set to false, because having nested loops in those queries always cause large performance issues. This usually leads to perfectly acceptable execution plans which use table scans, hash joins and merge joins. We have work_mem set to 64MB to 512MB (depending on the actual database size) to help with these. I have a query that does not want to work at all without a nested loop and a materialize, while a trivial (but for me strange) change will allow it to make a good plan, and I'm wondering why that is. The below examples are stripped versions of the real queries (which have a lot more filter conditions) but they exhibit the issue. I tested them on both Postgres 16 and 18, on Ubuntu 25.04, with the same results. The slow query is this one: select eenheid.id_s as id_s , eenheid.identificatie as id , exists( select 1 from datavault.l_cluster_eenheid_ssm inner join datavault.s_h_huurovereenkomst_ssm huurovereenkomst_s on eenheid.id_h_eenheid = huurovereenkomst_s._l_eenheid where eenheid.id_h_eenheid = l_cluster_eenheid_ssm.id_h_eenheid and l_cluster_eenheid_ssm.dv_start_dts <= timestamp '2025-10-08 00:00:00' and l_cluster_eenheid_ssm.dv_end_dts > timestamp '2025-10-08 00:00:00' ) as qd001_e from datavault.s_h_eenheid_ssm eenheid It produces the following execution plan: Seq Scan on s_h_eenheid_ssm eenheid (cost=0.00..172110004562434.50 rows=17211 width=21) SubPlan 1 -> Nested Loop (cost=10000000000.00..10000007950.48 rows=30 width=0) -> Seq Scan on l_cluster_eenheid_ssm (cost=0.00..6768.46 rows=15 width=0) Filter: ((dv_start_dts <= '2025-10-08 00:00:00'::timestamp without time zone) AND (dv_end_dts > '2025-10-08 00:00:00'::timestamp without time zone) AND (id_h_eenheid = eenheid.id_h_eenheid)) -> Materialize (cost=0.00..1181.65 rows=2 width=0) -> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_s (cost=0.00..1181.64 rows=2 width=0) Filter: (_l_eenheid = eenheid.id_h_eenheid) I can make the query very fast by adding a self-join to the root table (s_h_eenheid_ssm) inside the subquery: select eenheid.id_s as id_s , eenheid.identificatie as id , exists( select 1 from datavault.s_h_eenheid_ssm eenheid_s inner join datavault.l_cluster_eenheid_ssm l_cluster_eenheid_ssm on eenheid_s.id_h_eenheid = l_cluster_eenheid_ssm.id_h_eenheid inner join datavault.s_h_huurovereenkomst_ssm huurovereenkomst_s on eenheid_s.id_h_eenheid = huurovereenkomst_s._l_eenheid where eenheid.id_h_eenheid = eenheid_s.id_h_eenheid and l_cluster_eenheid_ssm.dv_start_dts <= timestamp '2025-10-08 00:00:00' and l_cluster_eenheid_ssm.dv_end_dts > timestamp '2025-10-08 00:00:00' ) as DQ87_e from datavault.s_h_eenheid_ssm eenheid which produces a way better plan: QUERY PLAN Seq Scan on s_h_eenheid_ssm eenheid (cost=0.00..344220005300951.31 rows=17211 width=21) SubPlan 2 -> Hash Join (cost=3244.40..14039.76 rows=371258 width=4) Hash Cond: (l_cluster_eenheid_ssm.id_h_eenheid = eenheid_s.id_h_eenheid) -> Seq Scan on l_cluster_eenheid_ssm (cost=0.00..6139.82 rows=251455 width=4) Filter: ((dv_start_dts <= '2025-10-08 00:00:00'::timestamp without time zone) AND (dv_end_dts > '2025-10-08 00:00:00'::timestamp without time zone)) -> Hash (cost=2926.76..2926.76 rows=25411 width=8) -> Hash Join (cost=1459.25..2926.76 rows=25411 width=8) Hash Cond: (huurovereenkomst_s._l_eenheid = eenheid_s.id_h_eenheid) -> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_s (cost=0.00..1118.11 rows=25411 width=4) -> Hash (cost=1244.11..1244.11 rows=17211 width=4) -> Seq Scan on s_h_eenheid_ssm eenheid_s (cost=0.00..1244.11 rows=17211 width=4) The difference in execution speed is large; the slow query takes about 30 seconds on a test system while the fast one is under a second. I do not understand why the simpler query (without the self join) produces a plan that seems to require nested loops, I hope someone can explain. --000000000000899c390640b5ebbe Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Dear list,

We have databases with rather large tabl= es, and we do bulk queries on them, i.e. those queries always have a large = result set.
We execute those queries with enable_nestloop set to false, = because having nested loops in those queries always cause large performance= issues. This usually leads to perfectly acceptable execution plans which u= se table scans, hash joins and merge joins. We have work_mem set to 64MB to= 512MB (depending on the actual database size) to help with these.

I= have a query that does not want to work at all without a nested loop and a= materialize, while a trivial (but for me strange) change will allow it to = make a good plan, and I'm wondering why that is.
The below examples = are stripped versions of the real queries (which have a lot more filter con= ditions) but they exhibit the issue. I tested them on both Postgres 16 and = 18, on Ubuntu 25.04, with the same results.

The slow query is this o= ne:

select
=C2=A0 =C2=A0 eenheid.id_s as id_s
, =C2=A0 eenheid= .identificatie as id
, =C2=A0 exists(
=C2=A0 =C2=A0 select
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 1
=C2=A0 =C2=A0 from datavault.l_cluster_eenheid_ss= m
=C2=A0 =C2=A0 inner join datavault.s_h_huurovereenkomst_ssm huuroveree= nkomst_s
=C2=A0 =C2=A0 =C2=A0 =C2=A0 on eenheid.id_h_eenheid =3D huurove= reenkomst_s._l_eenheid
=C2=A0 =C2=A0 where eenheid.id_h_eenheid =3D l_cl= uster_eenheid_ssm.id_h_eenheid
=C2=A0 =C2=A0 and l_cluster_eenheid_ssm.d= v_start_dts <=3D timestamp '2025-10-08 00:00:00'
=C2=A0 =C2= =A0 and l_cluster_eenheid_ssm.dv_end_dts > timestamp '2025-10-08 00:= 00:00'
) as qd001_e
from datavault.s_h_eenheid_ssm eenheid
It produces the following execution plan:
Seq Scan on s_h_eenheid_ssm e= enheid =C2=A0(cost=3D0.00..172110004562434.50 rows=3D17211 width=3D21)
= =C2=A0 SubPlan 1
=C2=A0 =C2=A0 -> =C2=A0Nested Loop =C2=A0(cost=3D100= 00000000.00..10000007950.48 rows=3D30 width=3D0)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 -> =C2=A0Seq Scan on l_cluster_eenheid_ssm =C2=A0(cost=3D0= .00..6768.46 rows=3D15 width=3D0)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 Filter: ((dv_start_dts <=3D '2025-10-08 00:00:00&#= 39;::timestamp without time zone) AND (dv_end_dts > '2025-10-08 00:0= 0:00'::timestamp without time zone) AND (id_h_eenheid =3D eenheid.id_h_= eenheid))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Materialize =C2= =A0(cost=3D0.00..1181.65 rows=3D2 width=3D0)
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Seq Scan on s_h_huurovereenkomst_s= sm huurovereenkomst_s =C2=A0(cost=3D0.00..1181.64 rows=3D2 width=3D0)
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 Filter: (_l_eenheid =3D eenheid.id_h_eenheid)

I can make the que= ry very fast by adding a self-join to the root table (s_h_eenheid_ssm) insi= de the subquery:

select
=C2=A0 =C2=A0 eenheid.id_s as id_s
, = =C2=A0 eenheid.identificatie as id
, =C2=A0 exists(
=C2=A0 =C2=A0 sel= ect
=C2=A0 =C2=A0 =C2=A0 =C2=A0 1
=C2=A0 =C2=A0 from datavault.s_h_ee= nheid_ssm eenheid_s
=C2=A0 =C2=A0 inner join datavault.l_cluster_eenheid= _ssm l_cluster_eenheid_ssm
=C2=A0 =C2=A0 =C2=A0 =C2=A0 on eenheid_s.id_h= _eenheid =3D l_cluster_eenheid_ssm.id_h_eenheid
=C2=A0 =C2=A0 inner join= datavault.s_h_huurovereenkomst_ssm huurovereenkomst_s
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 on eenheid_s.id_h_eenheid =3D huurovereenkomst_s._l_eenheid
= =C2=A0 =C2=A0 where eenheid.id_h_eenheid =3D eenheid_s.id_h_eenheid
=C2= =A0 =C2=A0 and l_cluster_eenheid_ssm.dv_start_dts <=3D timestamp '20= 25-10-08 00:00:00'
=C2=A0 =C2=A0 and l_cluster_eenheid_ssm.dv_end_dt= s > timestamp '2025-10-08 00:00:00'
) as DQ87_e
from datav= ault.s_h_eenheid_ssm eenheid

which produces a way better plan:
QUERY PLAN
Seq Scan on s_h_eenheid_ssm eenheid =C2=A0(cost=3D0.00..344= 220005300951.31 rows=3D17211 width=3D21)
=C2=A0 SubPlan 2
=C2=A0 =C2= =A0 -> =C2=A0Hash Join =C2=A0(cost=3D3244.40..14039.76 rows=3D371258 wid= th=3D4)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Hash Cond: (l_cluster_eenheid= _ssm.id_h_eenheid =3D eenheid_s.id_h_eenheid)
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 -> =C2=A0Seq Scan on l_cluster_eenheid_ssm =C2=A0(cost=3D0.00= ..6139.82 rows=3D251455 width=3D4)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 Filter: ((dv_start_dts <=3D '2025-10-08 00:00:0= 0'::timestamp without time zone) AND (dv_end_dts > '2025-10-08 0= 0:00:00'::timestamp without time zone))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 -> =C2=A0Hash =C2=A0(cost=3D2926.76..2926.76 rows=3D25411 width= =3D8)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2= =A0Hash Join =C2=A0(cost=3D1459.25..2926.76 rows=3D25411 width=3D8)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 H= ash Cond: (huurovereenkomst_s._l_eenheid =3D eenheid_s.id_h_eenheid)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -= > =C2=A0Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_s =C2=A0(c= ost=3D0.00..1118.11 rows=3D25411 width=3D4)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Hash =C2=A0(co= st=3D1244.11..1244.11 rows=3D17211 width=3D4)
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -= > =C2=A0Seq Scan on s_h_eenheid_ssm eenheid_s =C2=A0(cost=3D0.00..1244.1= 1 rows=3D17211 width=3D4)

The difference in execution speed is large= ; the slow query takes about 30 seconds on a test system while the fast one= is under a second.

I do not understand why the simpler query (witho= ut the self join) produces a plan that seems to require nested loops, I hop= e someone can explain.

--000000000000899c390640b5ebbe--