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 1v1s0s-00DWQf-8b for pgsql-performance@arkaria.postgresql.org; Thu, 25 Sep 2025 19:52:22 +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 1v1s0q-005VBd-W9 for pgsql-performance@arkaria.postgresql.org; Thu, 25 Sep 2025 19:52:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1rjt-005EuZ-9I for pgsql-performance@lists.postgresql.org; Thu, 25 Sep 2025 19:34:49 +0000 Received: from mail-vs1-xe2f.google.com ([2607:f8b0:4864:20::e2f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1rjq-002Q3m-1y for Pgsql-performance@postgresql.org; Thu, 25 Sep 2025 19:34:48 +0000 Received: by mail-vs1-xe2f.google.com with SMTP id ada2fe7eead31-50f8bf5c518so943874137.3 for ; Thu, 25 Sep 2025 12:34:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758828885; x=1759433685; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=tP0zHnaOPxDjvamj0LtbbvaXAWfV8IjEfKNyV2g/U3Y=; b=RsxP22UaYE48GuZw1/iXdXLkcXEXSr/wO3W66f7QiI24LS/eRs/HASyhj5AvK5G/Au BEqbcK3+jGUXnYriUVrD1vC/Z25J8ybfEv0JFbDDX7BdsbU9DlegSig1jaoA7TC3emw9 9r+/oIm4oO1DVQpXjQeZI02/Uufexby8ara4x03/X6dlI3sSfxB4bMgr3Aa2a4kA028i FAQ4dWiGipSI2L4uAsIPrd/eONsfpUIOmP7L/YcOVCVthVFukaVlksiFcDs8nVjtzKTA lM5CVRJHIwty3pP3oijZ5DN0swBNh1MLXsxWeC7rdY0V7Mp5H6F1HSnqxNlbO4nF8bfH PnjQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758828885; x=1759433685; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=tP0zHnaOPxDjvamj0LtbbvaXAWfV8IjEfKNyV2g/U3Y=; b=ASaWseD+yEharNlN810ZICcK4ThWtWgvpb6kMIC3rWiYkSoH/s6ALqpzAywvSJO60Y Yhli0nz2a4C84xui/K0PxsWzfbS6q6WvcgsdtSop6xvP8wwG43BhcCpbU4uzki6S8equ JwJJ5Ym0uuwibY6RnoiVxfqjBfkNlUHxGG9N8FrNF4jRxG4pVzxG8FDvimV/injcMan2 cBlYgz9pgs6SiDJlxYhy6hq7R0NEmBd4bNRnRjB6ssjX5jiKlGdU/fEJbIOKCWumyc2y uCYLZOfswYP1GIL/v3RcYDxU5UWGLlCfVzU+RgONxaS7R4Wrye5QjtXCpH/Elfyv5Eai TpcQ== X-Gm-Message-State: AOJu0YxZPcnDZ3yOH+XtRbdMBULkiEozSYWbegKBTjweBmSPAh0TNweW 0SJviqfvd61N4HrS2XCJJFSUzAZIFF+5m2kcEs016ViAyUgcRrZ7HiFXugmGHzWJ+fHmxpEMbnL CQjBnCvvBICE31k2ho7WqWbhfE3tOCjvcRaUhOfs= X-Gm-Gg: ASbGncuClw/IuJbuqpV4u8oYWFEL/OwGF3Q5XzneYlvVivpn5D/XdaHomKO613OYm3w 9GXeaMDPKOAxhlDR1/TX5Ur2Pj96Myt5wBrTGhbx3nqlUE+N9THIjMr7jto3oOI8vR1Umz0d4X4 KLoAdn5Kz4uMpjigbpuUSQgR/hFKrJXm/JcIkV39VZc7R8X0XxInzoxY03KBuADamjZnlDHtIe5 MjYlrxuXiM4NlW2hfQ= X-Google-Smtp-Source: AGHT+IE4mZuEEJggL3Vk8lN0B4W6yxDYwtb3+zZ3N4XW9Y1WHS07pJhnfIsmMgEiIIlM8B+jHASC8CFVcoQm1doZFpQ= X-Received: by 2002:a05:6102:50a0:b0:530:f657:c25 with SMTP id ada2fe7eead31-5accb9ffebfmr2238104137.5.1758828885139; Thu, 25 Sep 2025 12:34:45 -0700 (PDT) MIME-Version: 1.0 From: Lauro Ojeda Date: Thu, 25 Sep 2025 21:34:29 +0200 X-Gm-Features: AS18NWADXtSem64-syF03HY73cuzob_Hpabh352t0-dlLD5Ecj9QKtDFwCIFSJc Message-ID: Subject: Partition pruning is not happening (even in PG18) To: Pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="000000000000eb6473063fa54278" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000eb6473063fa54278 Content-Type: text/plain; charset="UTF-8" Hi super-experts, I am trying to solve a mystery for a customer where they had some very large heap tables (500GB+ each) on their PG 13 database which was bringing them loads of performance problems. Apart from the regular server tuning efforts, I suggested them to upgrade it to at least Postgresql 15 and partition these large tables. They've accepted it and they have applied my suggestions. However, partition pruning is not happening. Instead, PG is doing sequential scans against all partitions. I thought it was PG 15 behaviour, so I tested the same on PG 17 and compiled PG18rc1, getting the same results. I read on the release notes of PG18 that partition pruning had many fixes but it seems this one is not in place. The only way I found to make pruning work is to force index_scan using pg_hint_plan, but I wanted to influence the planner to decide it by itself rather than relying on hints. What's the reason for this misbehaving and what could I do to overcome it? *Relevant parameters/info:* DB cluster hosted on Azure Flex Server random_page_cost=1.1; plan_cache_mode=force_custom_plan; *# the so called magic-bullet for pruning* default_statistics_target=1000;* # better stats to the planner* geqo_effort=7; *# I wanted the planner to "think" better and do pruning* enable_partition_pruning=on; *Test case* *-- Tables def* postgres=> \d+ accounts Partitioned table "public.accounts" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ------------------+---------+-----------+----------+---------------------------------------+----------+-------------+--------------+------------- aid | integer | | not null | nextval('accounts_aid_seq'::regclass) | plain | | | bid | integer | | | | plain | | | abalance | integer | | | | plain | | | filler | text | | | | extended | | | transaction_date | date | | not null | | plain | | | Partition key: RANGE (transaction_date) Indexes: "accounts_pkey" PRIMARY KEY, btree (transaction_date, aid) Partitions: accounts_p1 FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'), accounts_p10 FOR VALUES FROM ('2025-10-01') TO ('2025-11-01'), accounts_p11 FOR VALUES FROM ('2025-11-01') TO ('2025-12-01'), accounts_p12 FOR VALUES FROM ('2025-12-01') TO ('2026-01-01'), accounts_p2 FOR VALUES FROM ('2025-02-01') TO ('2025-03-01'), accounts_p3 FOR VALUES FROM ('2025-03-01') TO ('2025-04-01'), accounts_p4 FOR VALUES FROM ('2025-04-01') TO ('2025-05-01'), accounts_p5 FOR VALUES FROM ('2025-05-01') TO ('2025-06-01'), accounts_p6 FOR VALUES FROM ('2025-06-01') TO ('2025-07-01'), accounts_p7 FOR VALUES FROM ('2025-07-01') TO ('2025-08-01'), accounts_p8 FOR VALUES FROM ('2025-08-01') TO ('2025-09-01'), accounts_p9 FOR VALUES FROM ('2025-09-01') TO ('2025-10-01') postgres=> \d+ t2 Table "public.t2" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+------+-----------+----------+---------+---------+-------------+--------------+------------- dt_col | date | | | | plain | | | Access method: heap *-- Executing test* *-- Regular execution* postgres=> explain analyze select aid, abalance from accounts where transaction_date in (select dt_col from t2); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1001.23..2073502.05 rows=2767123 width=8) (actual time=3.574..211619.991 rows=1664742 loops=1) Workers Planned: 2 Workers Launched: 1 -> Hash Semi Join (cost=1.23..1795789.75 rows=1152968 width=8) (actual time=77575.241..203629.828 rows=832371 loops=2) Hash Cond: (accounts.transaction_date = t2.dt_col) -> Parallel Append (cost=0.00..1672493.00 rows=42083334 width=12) (actual time=0.645..197115.678 rows=50500000 loops=2) -> Parallel Seq Scan on accounts_p10 accounts_10 (cost=0.00..124553.77 rows=3585078 width=12) (actual time=0.560..30193.541 rows=8604186 loops=1) -> Parallel Seq Scan on accounts_p5 accounts_5 (cost=0.00..124550.68 rows=3584968 width=12) (actual time=56.415..54334.025 rows=8603923 loops=1) -> Parallel Seq Scan on accounts_p8 accounts_8 (cost=0.00..124535.20 rows=3584520 width=12) (actual time=1.738..31555.264 rows=8602847 loops=1) -> Parallel Seq Scan on accounts_p7 accounts_7 (cost=0.00..124531.08 rows=3584408 width=12) (actual time=2.357..29998.452 rows=8602579 loops=1) -> Parallel Seq Scan on accounts_p3 accounts_3 (cost=0.00..124481.85 rows=3582985 width=12) (actual time=2.525..24765.109 rows=8599165 loops=1) -> Parallel Seq Scan on accounts_p1 accounts_1 (cost=0.00..122514.91 rows=3526391 width=12) (actual time=2.675..13909.461 rows=4231669 loops=2) -> Parallel Seq Scan on accounts_p12 accounts_12 (cost=0.00..122486.55 rows=3525555 width=12) (actual time=32.199..14350.771 rows=8461332 loops=1) -> Parallel Seq Scan on accounts_p6 accounts_6 (cost=0.00..120551.91 rows=3469891 width=12) (actual time=1.284..27367.522 rows=8327739 loops=1) -> Parallel Seq Scan on accounts_p9 accounts_9 (cost=0.00..120515.24 rows=3468824 width=12) (actual time=2.594..29812.536 rows=8325177 loops=1) -> Parallel Seq Scan on accounts_p4 accounts_4 (cost=0.00..120505.38 rows=3468538 width=12) (actual time=42.894..30691.460 rows=8324491 loops=1) -> Parallel Seq Scan on accounts_p11 accounts_11 (cost=0.00..120459.09 rows=3467209 width=12) (actual time=1.068..52911.011 rows=8321302 loops=1) -> Parallel Seq Scan on accounts_p2 accounts_2 (cost=0.00..112390.67 rows=3234967 width=12) (actual time=0.728..28041.580 rows=7763921 loops=1) -> Hash (cost=1.10..1.10 rows=10 width=4) (actual time=0.261..0.265 rows=6 loops=2) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on t2 (cost=0.00..1.10 rows=10 width=4) (actual time=0.253..0.256 rows=6 loops=2) Planning Time: 1.490 ms Execution Time: 211741.385 ms (23 rows) *-- Forcing pruning using hints* postgres=> explain analyze */*+ IndexScan(accounts accounts_pkey) */* select aid, abalance from accounts where transaction_date in (select dt_col from t2); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=1.56..53359847.63 rows=2767123 width=8) (actual time=9.253..103956.379 rows=1664742 loops=1) -> HashAggregate (cost=1.12..1.23 rows=10 width=4) (actual time=0.370..59.714 rows=6 loops=1) Group Key: t2.dt_col Batches: 1 Memory Usage: 24kB -> Seq Scan on t2 (cost=0.00..1.10 rows=10 width=4) (actual time=0.359..0.362 rows=6 loops=1) -> Append (cost=0.43..5302777.61 rows=3320703 width=12) (actual time=3.113..17280.161 rows=277457 loops=6) -> Index Scan using accounts_p1_pkey on accounts_p1 accounts_1 (cost=0.43..434635.82 rows=273011 width=12) (actual time=5.202..17125.276 rows=276971 loops=1) Index Cond: (transaction_date = t2.dt_col) -> Index Scan using accounts_p2_pkey on accounts_p2 accounts_2 (cost=0.43..441097.44 rows=277283 width=12) (actual time=0.718..14671.096 rows=276634 loops=1) Index Cond: (transaction_date = t2.dt_col) -> Index Scan using accounts_p3_pkey on accounts_p3 accounts_3 (cost=0.43..441666.94 rows=277392 width=12) (actual time=1.368..15826.853 rows=278390 loops=1) Index Cond: (transaction_date = t2.dt_col) -> Index Scan using accounts_p4_pkey on accounts_p4 accounts_4 (cost=0.43..441689.70 rows=277483 width=12) (never executed) Index Cond: (transaction_date = t2.dt_col) -> Index Scan using accounts_p5_pkey on accounts_p5 accounts_5 (cost=0.43..441883.26 rows=277546 width=12) (never executed) Index Cond: (transaction_date = t2.dt_col) -> Index Scan using accounts_p6_pkey on accounts_p6 accounts_6 (cost=0.43..441857.26 rows=277591 width=12) (actual time=3.678..18625.085 rows=277582 loops=3) Index Cond: (transaction_date = t2.dt_col) -> Index Scan using accounts_p7_pkey on accounts_p7 accounts_7 (cost=0.43..441837.57 rows=277503 width=12) (never executed) Index Cond: (transaction_date = t2.dt_col) -> Index Scan using accounts_p8_pkey on accounts_p8 accounts_8 (cost=0.43..441843.95 rows=277511 width=12) (never executed) Index Cond: (transaction_date = t2.dt_col) -> Index Scan using accounts_p9_pkey on accounts_p9 accounts_9 (cost=0.43..441711.03 rows=277506 width=12) (never executed) Index Cond: (transaction_date = t2.dt_col) -> Index Scan using accounts_p10_pkey on accounts_p10 accounts_10 (cost=0.43..441918.96 rows=277554 width=12) (never executed) Index Cond: (transaction_date = t2.dt_col) -> Index Scan using accounts_p11_pkey on accounts_p11 accounts_11 (cost=0.43..441501.86 rows=277377 width=12) (never executed) Index Cond: (transaction_date = t2.dt_col) -> Index Scan using accounts_p12_pkey on accounts_p12 accounts_12 (cost=0.43..434530.31 rows=272946 width=12) (never executed) Index Cond: (transaction_date = t2.dt_col) Planning Time: 1.147 ms Execution Time: 104040.968 ms (32 rows) Any help/explanation would be appreciated. Also, how could I contribute to get this partition pruning to work? Kindest regards, -- *Lauro Ojeda* --000000000000eb6473063fa54278 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi super-experts,
I am trying to solve a my= stery=C2=A0for a customer where they had some very large heap tables (500GB= + each) on their PG 13 database which was bringing them loads of performanc= e problems.=C2=A0
Apart from the regular server tuning efforts, I= suggested them to upgrade it to at least Postgresql 15 and partition these= large tables. They've accepted it and they have applied my suggestions= .
However, partition=C2=A0 pruning is not happening. Instead, PG = is doing sequential scans against all partitions. I thought it was PG 15 be= haviour, so I tested the same on PG 17 and compiled PG18rc1, getting the sa= me results. I read on the release notes of PG18 that partition pruning had = many fixes but it seems this one is not in place.
The only way I = found to make pruning work is to force index_scan using pg_hint_plan, but I= wanted to influence the planner to decide it by itself rather than relying= on hints. What's the reason for this misbehaving and what could I do t= o overcome it?

Relevant parameters/info:
DB cluster hosted on Azure Flex Server
random_page_cost= =3D1.1;
plan_cache_mode=3Dforce_custom_plan;=C2=A0# the so cal= led magic-bullet for pruning
default_statistics_target= =3D1000; # better stats to the planner
geqo_effort=3D7;=C2= =A0# I wanted the planner to "think" better and do pruning=
enable_partition_pruning=3Don;

Test case
-- Tables def
postgres=3D> \d+ accounts
=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=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=A0Partitioned table "public.accounts"
=C2=A0 =C2=A0 =C2=A0= Column =C2=A0 =C2=A0 =C2=A0| =C2=A0Type =C2=A0 | Collation | Nullable | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Default =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| Storage =C2=A0| Compression = | Stats target | Description
------------------+---------+-----------+--= --------+---------------------------------------+----------+-------------+-= -------------+-------------
=C2=A0aid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0| integer | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null | n= extval('accounts_aid_seq'::regclass) | plain =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=A0|
=C2=A0bid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| = integer | =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=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=A0 | plain = =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=A0|
=C2=A0abalance =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | integer | =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=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= =A0 | plain =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=A0|
=C2=A0filler =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | text =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=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=A0 =C2=A0 =C2=A0 | extended | =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= transaction_date | date =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |= not null | =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=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | pla= in =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=A0|
Partition key: RANGE (transaction_d= ate)
Indexes:
=C2=A0 =C2=A0 "accounts_pkey" PRIMARY KEY, bt= ree (transaction_date, aid)
Partitions: accounts_p1 FOR VALUES FROM (= 9;2025-01-01') TO ('2025-02-01'),
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 accounts_p10 FOR VALUES FROM ('2025-10-01') TO (&= #39;2025-11-01'),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 accounts= _p11 FOR VALUES FROM ('2025-11-01') TO ('2025-12-01'),
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 accounts_p12 FOR VALUES FROM (= 9;2025-12-01') TO ('2026-01-01'),
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 accounts_p2 FOR VALUES FROM ('2025-02-01') TO (&#= 39;2025-03-01'),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 accounts_= p3 FOR VALUES FROM ('2025-03-01') TO ('2025-04-01'),
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 accounts_p4 FOR VALUES FROM ('20= 25-04-01') TO ('2025-05-01'),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 accounts_p5 FOR VALUES FROM ('2025-05-01') TO ('2= 025-06-01'),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 accounts_p6 F= OR VALUES FROM ('2025-06-01') TO ('2025-07-01'),
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 accounts_p7 FOR VALUES FROM ('2025-0= 7-01') TO ('2025-08-01'),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 accounts_p8 FOR VALUES FROM ('2025-08-01') TO ('2025-09= -01'),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 accounts_p9 FOR VAL= UES FROM ('2025-09-01') TO ('2025-10-01')

postgres= =3D> \d+ t2
=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=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 Table "public.t2"
=C2=A0Column | Type | Coll= ation | Nullable | Default | Storage | Compression | Stats target | Descrip= tion
--------+------+-----------+----------+---------+---------+--------= -----+--------------+-------------
=C2=A0dt_col | date | =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 | plain =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|
Access method: heap

-- Executing test
-- Regular executi= on
postgres=3D> explain analyze
= select aid, abalance
from accounts
where transaction_date in (select = dt_col from t2);
=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=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=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY = PLAN
-------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------
=C2=A0Gather =C2=A0(cost=3D1001.23..2073502.05 ro= ws=3D2767123 width=3D8) (actual time=3D3.574..211619.991 rows=3D1664742 loo= ps=3D1)
=C2=A0 =C2=A0Workers Planned: 2
=C2=A0 =C2=A0Workers Launched= : 1
=C2=A0 =C2=A0-> =C2=A0Hash Semi Join =C2=A0(cost=3D1.23..1795789.= 75 rows=3D1152968 width=3D8) (actual time=3D77575.241..203629.828 rows=3D83= 2371 loops=3D2)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Hash Cond: (accounts.t= ransaction_date =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> = =C2=A0Parallel Append =C2=A0(cost=3D0.00..1672493.00 rows=3D42083334 width= =3D12) (actual time=3D0.645..197115.678 rows=3D50500000 loops=3D2)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Se= q Scan on accounts_p10 accounts_10 =C2=A0(cost=3D0.00..124553.77 rows=3D358= 5078 width=3D12) (actual time=3D0.560..30193.541 rows=3D8604186 loops=3D1)<= br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Paral= lel Seq Scan on accounts_p5 accounts_5 =C2=A0(cost=3D0.00..124550.68 rows= =3D3584968 width=3D12) (actual time=3D56.415..54334.025 rows=3D8603923 loop= s=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2= =A0Parallel Seq Scan on accounts_p8 accounts_8 =C2=A0(cost=3D0.00..124535.2= 0 rows=3D3584520 width=3D12) (actual time=3D1.738..31555.264 rows=3D8602847= loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->= =C2=A0Parallel Seq Scan on accounts_p7 accounts_7 =C2=A0(cost=3D0.00..1245= 31.08 rows=3D3584408 width=3D12) (actual time=3D2.357..29998.452 rows=3D860= 2579 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-= > =C2=A0Parallel Seq Scan on accounts_p3 accounts_3 =C2=A0(cost=3D0.00..= 124481.85 rows=3D3582985 width=3D12) (actual time=3D2.525..24765.109 rows= =3D8599165 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0-> =C2=A0Parallel Seq Scan on accounts_p1 accounts_1 =C2=A0(cost= =3D0.00..122514.91 rows=3D3526391 width=3D12) (actual time=3D2.675..13909.4= 61 rows=3D4231669 loops=3D2)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0-> =C2=A0Parallel Seq Scan on accounts_p12 accounts_12 =C2= =A0(cost=3D0.00..122486.55 rows=3D3525555 width=3D12) (actual time=3D32.199= ..14350.771 rows=3D8461332 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Seq Scan on accounts_p6 accounts_= 6 =C2=A0(cost=3D0.00..120551.91 rows=3D3469891 width=3D12) (actual time=3D1= .284..27367.522 rows=3D8327739 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Seq Scan on accounts_p9 acc= ounts_9 =C2=A0(cost=3D0.00..120515.24 rows=3D3468824 width=3D12) (actual ti= me=3D2.594..29812.536 rows=3D8325177 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Seq Scan on accounts_p4= accounts_4 =C2=A0(cost=3D0.00..120505.38 rows=3D3468538 width=3D12) (actua= l time=3D42.894..30691.460 rows=3D8324491 loops=3D1)
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Seq Scan on acco= unts_p11 accounts_11 =C2=A0(cost=3D0.00..120459.09 rows=3D3467209 width=3D1= 2) (actual time=3D1.068..52911.011 rows=3D8321302 loops=3D1)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Seq Scan = on accounts_p2 accounts_2 =C2=A0(cost=3D0.00..112390.67 rows=3D3234967 widt= h=3D12) (actual time=3D0.728..28041.580 rows=3D7763921 loops=3D1)
=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Hash =C2=A0(cost=3D1.10..1.10 rows= =3D10 width=3D4) (actual time=3D0.261..0.265 rows=3D6 loops=3D2)
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buckets: 1024 =C2=A0Batches= : 1 =C2=A0Memory Usage: 9kB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0-> =C2=A0Seq Scan on t2 =C2=A0(cost=3D0.00..1.10 rows=3D10 = width=3D4) (actual time=3D0.253..0.256 rows=3D6 loops=3D2)
=C2=A0Plannin= g Time: 1.490 ms
=C2=A0Execution Time: 211741.385 ms
(23 rows)


-- Forcing pruning using hints
postgres=3D> explain analyze
/*+ IndexScan(a= ccounts accounts_pkey) */
select aid, abalance
from accounts
w= here transaction_date in (select dt_col from t2);
=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=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=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
-----------------------= ---------------------------------------------------------------------------= -----------------------------------------------------------------------
= =C2=A0Nested Loop =C2=A0(cost=3D1.56..53359847.63 rows=3D2767123 width=3D8)= (actual time=3D9.253..103956.379 rows=3D1664742 loops=3D1)
=C2=A0 =C2= =A0-> =C2=A0HashAggregate =C2=A0(cost=3D1.12..1.23 rows=3D10 width=3D4) = (actual time=3D0.370..59.714 rows=3D6 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Group Key: t2.dt_col
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Batc= hes: 1 =C2=A0Memory Usage: 24kB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> = =C2=A0Seq Scan on t2 =C2=A0(cost=3D0.00..1.10 rows=3D10 width=3D4) (actual = time=3D0.359..0.362 rows=3D6 loops=3D1)
=C2=A0 =C2=A0-> =C2=A0Append = =C2=A0(cost=3D0.43..5302777.61 rows=3D3320703 width=3D12) (actual time=3D3.= 113..17280.161 rows=3D277457 loops=3D6)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0Index Scan using accounts_p1_pkey on accounts_p1 accounts_1 = =C2=A0(cost=3D0.43..434635.82 rows=3D273011 width=3D12) (actual time=3D5.20= 2..17125.276 rows=3D276971 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)
=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using accounts_p2_pkey o= n accounts_p2 accounts_2 =C2=A0(cost=3D0.43..441097.44 rows=3D277283 width= =3D12) (actual time=3D0.718..14671.096 rows=3D276634 loops=3D1)
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_da= te =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Sc= an using accounts_p3_pkey on accounts_p3 accounts_3 =C2=A0(cost=3D0.43..441= 666.94 rows=3D277392 width=3D12) (actual time=3D1.368..15826.853 rows=3D278= 390 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0In= dex Cond: (transaction_date =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0-> =C2=A0Index Scan using accounts_p4_pkey on accounts_p4 accounts= _4 =C2=A0(cost=3D0.43..441689.70 rows=3D277483 width=3D12) (never executed)=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (tra= nsaction_date =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2= =A0Index Scan using accounts_p5_pkey on accounts_p5 accounts_5 =C2=A0(cost= =3D0.43..441883.26 rows=3D277546 width=3D12) (never executed)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date = =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan = using accounts_p6_pkey on accounts_p6 accounts_6 =C2=A0(cost=3D0.43..441857= .26 rows=3D277591 width=3D12) (actual time=3D3.678..18625.085 rows=3D277582= loops=3D3)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index= Cond: (transaction_date =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0Index Scan using accounts_p7_pkey on accounts_p7 accounts_7 = =C2=A0(cost=3D0.43..441837.57 rows=3D277503 width=3D12) (never executed)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transa= ction_date =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0= Index Scan using accounts_p8_pkey on accounts_p8 accounts_8 =C2=A0(cost=3D0= .43..441843.95 rows=3D277511 width=3D12) (never executed)
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D = t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan usin= g accounts_p9_pkey on accounts_p9 accounts_9 =C2=A0(cost=3D0.43..441711.03 = rows=3D277506 width=3D12) (never executed)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using accounts_p10= _pkey on accounts_p10 accounts_10 =C2=A0(cost=3D0.43..441918.96 rows=3D2775= 54 width=3D12) (never executed)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using accounts_p11_pkey on a= ccounts_p11 accounts_11 =C2=A0(cost=3D0.43..441501.86 rows=3D277377 width= =3D12) (never executed)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0Index Cond: (transaction_date =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0-> =C2=A0Index Scan using accounts_p12_pkey on accounts_p1= 2 accounts_12 =C2=A0(cost=3D0.43..434530.31 rows=3D272946 width=3D12) (neve= r executed)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index= Cond: (transaction_date =3D t2.dt_col)
=C2=A0Planning Time: 1.147 ms=C2=A0Execution Time: 104040.968 ms
(32 rows)

Any help/explanation would be appreciated.
Also, how could= I contribute to get this partition pruning to work?

Kindest regards,
-- <= br>
Lauro Ojeda
--000000000000eb6473063fa54278--