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 1v1sLR-00Db9I-JO for pgsql-performance@arkaria.postgresql.org; Thu, 25 Sep 2025 20:13:37 +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 1v1sLQ-005jrx-9P for pgsql-performance@arkaria.postgresql.org; Thu, 25 Sep 2025 20:13:36 +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 1v1sLP-005jro-TM for pgsql-performance@lists.postgresql.org; Thu, 25 Sep 2025 20:13:35 +0000 Received: from mail-ej1-x635.google.com ([2a00:1450:4864:20::635]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1sLN-0005N0-0h for pgsql-performance@postgresql.org; Thu, 25 Sep 2025 20:13:35 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-b2e66a300cbso275882866b.3 for ; Thu, 25 Sep 2025 13:13:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=kleczek.org; s=google; t=1758831212; x=1759436012; darn=postgresql.org; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=zkpisUDzWiBFzKjlg7PICqS+ygDeHYjh8ee72MVYipw=; b=IaZBZ61qOcGgRoeKHordjLWmn3YI+gf3W6oF1YqNawBtf8g+viT4NGQQHCH/fTiwWS 0rNKImw4f3Jyn3AUVMei0E8o4I5gMdV2+OaL3NY9MBDc5zpbWKEpDZCEFQ4CVJciHdlR FH6yw4+x17gSI+y28aoU2UWL3GlfxH4ZNaaGU= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758831212; x=1759436012; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=zkpisUDzWiBFzKjlg7PICqS+ygDeHYjh8ee72MVYipw=; b=sKOEAY9NSE3vejrW8l3WpPoRO3XhRCrhJib0dOUJTCcwA1tnAxKHX/P0tc3f7Vj/o7 HNuwamwTJM4SojBjT8o1SrrbqC8kMqD+ooyiHyEIHug4qFggCJFUWjknZSDQslp0WCkK dJN8zBB0yg8F0nqyIZPuXS1ETofifQPIIF+bpF2ehsyarCmEH/EoHlCfsxl5PrjZ/uMx HZq8j8EwD9/BxxACxethj2T5drFPv6N0tBGXWqBoDPUq2LEGXDqE/kzwb+B2FDzyp+O8 ARZmbr9BOLpqFk2+NjlUDZWCvRqBbMBMETBF70S42oUDRRtc9ysXaxK5LnB3cNZd8VbO yCbA== X-Gm-Message-State: AOJu0Yw4rSiR90/Gb+z/IrCAYFhfA0ZgfowUlL+ETjAqnEpuXw0/pKOc B17PrYe6KcTUR9A1DacqFRo1dvw2jP29NgQwCUwtPefeE87Hjkldfy7pCZDdzXAsDXm7sHyRQtW /pY4C X-Gm-Gg: ASbGncu13EFL+6sPmCt6vRXoNbR839ML3w8kOuwPGc5UhveNZpqc/iHPSyJ2AxU0PIE utdxG8noQHdSVGuSeSIw6zgBmleimT+0u2FL4QvemLegMVsHJu1jn5D6PFixSDSVycOjIGgVm6n L5GQzVruoVxG7moEYtecmpj4uLjMgblvKo+d05Ch4PY/ylJdFat5ZC/nPoEaayAaHCYROxhq+oR n+WIkiVQXv5YUUYTgZdwav0+jIT03kCyZGNETfjTiGgtfP6QtEU6gjkuuyW/Ydgu0PyYv6fwuRC PiEYIDcM553OreszFdiFPGls97twHYj50jmu4DH8m7Z5lRhfFvp0pBaW9odeandQu+2dAWf/mQa eGA40XcWqMMI4XhM8tyVdOBEMEk+x0En9jpeGPGk2wRsYfF1Axg== X-Google-Smtp-Source: AGHT+IGHBgVh0RHNMVc+AqFZCd7Mh/hF4GpXI7V/QP7nukergam7J26lFnv7/Ot8HvneSrBTNi7P5g== X-Received: by 2002:a17:907:9803:b0:b18:63b8:c508 with SMTP id a640c23a62f3a-b34be0fd02cmr453121866b.44.1758831211674; Thu, 25 Sep 2025 13:13:31 -0700 (PDT) Received: from smtpclient.apple (b15.kleczek.org. [185.20.172.182]) by smtp.gmail.com with ESMTPSA id 4fb4d7f45d1cf-634a3ae305csm1679141a12.30.2025.09.25.13.13.31 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 25 Sep 2025 13:13:31 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-05BE7AD0-6817-466F-B0AB-9A8C149AC4BD Content-Transfer-Encoding: 7bit From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= Mime-Version: 1.0 (1.0) Subject: Re: Partition pruning is not happening (even in PG18) Date: Thu, 25 Sep 2025 22:13:10 +0200 Message-Id: References: <811FB044-CDCA-49F6-9327-D95F046FE5B0@kleczek.org> Cc: pgsql-performance@postgresql.org In-Reply-To: <811FB044-CDCA-49F6-9327-D95F046FE5B0@kleczek.org> To: Lauro Ojeda X-Mailer: iPhone Mail (22G100) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-05BE7AD0-6817-466F-B0AB-9A8C149AC4BD Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Never mind my message. I misread it and missed the plan with no hints. Micha=C5=82=20 > On 25 Sep 2025, at 22:10, Micha=C5=82 K=C5=82eczek wr= ote: >=20 > =EF=BB=BF > Hi, >=20 > Partition pruning is happening: pruned nodes are marked as =E2=80=9Cnever e= xecuted=E2=80=9D. > It is just that pruning is performed not by the planner but by the executo= r in this case. >=20 > =E2=80=94 >=20 > Micha=C5=82=20 >=20 >>> On 25 Sep 2025, at 21:49, Lauro Ojeda wrote: >>>=20 >> =EF=BB=BF >> Hi super-experts, >> I am trying to solve a mystery for a customer where they had some very la= rge heap tables (500GB+ each) on their PG 13 database which was bringing the= m loads of performance problems.=20 >> 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 acce= pted it and they have applied my suggestions. >> However, partition pruning is not happening. Instead, PG is doing sequen= tial scans against all partitions. I thought it was PG 15 behaviour, so I te= sted the same on PG 17 and compiled PG18rc1, getting the same results. I rea= d on the release notes of PG18 that partition pruning had many fixes but it s= eems 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 rat= her than relying on hints. What's the reason for this misbehaving and what c= ould I do to overcome it? >>=20 >> Relevant parameters/info: >> DB cluster hosted on Azure Flex Server >> random_page_cost=3D1.1; >> plan_cache_mode=3Dforce_custom_plan; # the so called magic-bullet for pru= ning >> default_statistics_target=3D1000; # better stats to the planner >> geqo_effort=3D7; # I wanted the planner to "think" better and do pruning >> enable_partition_pruning=3Don; >>=20 >> Test case >> -- Tables def >> postgres=3D> \d+ accounts >> Partitioned table "= public.accounts" >> Column | Type | Collation | Nullable | Defau= lt | Storage | Compression | Stats target | Description >> ------------------+---------+-----------+----------+---------------------= ------------------+----------+-------------+--------------+------------- >> aid | integer | | not null | nextval('accounts_ai= d_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') >>=20 >> postgres=3D> \d+ t2 >> Table "public.t2" >> Column | Type | Collation | Nullable | Default | Storage | Compression |= Stats target | Description >> --------+------+-----------+----------+---------+---------+-------------+= --------------+------------- >> dt_col | date | | | | plain | |= | >> Access method: heap >>=20 >> -- Executing test >> -- Regular execution >> postgres=3D> explain analyze >> select aid, abalance >> from accounts >> where transaction_date in (select dt_col from t2); >> = QUERY PLAN >> -------------------------------------------------------------------------= ----------------------------------------------------------------------------= --------------- >> Gather (cost=3D1001.23..2073502.05 rows=3D2767123 width=3D8) (actual ti= me=3D3.574..211619.991 rows=3D1664742 loops=3D1) >> Workers Planned: 2 >> Workers Launched: 1 >> -> Hash Semi Join (cost=3D1.23..1795789.75 rows=3D1152968 width=3D8)= (actual time=3D77575.241..203629.828 rows=3D832371 loops=3D2) >> Hash Cond: (accounts.transaction_date =3D t2.dt_col) >> -> Parallel Append (cost=3D0.00..1672493.00 rows=3D42083334 wi= dth=3D12) (actual time=3D0.645..197115.678 rows=3D50500000 loops=3D2) >> -> Parallel Seq Scan on accounts_p10 accounts_10 (cost=3D= 0.00..124553.77 rows=3D3585078 width=3D12) (actual time=3D0.560..30193.541 r= ows=3D8604186 loops=3D1) >> -> Parallel Seq Scan on accounts_p5 accounts_5 (cost=3D0= .00..124550.68 rows=3D3584968 width=3D12) (actual time=3D56.415..54334.025 r= ows=3D8603923 loops=3D1) >> -> Parallel Seq Scan on accounts_p8 accounts_8 (cost=3D0= .00..124535.20 rows=3D3584520 width=3D12) (actual time=3D1.738..31555.264 ro= ws=3D8602847 loops=3D1) >> -> Parallel Seq Scan on accounts_p7 accounts_7 (cost=3D0= .00..124531.08 rows=3D3584408 width=3D12) (actual time=3D2.357..29998.452 ro= ws=3D8602579 loops=3D1) >> -> Parallel Seq Scan on accounts_p3 accounts_3 (cost=3D0= .00..124481.85 rows=3D3582985 width=3D12) (actual time=3D2.525..24765.109 ro= ws=3D8599165 loops=3D1) >> -> Parallel Seq Scan on accounts_p1 accounts_1 (cost=3D0= .00..122514.91 rows=3D3526391 width=3D12) (actual time=3D2.675..13909.461 ro= ws=3D4231669 loops=3D2) >> -> Parallel Seq Scan on accounts_p12 accounts_12 (cost=3D= 0.00..122486.55 rows=3D3525555 width=3D12) (actual time=3D32.199..14350.771 r= ows=3D8461332 loops=3D1) >> -> Parallel Seq Scan on accounts_p6 accounts_6 (cost=3D0= .00..120551.91 rows=3D3469891 width=3D12) (actual time=3D1.284..27367.522 ro= ws=3D8327739 loops=3D1) >> -> Parallel Seq Scan on accounts_p9 accounts_9 (cost=3D0= .00..120515.24 rows=3D3468824 width=3D12) (actual time=3D2.594..29812.536 ro= ws=3D8325177 loops=3D1) >> -> Parallel Seq Scan on accounts_p4 accounts_4 (cost=3D0= .00..120505.38 rows=3D3468538 width=3D12) (actual time=3D42.894..30691.460 r= ows=3D8324491 loops=3D1) >> -> Parallel Seq Scan on accounts_p11 accounts_11 (cost=3D= 0.00..120459.09 rows=3D3467209 width=3D12) (actual time=3D1.068..52911.011 r= ows=3D8321302 loops=3D1) >> -> Parallel Seq Scan on accounts_p2 accounts_2 (cost=3D0= .00..112390.67 rows=3D3234967 width=3D12) (actual time=3D0.728..28041.580 ro= ws=3D7763921 loops=3D1) >> -> Hash (cost=3D1.10..1.10 rows=3D10 width=3D4) (actual time=3D= 0.261..0.265 rows=3D6 loops=3D2) >> Buckets: 1024 Batches: 1 Memory Usage: 9kB >> -> Seq Scan on t2 (cost=3D0.00..1.10 rows=3D10 width=3D4= ) (actual time=3D0.253..0.256 rows=3D6 loops=3D2) >> Planning Time: 1.490 ms >> Execution Time: 211741.385 ms >> (23 rows) >>=20 >>=20 >> -- Forcing pruning using hints >> postgres=3D> 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=3D1.56..53359847.63 rows=3D2767123 width=3D8) (actual= time=3D9.253..103956.379 rows=3D1664742 loops=3D1) >> -> HashAggregate (cost=3D1.12..1.23 rows=3D10 width=3D4) (actual tim= e=3D0.370..59.714 rows=3D6 loops=3D1) >> Group Key: t2.dt_col >> Batches: 1 Memory Usage: 24kB >> -> Seq Scan on t2 (cost=3D0.00..1.10 rows=3D10 width=3D4) (act= ual time=3D0.359..0.362 rows=3D6 loops=3D1) >> -> Append (cost=3D0.43..5302777.61 rows=3D3320703 width=3D12) (actua= l time=3D3.113..17280.161 rows=3D277457 loops=3D6) >> -> Index Scan using accounts_p1_pkey on accounts_p1 accounts_1 = (cost=3D0.43..434635.82 rows=3D273011 width=3D12) (actual time=3D5.202..171= 25.276 rows=3D276971 loops=3D1) >> Index Cond: (transaction_date =3D t2.dt_col) >> -> Index Scan using accounts_p2_pkey on accounts_p2 accounts_2 = (cost=3D0.43..441097.44 rows=3D277283 width=3D12) (actual time=3D0.718..146= 71.096 rows=3D276634 loops=3D1) >> Index Cond: (transaction_date =3D t2.dt_col) >> -> Index Scan using accounts_p3_pkey on accounts_p3 accounts_3 = (cost=3D0.43..441666.94 rows=3D277392 width=3D12) (actual time=3D1.368..158= 26.853 rows=3D278390 loops=3D1) >> Index Cond: (transaction_date =3D t2.dt_col) >> -> Index Scan using accounts_p4_pkey on accounts_p4 accounts_4 = (cost=3D0.43..441689.70 rows=3D277483 width=3D12) (never executed) >> Index Cond: (transaction_date =3D t2.dt_col) >> -> Index Scan using accounts_p5_pkey on accounts_p5 accounts_5 = (cost=3D0.43..441883.26 rows=3D277546 width=3D12) (never executed) >> Index Cond: (transaction_date =3D t2.dt_col) >> -> Index Scan using accounts_p6_pkey on accounts_p6 accounts_6 = (cost=3D0.43..441857.26 rows=3D277591 width=3D12) (actual time=3D3.678..186= 25.085 rows=3D277582 loops=3D3) >> Index Cond: (transaction_date =3D t2.dt_col) >> -> Index Scan using accounts_p7_pkey on accounts_p7 accounts_7 = (cost=3D0.43..441837.57 rows=3D277503 width=3D12) (never executed) >> Index Cond: (transaction_date =3D t2.dt_col) >> -> Index Scan using accounts_p8_pkey on accounts_p8 accounts_8 = (cost=3D0.43..441843.95 rows=3D277511 width=3D12) (never executed) >> Index Cond: (transaction_date =3D t2.dt_col) >> -> Index Scan using accounts_p9_pkey on accounts_p9 accounts_9 = (cost=3D0.43..441711.03 rows=3D277506 width=3D12) (never executed) >> Index Cond: (transaction_date =3D t2.dt_col) >> -> Index Scan using accounts_p10_pkey on accounts_p10 accounts_= 10 (cost=3D0.43..441918.96 rows=3D277554 width=3D12) (never executed) >> Index Cond: (transaction_date =3D t2.dt_col) >> -> Index Scan using accounts_p11_pkey on accounts_p11 accounts_= 11 (cost=3D0.43..441501.86 rows=3D277377 width=3D12) (never executed) >> Index Cond: (transaction_date =3D t2.dt_col) >> -> Index Scan using accounts_p12_pkey on accounts_p12 accounts_= 12 (cost=3D0.43..434530.31 rows=3D272946 width=3D12) (never executed) >> Index Cond: (transaction_date =3D t2.dt_col) >> Planning Time: 1.147 ms >> Execution Time: 104040.968 ms >> (32 rows) >>=20 >> Any help/explanation would be appreciated. >> Also, how could I contribute to get this partition pruning to work? >>=20 >> Kindest regards, >> -- >> Lauro Ojeda --Apple-Mail-05BE7AD0-6817-466F-B0AB-9A8C149AC4BD Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Never mind my message. I misread it and mis= sed the plan with no hints.


Micha=C5=82 

<= blockquote type=3D"cite">On 25 Sep 2025, at 22:10, Micha=C5=82 K=C5=82eczek &= lt;michal@kleczek.org> wrote:

=EF=BB=BF
Hi,

Partition pru= ning is happening: pruned nodes are marked as =E2=80=9Cnever executed=E2=80=9D= .
It is just that pruning is performed not by the planner but by t= he executor in this case.

=E2=80=94

Micha=C5=82 

On 25 Sep 2025, at 21:49, Lauro O= jeda <lauro.ojeda@gmail.com> wrote:

=EF=BB=BF
Hi super-e= xperts,
I am trying to solve a mystery for a customer where t= hey had some very large heap tables (500GB+ each) on their PG 13 database wh= ich was bringing them loads of performance problems. 
Apart f= rom the regular server tuning efforts, I suggested them to upgrade it to at l= east Postgresql 15 and partition these large tables. They've accepted it and= they have applied my suggestions.
However, partition  prunin= g is not happening. Instead, PG is doing sequential scans against all partit= ions. I thought it was PG 15 behaviour, so I tested the same on PG 17 and co= mpiled PG18rc1, getting the same results. I read on the release notes of PG1= 8 that partition pruning had many fixes but it seems this one is not in plac= e.
The only way I found to make pruning work is to force index_sca= n using pg_hint_plan, but I wanted to influence the planner to decide it by i= tself rather than relying on hints. What's the reason for this misbehaving a= nd what could I do to overcome it?

Relevant para= meters/info:
DB cluster hosted on Azure Flex Server
= random_page_cost=3D1.1;
plan_cache_mode=3Dforce_custom_plan; = # the so called magic-bullet for pruning
default_stati= stics_target=3D1000; # better stats to the planner
geqo_eff= ort=3D7; # I wanted the planner to "think" better and do pruning=
enable_partition_pruning=3Don;

= Test case
-- Tables def
p= ostgres=3D> \d+ accounts
            &nb= sp;                     &n= bsp;                    Pa= rtitioned table "public.accounts"
      Column   &nbs= p;  |  Type   | Collation | Nullable |       &= nbsp;        Default           &= nbsp;    | Storage  | Compression | Stats target | Descriptio= n
------------------+---------+-----------+----------+-------------------= --------------------+----------+-------------+--------------+------------- aid              | integer | &nbs= p;         | not null | nextval('accounts_aid_seq'::regc= lass) | plain    |             | &nb= sp;            |
 bid     &n= bsp;        | integer |         &nbs= p; |          |           &= nbsp;                     &= nbsp;     | plain    |          = ;   |              |
 abalan= ce         | integer |          = ; |          |           &= nbsp;                     &= nbsp;     | plain    |          = ;   |              |
 filler=           | text    |     &nbs= p;     |          |       &= nbsp;                     &= nbsp;         | extended |         &= nbsp;   |              |
 tr= ansaction_date | date    |           | no= t null |                   &nbs= p;                   | plain &n= bsp;  |             |      = ;        |
Partition key: RANGE (transaction_date)Indexes:
    "accounts_pkey" PRIMARY KEY, btree (transaction_d= ate, aid)
Partitions: accounts_p1 FOR VALUES FROM ('2025-01-01') TO ('202= 5-02-01'),
            accounts_p10 FOR VAL= UES FROM ('2025-10-01') TO ('2025-11-01'),
        &n= bsp;   accounts_p11 FOR VALUES FROM ('2025-11-01') TO ('2025-12-01'),            accounts_p12 FOR VALUES FROM ('2= 025-12-01') TO ('2026-01-01'),
            a= ccounts_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 V= ALUES FROM ('2025-04-01') TO ('2025-05-01'),
        &= nbsp;   accounts_p5 FOR VALUES FROM ('2025-05-01') TO ('2025-06-01'),            accounts_p6 FOR VALUES FROM ('20= 25-06-01') TO ('2025-07-01'),
            a= ccounts_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 V= ALUES FROM ('2025-09-01') TO ('2025-10-01')

postgres=3D> \d+ t2                     = ;                     Tabl= e "public.t2"
 Column | Type | Collation | Nullable | Default | Stor= age | Compression | Stats target | Description
--------+------+----------= -+----------+---------+---------+-------------+--------------+-------------<= br> dt_col | date |           |     &= nbsp;    |         | plain   |   &nb= sp;         |             &= nbsp;|
Access method: heap

-- Executing test<= /b>
-- Regular execution
postg= res=3D> explain analyze
select aid, abalance
from accounts
where= transaction_date in (select dt_col from t2);
       =                      = ;                     &nbs= p;                     &nb= sp;    QUERY PLAN
---------------------------------------------= ----------------------------------------------------------------------------= -------------------------------------------
 Gather  (cost=3D10= 01.23..2073502.05 rows=3D2767123 width=3D8) (actual time=3D3.574..211619.991= rows=3D1664742 loops=3D1)
   Workers Planned: 2
  &nbs= p;Workers Launched: 1
   ->  Hash Semi Join  (cost= =3D1.23..1795789.75 rows=3D1152968 width=3D8) (actual time=3D77575.241..2036= 29.828 rows=3D832371 loops=3D2)
         Hash Co= nd: (accounts.transaction_date =3D t2.dt_col)
       =  ->  Parallel Append  (cost=3D0.00..1672493.00 rows=3D420= 83334 width=3D12) (actual time=3D0.645..197115.678 rows=3D50500000 loops=3D2= )
               ->  Para= llel Seq Scan on accounts_p10 accounts_10  (cost=3D0.00..124553.77 rows= =3D3585078 width=3D12) (actual time=3D0.560..30193.541 rows=3D8604186 loops=3D= 1)
               ->  Par= allel Seq Scan on accounts_p5 accounts_5  (cost=3D0.00..124550.68 rows=3D= 3584968 width=3D12) (actual time=3D56.415..54334.025 rows=3D8603923 loops=3D= 1)
               ->  Par= allel Seq Scan on accounts_p8 accounts_8  (cost=3D0.00..124535.20 rows=3D= 3584520 width=3D12) (actual time=3D1.738..31555.264 rows=3D8602847 loops=3D1= )
               ->  Para= llel Seq Scan on accounts_p7 accounts_7  (cost=3D0.00..124531.08 rows=3D= 3584408 width=3D12) (actual time=3D2.357..29998.452 rows=3D8602579 loops=3D1= )
               ->  Para= llel Seq Scan on accounts_p3 accounts_3  (cost=3D0.00..124481.85 rows=3D= 3582985 width=3D12) (actual time=3D2.525..24765.109 rows=3D8599165 loops=3D1= )
               ->  Para= llel Seq Scan on accounts_p1 accounts_1  (cost=3D0.00..122514.91 rows=3D= 3526391 width=3D12) (actual time=3D2.675..13909.461 rows=3D4231669 loops=3D2= )
               ->  Para= llel Seq Scan on accounts_p12 accounts_12  (cost=3D0.00..122486.55 rows= =3D3525555 width=3D12) (actual time=3D32.199..14350.771 rows=3D8461332 loops= =3D1)
               ->  = Parallel Seq Scan on accounts_p6 accounts_6  (cost=3D0.00..120551.91 ro= ws=3D3469891 width=3D12) (actual time=3D1.284..27367.522 rows=3D8327739 loop= s=3D1)
               ->  = ;Parallel Seq Scan on accounts_p9 accounts_9  (cost=3D0.00..120515.24 r= ows=3D3468824 width=3D12) (actual time=3D2.594..29812.536 rows=3D8325177 loo= ps=3D1)
               -> &nbs= p;Parallel Seq Scan on accounts_p4 accounts_4  (cost=3D0.00..120505.38 r= ows=3D3468538 width=3D12) (actual time=3D42.894..30691.460 rows=3D8324491 lo= ops=3D1)
               -> &nb= sp;Parallel Seq Scan on accounts_p11 accounts_11  (cost=3D0.00..120459.= 09 rows=3D3467209 width=3D12) (actual time=3D1.068..52911.011 rows=3D8321302= loops=3D1)
               -> &= nbsp;Parallel Seq Scan on accounts_p2 accounts_2  (cost=3D0.00..112390.= 67 rows=3D3234967 width=3D12) (actual time=3D0.728..28041.580 rows=3D7763921= loops=3D1)
         ->  Hash  (cos= t=3D1.10..1.10 rows=3D10 width=3D4) (actual time=3D0.261..0.265 rows=3D6 loo= ps=3D2)
               Buckets: 1= 024  Batches: 1  Memory Usage: 9kB
        &= nbsp;      ->  Seq Scan on t2  (cost=3D0.00..1.1= 0 rows=3D10 width=3D4) (actual time=3D0.253..0.256 rows=3D6 loops=3D2)
&n= bsp;Planning Time: 1.490 ms
 Execution Time: 211741.385 ms
(23 ro= ws)


-- Forcing pruning using hints=
postgres=3D> explain analyze
/*+ Inde= xScan(accounts accounts_pkey) */
select aid, abalance
from account= s
where transaction_date in (select dt_col from t2);
    &nb= sp;                     &n= bsp;                     &= nbsp;                     &= nbsp;        QUERY PLAN
-----------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------
 Ne= sted Loop  (cost=3D1.56..53359847.63 rows=3D2767123 width=3D8) (actual t= ime=3D9.253..103956.379 rows=3D1664742 loops=3D1)
   -> &nbs= p;HashAggregate  (cost=3D1.12..1.23 rows=3D10 width=3D4) (actual time=3D= 0.370..59.714 rows=3D6 loops=3D1)
         Group= Key: t2.dt_col
         Batches: 1  Memory= Usage: 24kB
         ->  Seq Scan on t2=  (cost=3D0.00..1.10 rows=3D10 width=3D4) (actual time=3D0.359..0.362 r= ows=3D6 loops=3D1)
   ->  Append  (cost=3D0.43..53= 02777.61 rows=3D3320703 width=3D12) (actual time=3D3.113..17280.161 rows=3D2= 77457 loops=3D6)
         ->  Index Scan= using accounts_p1_pkey on accounts_p1 accounts_1  (cost=3D0.43..434635= .82 rows=3D273011 width=3D12) (actual time=3D5.202..17125.276 rows=3D276971 l= oops=3D1)
               Index Co= nd: (transaction_date =3D t2.dt_col)
         -&= gt;  Index Scan using accounts_p2_pkey on accounts_p2 accounts_2  = (cost=3D0.43..441097.44 rows=3D277283 width=3D12) (actual time=3D0.718..1467= 1.096 rows=3D276634 loops=3D1)
            &= nbsp;  Index Cond: (transaction_date =3D t2.dt_col)
    &n= bsp;    ->  Index Scan using accounts_p3_pkey on accounts_= p3 accounts_3  (cost=3D0.43..441666.94 rows=3D277392 width=3D12) (actua= l time=3D1.368..15826.853 rows=3D278390 loops=3D1)
      &= nbsp;        Index Cond: (transaction_date =3D t2.dt_col= )
         ->  Index Scan using accounts= _p4_pkey on accounts_p4 accounts_4  (cost=3D0.43..441689.70 rows=3D2774= 83 width=3D12) (never executed)
           =    Index Cond: (transaction_date =3D t2.dt_col)
    &= nbsp;    ->  Index Scan using accounts_p5_pkey on accounts= _p5 accounts_5  (cost=3D0.43..441883.26 rows=3D277546 width=3D12) (neve= r executed)
               Index C= ond: (transaction_date =3D t2.dt_col)
         -= >  Index Scan using accounts_p6_pkey on accounts_p6 accounts_6  = ;(cost=3D0.43..441857.26 rows=3D277591 width=3D12) (actual time=3D3.678..186= 25.085 rows=3D277582 loops=3D3)
           =    Index Cond: (transaction_date =3D t2.dt_col)
    &= nbsp;    ->  Index Scan using accounts_p7_pkey on accounts= _p7 accounts_7  (cost=3D0.43..441837.57 rows=3D277503 width=3D12) (neve= r executed)
               Index C= ond: (transaction_date =3D t2.dt_col)
         -= >  Index Scan using accounts_p8_pkey on accounts_p8 accounts_8  = ;(cost=3D0.43..441843.95 rows=3D277511 width=3D12) (never executed)
 = ;              Index Cond: (transaction_d= ate =3D t2.dt_col)
         ->  Index Sc= an using accounts_p9_pkey on accounts_p9 accounts_9  (cost=3D0.43..4417= 11.03 rows=3D277506 width=3D12) (never executed)
      &nb= sp;        Index Cond: (transaction_date =3D t2.dt_col)<= br>         ->  Index Scan using accounts_p= 10_pkey on accounts_p10 accounts_10  (cost=3D0.43..441918.96 rows=3D277= 554 width=3D12) (never executed)
           = ;    Index Cond: (transaction_date =3D t2.dt_col)
   =      ->  Index Scan using accounts_p11_pkey on accou= nts_p11 accounts_11  (cost=3D0.43..441501.86 rows=3D277377 width=3D12) (= never executed)
               In= dex Cond: (transaction_date =3D t2.dt_col)
        &n= bsp;->  Index Scan using accounts_p12_pkey on accounts_p12 accounts_= 12  (cost=3D0.43..434530.31 rows=3D272946 width=3D12) (never executed)<= br>               Index Cond: (trans= action_date =3D t2.dt_col)
 Planning Time: 1.147 ms
 Executi= on 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 regard= s,
--
L= auro Ojeda
= --Apple-Mail-05BE7AD0-6817-466F-B0AB-9A8C149AC4BD--