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 1v1sIX-00Dacf-CF for pgsql-performance@arkaria.postgresql.org; Thu, 25 Sep 2025 20:10: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 1v1sIW-005c1G-2D for pgsql-performance@arkaria.postgresql.org; Thu, 25 Sep 2025 20:10: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 1v1sIV-005c0w-Km for pgsql-performance@lists.postgresql.org; Thu, 25 Sep 2025 20:10:35 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1sIT-0005Jd-01 for pgsql-performance@postgresql.org; Thu, 25 Sep 2025 20:10:35 +0000 Received: by mail-ej1-x62d.google.com with SMTP id a640c23a62f3a-afcb7ae6ed0so228276666b.3 for ; Thu, 25 Sep 2025 13:10:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=kleczek.org; s=google; t=1758831031; x=1759435831; 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=KG0NbanPlY1z9UH2pUhoVhOYMn4EKE2oWvqvVBx/Woo=; b=gH330C1uyhLK/mUKCtsH0PMR4ikhYbxjdHn+QGiyuRyUVzxO1qdvOVBk3Dtmi7MYA4 8QQBUjrTdV5Q7XsrSVaKlnoVMJlrY4+Hfl9z0aYTfo7NJgPQi8+SrZOpUFHBdqbk/p5T 96yBGm8RrHMC2t3+IUUHGnHkCsgYYB1iSh5nE= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758831031; x=1759435831; 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=KG0NbanPlY1z9UH2pUhoVhOYMn4EKE2oWvqvVBx/Woo=; b=nOXbPJR1YqMX9i+yzI4WKJords1BEM2RNoRmAwvN1x7NlfU0rEjVAA5TOOUx+KgRXl mqYCmLfFPWz4JTacyg4CfJu4XJfSFbQb00zYtZaW0gEil9y0ha53KeFm0YdRTsNjSWlB lL+lhbFAgyqzuutEhYsgzEtEAjbYWJPhtgrwhzYhXensj8TFfOxml/aiWhqCg+rTRDx7 n9L1OkUxWFrj0b+3DhfyDWEXH6nMqRUvM0Jn6QIZCjoFD+aJAOCkCrCWRsMZeoKexGNu XuQkhUnhkeMRWP4hYu6+yNuTmFIoaLNxzxXVfpNxm/eA3Lm/VO8mYRNCo+f0wWVyVIcL AseQ== X-Gm-Message-State: AOJu0YyJLDEAWNcwKw+ixUWzG7q19GnL0MyJTlACmy+VOl8feM4IzB+0 yafe4vws+b+01tKufuJ9mDLF58XV4Aqv3YV/NcdOZeWYNuHXtGhgupita/D6xdZlaxNjp1EDy0V YpgRj X-Gm-Gg: ASbGncvyubkSXutikrScVbNUmXEn1rdtrdXyvSBnrPzYtT+v//kISXAA7YB8d+wqSPP 4LMYUCsysrvlMy5yhQf6G5pBHUxe7pQsSe9cSpkJyjOFTblRRsJQIo5Th14GP4cyzQoFsA43wUq OJL1yr7tcK6BxsWmKK8SJm847n/rzjXL6E8KmIGdAbSA5ZpwJpKFelt1jTBdRvRgrXD4IXxSYQf IHlZHhcC5JlZ91VpykMfjwH2h5Lu38nmXa3tFsvsteB5zEPiBskLoMorxWGhkjCsiS1mwACgtye jAI+XetNY+xP3Hcem6AIZsGyoC2c6fvFmCD29XCG2OoZdSTf3jUH5sOnE84+xoMuGJ6lP/mgTtK 3+fRNP6Te6vDYRnQsLjTHXj3A/rqToHqA/Jtu6H8BwAjMP9fEfMmCZr1ZO+i9 X-Google-Smtp-Source: AGHT+IEJ/ydcbycMq55sh/QSnMb+aulVL4yKsdbKGOXPznpIQftJmb8+T9ejKT8NYPbfnjix0HGizA== X-Received: by 2002:a17:907:72ce:b0:b04:ae68:afdf with SMTP id a640c23a62f3a-b34b9782c8emr543408966b.28.1758831031265; Thu, 25 Sep 2025 13:10:31 -0700 (PDT) Received: from smtpclient.apple (b15.kleczek.org. [185.20.172.182]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-b3545a9c3e1sm224405366b.105.2025.09.25.13.10.30 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 25 Sep 2025 13:10:30 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-8716B0E1-8126-4D98-8CAC-96C9ACA083D2 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:10:10 +0200 Message-Id: <811FB044-CDCA-49F6-9327-D95F046FE5B0@kleczek.org> References: Cc: pgsql-performance@postgresql.org In-Reply-To: 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-8716B0E1-8126-4D98-8CAC-96C9ACA083D2 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi, Partition pruning is happening: pruned nodes are marked as =E2=80=9Cnever ex= ecuted=E2=80=9D. It is just that pruning is performed not by the planner but by the executor i= n this case. =E2=80=94 Micha=C5=82=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 lar= ge heap tables (500GB+ each) on their PG 13 database which was bringing them= loads of performance problems.=20 > Apart from the regular server tuning efforts, I suggested them to upgrade i= t to at least Postgresql 15 and partition these large tables. They've accept= ed it and they have applied my suggestions. > However, partition pruning is not happening. Instead, PG is doing sequent= ial scans against all partitions. I thought it was PG 15 behaviour, so I tes= ted 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 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 rath= er than relying on hints. What's the reason for this misbehaving and what co= uld 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 prun= ing > 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 | Defaul= t | 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') >=20 > postgres=3D> \d+ t2 > Table "public.t2" > Column | Type | Collation | Nullable | Default | Storage | Compression | S= tats 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 tim= e=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 wid= th=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 ro= ws=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 row= s=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 row= s=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 row= s=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 row= s=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 row= s=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 row= s=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 ro= ws=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 row= s=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 t= ime=3D9.253..103956.379 rows=3D1664742 loops=3D1) > -> HashAggregate (cost=3D1.12..1.23 rows=3D10 width=3D4) (actual time= =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) (actu= al time=3D0.359..0.362 rows=3D6 loops=3D1) > -> Append (cost=3D0.43..5302777.61 rows=3D3320703 width=3D12) (actual= 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..17125= .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..14671= .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..15826= .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..18625= .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_1= 0 (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_1= 1 (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_1= 2 (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-8716B0E1-8126-4D98-8CAC-96C9ACA083D2 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hi,

Partition= pruning 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 bu= t by the executor in this case.

=E2=80=94

Micha=C5=82 

On 25 Sep 2025, at 21:49, L= auro Ojeda <lauro.ojeda@gmail.com> wrote:

=EF=BB=BF
Hi s= uper-experts,
I am trying to solve a mystery for a customer w= here they had some very large heap tables (500GB+ each) on their PG 13 datab= ase which was bringing them loads of performance problems. 
A= part from the regular server tuning efforts, I suggested them to upgrade it t= o at least Postgresql 15 and partition these large tables. They've accepted i= t and they have applied my suggestions.
However, partition  p= runing is not happening. Instead, PG is doing sequential scans against all p= artitions. I thought it was PG 15 behaviour, so I tested the same on PG 17 a= nd compiled PG18rc1, getting the same results. I read on the release notes o= f 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 inde= x_scan using pg_hint_plan, but I wanted to influence the planner to decide i= t by itself rather than relying on hints. What's the reason for this misbeha= ving and what could I do to overcome it?

Relevan= t 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 pruning
default= _statistics_target=3D1000; # better stats to the planner
ge= qo_effort=3D7; # I wanted the planner to "think" better and do pruni= ng
enable_partition_pruning=3Don;

Test case
-- Tables def
postgres=3D> \d+ accounts
          &nbs= p;                     &nb= sp;                     &n= bsp;Partitioned table "public.accounts"
      Column  = ;    |  Type   | Collation | Nullable |     &n= bsp;          Default         &= nbsp;      | Storage  | Compression | Stats target | Des= cription
------------------+---------+-----------+----------+------------= ---------------------------+----------+-------------+--------------+--------= -----
 aid              | integer= |           | not null | nextval('accounts_aid_seq= '::regclass) | plain    |            = ; |              |
 bid   &n= bsp;          | integer |       &nbs= p;   |          |         &= nbsp;                     &= nbsp;       | plain    |        = ;     |              |
 = ;abalance         | integer |        = ;   |          |         &= nbsp;                     &= nbsp;       | plain    |        = ;     |              |
 = ;filler           | text    |   &nbs= p;       |          |     &= nbsp;                     &= nbsp;           | extended |       &= nbsp;     |              |
&= nbsp;transaction_date | date    |         &nbs= p; | not null |                 &nbs= p;                     | p= lain    |             |    = ;          |
Partition key: RANGE (transaction_d= ate)
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (transa= ction_date, aid)
Partitions: accounts_p1 FOR VALUES FROM ('2025-01-01') T= O ('2025-02-01'),
            accounts_p10 = FOR VALUES 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 FR= OM ('2025-12-01') TO ('2026-01-01'),
          &= nbsp; accounts_p2 FOR VALUES FROM ('2025-02-01') TO ('2025-03-01'),
 = ;           accounts_p3 FOR VALUES FROM ('2025-03-0= 1') 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'),
&n= bsp;           accounts_p8 FOR VALUES FROM ('2025-0= 8-01') TO ('2025-09-01'),
            accou= nts_p9 FOR VALUES FROM ('2025-09-01') TO ('2025-10-01')

postgres=3D&g= t; \d+ t2
                  &= nbsp;                     &= nbsp; Table "public.t2"
 Column | Type | Collation | Nullable | Defa= ult | Storage | Compression | Stats target | Description
--------+------+= -----------+----------+---------+---------+-------------+--------------+----= ---------
 dt_col | date |           | &nbs= p;        |         | plain   |=             |         &nb= sp;    |
Access method: heap

-- Exe= cuting test
-- Regular execution
postgres=3D> explain analyze
select aid, abalance
from accou= nts
where transaction_date in (select dt_col from t2);
    &= nbsp;                     &= nbsp;                     &= nbsp;                     &= nbsp;      QUERY PLAN
------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------
 Gather  (= cost=3D1001.23..2073502.05 rows=3D2767123 width=3D8) (actual time=3D3.574..2= 11619.991 rows=3D1664742 loops=3D1)
   Workers Planned: 2
&n= bsp;  Workers Launched: 1
   ->  Hash Semi Join &n= bsp;(cost=3D1.23..1795789.75 rows=3D1152968 width=3D8) (actual time=3D77575.= 241..203629.828 rows=3D832371 loops=3D2)
        &nbs= p;Hash Cond: (accounts.transaction_date =3D t2.dt_col)
    &nbs= p;    ->  Parallel Append  (cost=3D0.00..1672493.00 r= ows=3D42083334 width=3D12) (actual time=3D0.645..197115.678 rows=3D50500000 l= oops=3D2)
               -> &n= bsp;Parallel Seq Scan on accounts_p10 accounts_10  (cost=3D0.00..124553= .77 rows=3D3585078 width=3D12) (actual time=3D0.560..30193.541 rows=3D860418= 6 loops=3D1)
               ->=  Parallel Seq Scan on accounts_p5 accounts_5  (cost=3D0.00..12455= 0.68 rows=3D3584968 width=3D12) (actual time=3D56.415..54334.025 rows=3D8603= 923 loops=3D1)
               -&g= t;  Parallel Seq Scan on accounts_p8 accounts_8  (cost=3D0.00..124= 535.20 rows=3D3584520 width=3D12) (actual time=3D1.738..31555.264 rows=3D860= 2847 loops=3D1)
               -&= gt;  Parallel Seq Scan on accounts_p7 accounts_7  (cost=3D0.00..12= 4531.08 rows=3D3584408 width=3D12) (actual time=3D2.357..29998.452 rows=3D86= 02579 loops=3D1)
               -= >  Parallel Seq Scan on accounts_p3 accounts_3  (cost=3D0.00..1= 24481.85 rows=3D3582985 width=3D12) (actual time=3D2.525..24765.109 rows=3D8= 599165 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 rows=3D= 4231669 loops=3D2)
               = ;->  Parallel Seq Scan on accounts_p12 accounts_12  (cost=3D0.0= 0..122486.55 rows=3D3525555 width=3D12) (actual time=3D32.199..14350.771 row= s=3D8461332 loops=3D1)
              &= nbsp;->  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)
              &= nbsp;->  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)
              &= nbsp;->  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= =3D0.00..120459.09 rows=3D3467209 width=3D12) (actual time=3D1.068..52911.01= 1 rows=3D8321302 loops=3D1)
            &nb= sp;  ->  Parallel Seq Scan on accounts_p2 accounts_2  (cos= t=3D0.00..112390.67 rows=3D3234967 width=3D12) (actual time=3D0.728..28041.5= 80 rows=3D7763921 loops=3D1)
         -> &nbs= p;Hash  (cost=3D1.10..1.10 rows=3D10 width=3D4) (actual time=3D0.261..0= .265 rows=3D6 loops=3D2)
             =  Buckets: 1024  Batches: 1  Memory Usage: 9kB
  &nbs= p;            ->  Seq Scan on t2  = ;(cost=3D0.00..1.10 rows=3D10 width=3D4) (actual time=3D0.253..0.256 rows=3D= 6 loops=3D2)
 Planning Time: 1.490 ms
 Execution Time: 21174= 1.385 ms
(23 rows)


-- Forcing pruning u= sing hints
postgres=3D> explain anal= yze
/*+ IndexScan(accounts accounts_pkey) */
select aid, abalan= ce
from accounts
where transaction_date in (select dt_col from t2);                     = ;                     &nbs= p;                     &nb= sp;              QUERY PLAN
----------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= -------
 Nested Loop  (cost=3D1.56..53359847.63 rows=3D2767123 w= idth=3D8) (actual time=3D9.253..103956.379 rows=3D1664742 loops=3D1)
&nbs= p;  ->  HashAggregate  (cost=3D1.12..1.23 rows=3D10 width=3D= 4) (actual time=3D0.370..59.714 rows=3D6 loops=3D1)
      &= nbsp;  Group Key: t2.dt_col
         Batche= s: 1  Memory Usage: 24kB
         -> &nb= sp;Seq Scan on t2  (cost=3D0.00..1.10 rows=3D10 width=3D4) (actual time= =3D0.359..0.362 rows=3D6 loops=3D1)
   ->  Append  = ;(cost=3D0.43..5302777.61 rows=3D3320703 width=3D12) (actual time=3D3.113..1= 7280.161 rows=3D277457 loops=3D6)
         ->=  Index Scan using accounts_p1_pkey on accounts_p1 accounts_1  (co= st=3D0.43..434635.82 rows=3D273011 width=3D12) (actual time=3D5.202..17125.2= 76 rows=3D276971 loops=3D1)
            &nb= sp;  Index Cond: (transaction_date =3D t2.dt_col)
    &nbs= p;    ->  Index Scan using accounts_p2_pkey on accounts_p2= accounts_2  (cost=3D0.43..441097.44 rows=3D277283 width=3D12) (actual t= ime=3D0.718..14671.096 rows=3D276634 loops=3D1)
      &nbs= p;        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 w= idth=3D12) (actual time=3D1.368..15826.853 rows=3D278390 loops=3D1)
 = ;              Index Cond: (transaction_d= ate =3D t2.dt_col)
         ->  Index Sc= an using accounts_p4_pkey on accounts_p4 accounts_4  (cost=3D0.43..4416= 89.70 rows=3D277483 width=3D12) (never executed)
      &nb= sp;        Index Cond: (transaction_date =3D t2.dt_col)<= br>         ->  Index Scan using accounts_p= 5_pkey on accounts_p5 accounts_5  (cost=3D0.43..441883.26 rows=3D277546= width=3D12) (never executed)
            &= nbsp;  Index Cond: (transaction_date =3D t2.dt_col)
    &n= bsp;    ->  Index Scan using accounts_p6_pkey on accounts_= p6 accounts_6  (cost=3D0.43..441857.26 rows=3D277591 width=3D12) (actua= l time=3D3.678..18625.085 rows=3D277582 loops=3D3)
      &= nbsp;        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=3D2775= 03 width=3D12) (never executed)
           =    Index Cond: (transaction_date =3D t2.dt_col)
    &= nbsp;    ->  Index Scan using accounts_p8_pkey on accounts= _p8 accounts_8  (cost=3D0.43..441843.95 rows=3D277511 width=3D12) (neve= r executed)
               Index C= ond: (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_d= ate =3D t2.dt_col)
         ->  Index Sc= an using accounts_p10_pkey on accounts_p10 accounts_10  (cost=3D0.43..4= 41918.96 rows=3D277554 width=3D12) (never executed)
      &= nbsp;        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=3D2= 77377 width=3D12) (never executed)
          &nb= sp;    Index Cond: (transaction_date =3D t2.dt_col)
  &nbs= p;      ->  Index Scan using accounts_p12_pkey on acc= ounts_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 m= s
 Execution Time: 104040.968 ms
(32 rows)

<= /div>
Any help/explanation would be appreciated.
Also, how cou= ld I contribute to get this partition pruning to work?

<= div>Kindest regards,
-- <= br>
Lauro Ojeda
<= /div>
= --Apple-Mail-8716B0E1-8126-4D98-8CAC-96C9ACA083D2--