MIME-Version: 1.0
References: 
 <CAG3nGZ-Pi=ExcSiRpCmjJWpqT+Ugd4p+xHC-K7M33kvAh4xZNQ@mail.gmail.com>
 <CAApHDvrydoBmh5MJQUMpvqaxNOioB2U-jTGahQevYjT_YFfDCw@mail.gmail.com>
In-Reply-To: 
 <CAApHDvrydoBmh5MJQUMpvqaxNOioB2U-jTGahQevYjT_YFfDCw@mail.gmail.com>
From: Lauro Ojeda <lauro.ojeda@gmail.com>
Date: Mon, 29 Sep 2025 15:49:31 +0200
Message-ID: 
 <CAG3nGZ-PpRarYRqbMzLZoVkma+ifSmNjmuPSL0tT3_opMZ1Dxg@mail.gmail.com>
Subject: Re: Partition pruning is not happening (even in PG18)
To: David Rowley <dgrowleyml@gmail.com>
Cc: Pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000099cb5b063ff0e804"
Archived-At: 
 <https://www.postgresql.org/message-id/CAG3nGZ-PpRarYRqbMzLZoVkma%2BifSmNjmuPSL0tT3_opMZ1Dxg%40mail.gmail.com>
Precedence: bulk

--00000000000099cb5b063ff0e804
Content-Type: text/plain; charset="UTF-8"

Hi David,
Thank you for your nice reply.

I have the impression there is something heavily penalizing the usage of
partition pruning. While trying to go a bit further, I realized that
partition pruning is not occurring because the planner gives the cost of
seq-scanning all partitions to just over 2M units, but the query takes over
3 minutes. When forcing the usage of PK index via hinting (which down the
line forces the partition pruning to happen), the cost sky-rocket to 53M
units (however scanning 4 partitions out of 12). Even though with much
higher cost, the query launches less workers and completes in a third (or
less) of the time of all partition scans.

My observation is that the cost of seq-scanning each partition is estimated
in ~124k units, but index-scanning each partition is 440k (say, 3x higher).
Therefore, scanning 4 partitions, the total cost should be ~1.7M units,
which is below the 2M units from the cost of all partition scanning, thus
the planner should prefer the indexed path instead. However when analyzing
the hinted query (doing index scan and thus partition pruning) we can note
that the cost estimated to each partition scan was added to the final cost,
even though 8 out of 12 partitions were not scanned (never executed).

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=2.299..111346.649 rows=1664742 loops=1)
   ->  HashAggregate  (cost=1.12..1.23 rows=10 width=4) (actual
time=1.060..40.562 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=1.048..1.050 rows=6 loops=1)
   ->  Append  (cost=0.43..5302777.61 rows=3320703 width=12) (actual
time=1.554..18514.181 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=2.214..17627.113
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=2.185..16280.020
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=0.751..25992.220
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=1.376..17001.777 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: 0.879 ms
 Execution Time: 111432.755 ms
(32 rows)

By looking into it, I have the impression there is a bug in the costing sum
in that situation, where the cost of the "never executed" partitions should
be deducted from the final cost estimation, which would make pruning to be
the preferred option in this case.

Are my assumptions correct?
Is there anything I could do to influence the planner to dismiss the cost
of "never executed" scans?

Kind regards,
Lauro Ojeda


On Thu, 25 Sept 2025 at 22:18, David Rowley <dgrowleyml@gmail.com> wrote:

> On Fri, 26 Sept 2025 at 07:49, Lauro Ojeda <lauro.ojeda@gmail.com> wrote:
> > 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?
>
> > Partition key: RANGE (transaction_date)
>
> > postgres=> explain analyze
> > select aid, abalance
> > from accounts
> > where transaction_date in (select dt_col from t2);
>
> The only partition pruning that exists in PostgreSQL that can prune
> for that query is for parameterised Nested Loop joins.  For Hash Join,
> it's been talked about, but this requires running the partition
> pruning code for every values that goes into the Hash Table and only
> scanning the unioned set of those partitions during the hash probe
> phase. The trouble with that is that it's very hard to know in advance
> if it'll be worth the extra effort. Putting a tuple into a hash table
> is quite cheap. Running the pruning code for a range partitioned table
> is likely to be a few times more costly than the hash insert
> (depending on how many partitions there are), so if the end result is
> that nothing was pruned, then that's quite a bit of extra effort for
> no gain.
>
> What we maybe could do better is reduce the cost of the Append scan
> when there's a run-time pruning object attached. This is a little
> tricky as we currently only build that object when creating the final
> plan.  To include that in the costs we'd need to move that to the Path
> generation phase so that we didn't accidentally reject Paths which
> could be cheaper than we first think.
>
> > Also, how could I contribute to get this partition pruning to work?
>
> The pgsql-hackers mailing list is where all the discussions about that
> happen. There is plenty of past discussions on these topics. One such
> (fairly) recent discussion is in [1]. There are plenty more, including
> some ideas from Robert Haas about how we might cost run-time partition
> pruning. That was likely around 2017-2018 range, so you might need to
> dig deep to find that.
>
> David
>
> [1]
> https://www.postgresql.org/message-id/flat/CAApHDvoC7n_oceb%3D8z%2BMY8sTgH4xa%2ByAwBxZ4Dxv8pwkT9bOcA%40mail.gmail.com#45314d3d01ef8ad1eebe72111989062c
>


-- 
*Lauro Ojeda*

--00000000000099cb5b063ff0e804
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi David,<div>Thank you for your nice reply.</div><div><br=
></div><div>I have the impression there is something heavily penalizing the=
 usage of partition pruning. While trying to go a bit further, I realized t=
hat partition pruning is not occurring=C2=A0because the planner gives the c=
ost of seq-scanning all partitions to just over 2M units, but the query tak=
es over 3 minutes. When forcing the usage of PK index via hinting (which do=
wn the line forces the partition pruning to happen), the cost sky-rocket to=
 53M units=C2=A0(however scanning 4 partitions out of 12).=C2=A0Even though=
 with much higher cost, the query launches less workers and completes in a =
third (or less) of the time of all partition scans.</div><div><br>My observ=
ation is that the cost of seq-scanning each partition is estimated in ~124k=
 units, but index-scanning each partition is 440k (say, 3x higher). Therefo=
re, scanning 4 partitions, the total cost should be ~1.7M units, which is b=
elow the 2M units from the cost of all partition scanning, thus the planner=
 should prefer the indexed path instead. However when analyzing the hinted=
=C2=A0query (doing index scan and thus partition pruning) we can note that =
the cost estimated to each partition scan was added to the final cost, even=
 though 8 out of 12 partitions were not scanned (never executed).</div><div=
><br><font face=3D"monospace">postgres=3D&gt; explain analyze<br>/*+ IndexS=
can(accounts accounts_pkey) */<br>select aid, abalance<br>from accounts<br>=
where transaction_date in (select dt_col from t2);<br>=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<br>-----------------------=
---------------------------------------------------------------------------=
-----------------------------------------------------------------------<br>=
=C2=A0Nested Loop =C2=A0(cost=3D1.56..<font color=3D"#ff0000">53359847.63 <=
/font>rows=3D2767123 width=3D8) (actual time=3D2.299..111346.649 rows=3D166=
4742 loops=3D1)<br>=C2=A0 =C2=A0-&gt; =C2=A0HashAggregate =C2=A0(cost=3D1.1=
2..1.23 rows=3D10 width=3D4) (actual time=3D1.060..40.562 rows=3D6 loops=3D=
1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Group Key: t2.dt_col<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0Batches: 1 =C2=A0Memory Usage: 24kB<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Seq Scan on t2 =C2=A0(cost=3D0.00..1.10=
 rows=3D10 width=3D4) (actual time=3D1.048..1.050 rows=3D6 loops=3D1)<br>=
=C2=A0 =C2=A0-&gt; =C2=A0Append =C2=A0(cost=3D0.43..5302777.61 rows=3D33207=
03 width=3D12) (actual time=3D1.554..18514.181 rows=3D277457 loops=3D6)<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Index Scan using accounts_p1_=
pkey on accounts_p1 accounts_1 =C2=A0(cost=3D0.43..<font color=3D"#ff0000">=
434635.82 </font>rows=3D273011 width=3D12) (actual time=3D2.214..17627.113 =
rows=3D276971 loops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0-&gt; =C2=A0Index Scan using accounts_p2_pkey on accounts_=
p2 accounts_2 =C2=A0(cost=3D0.43..<font color=3D"#ff0000">441097.44 </font>=
rows=3D277283 width=3D12) (actual time=3D2.185..16280.020 rows=3D276634 loo=
ps=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Con=
d: (transaction_date =3D t2.dt_col)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&=
gt; =C2=A0Index Scan using accounts_p3_pkey on accounts_p3 accounts_3 =C2=
=A0(cost=3D0.43..<font color=3D"#ff0000">441666.94 </font>rows=3D277392 wid=
th=3D12) (actual time=3D0.751..25992.220 rows=3D278390 loops=3D1)<br>=C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_d=
ate =3D t2.dt_col)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Index S=
can using accounts_p4_pkey on accounts_p4 accounts_4 =C2=A0(cost=3D0.43..44=
1689.70 rows=3D277483 width=3D12) (never executed)<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_c=
ol)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Index Scan using accou=
nts_p5_pkey on accounts_p5 accounts_5 =C2=A0(cost=3D0.43..441883.26 rows=3D=
277546 width=3D12) (never executed)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Index Scan using accounts_p6_pkey on=
 accounts_p6 accounts_6 =C2=A0(cost=3D0.43..<b><font color=3D"#ff0000">4418=
57.26 </font></b>rows=3D277591 width=3D12) (actual time=3D1.376..17001.777 =
rows=3D277582 loops=3D3)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0-&gt; =C2=A0Index Scan using accounts_p7_pkey on accounts_=
p7 accounts_7 =C2=A0(cost=3D0.43..441837.57 rows=3D277503 width=3D12) (neve=
r executed)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index=
 Cond: (transaction_date =3D t2.dt_col)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0-&gt; =C2=A0Index Scan using accounts_p8_pkey on accounts_p8 accounts_8 =
=C2=A0(cost=3D0.43..441843.95 rows=3D277511 width=3D12) (never executed)<br=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transa=
ction_date =3D t2.dt_col)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0=
Index Scan using accounts_p9_pkey on accounts_p9 accounts_9 =C2=A0(cost=3D0=
.43..441711.03 rows=3D277506 width=3D12) (never executed)<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D =
t2.dt_col)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Index Scan usin=
g accounts_p10_pkey on accounts_p10 accounts_10 =C2=A0(cost=3D0.43..441918.=
96 rows=3D277554 width=3D12) (never executed)<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)=
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Index Scan using accounts=
_p11_pkey on accounts_p11 accounts_11 =C2=A0(cost=3D0.43..441501.86 rows=3D=
277377 width=3D12) (never executed)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Index Scan using accounts_p12_pkey o=
n accounts_p12 accounts_12 =C2=A0(cost=3D0.43..434530.31 rows=3D272946 widt=
h=3D12) (never executed)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)<br>=C2=A0Planning Ti=
me: 0.879 ms<br>=C2=A0Execution Time: 111432.755 ms<br>(32 rows)</font><br>=
<br></div><div>By looking into it, I have the impression there is a bug in =
the costing sum in that situation, where the cost of the &quot;never execut=
ed&quot; partitions should be deducted from the final cost estimation, whic=
h would make pruning to be the preferred option in this case.</div><div><br=
></div><div>Are my assumptions correct?=C2=A0<br>Is there anything I could =
do to influence the planner to dismiss the cost of &quot;never executed&quo=
t; scans?</div><div><br></div><div>Kind regards,</div><div>Lauro Ojeda</div=
><div>=C2=A0=C2=A0</div></div><br><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, 25 Sept 2025 at 22:18=
, David Rowley &lt;<a href=3D"mailto:dgrowleyml@gmail.com">dgrowleyml@gmail=
.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex">On Fri, 26 Sept 2025 at 07:49, Lauro Ojeda &lt;<a href=3D"mailto:lauro.=
ojeda@gmail.com" target=3D"_blank">lauro.ojeda@gmail.com</a>&gt; wrote:<br>
&gt; 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&#39;s the reason for this misbehaving a=
nd what could I do to overcome it?<br>
<br>
&gt; Partition key: RANGE (transaction_date)<br>
<br>
&gt; postgres=3D&gt; explain analyze<br>
&gt; select aid, abalance<br>
&gt; from accounts<br>
&gt; where transaction_date in (select dt_col from t2);<br>
<br>
The only partition pruning that exists in PostgreSQL that can prune<br>
for that query is for parameterised Nested Loop joins.=C2=A0 For Hash Join,=
<br>
it&#39;s been talked about, but this requires running the partition<br>
pruning code for every values that goes into the Hash Table and only<br>
scanning the unioned set of those partitions during the hash probe<br>
phase. The trouble with that is that it&#39;s very hard to know in advance<=
br>
if it&#39;ll be worth the extra effort. Putting a tuple into a hash table<b=
r>
is quite cheap. Running the pruning code for a range partitioned table<br>
is likely to be a few times more costly than the hash insert<br>
(depending on how many partitions there are), so if the end result is<br>
that nothing was pruned, then that&#39;s quite a bit of extra effort for<br=
>
no gain.<br>
<br>
What we maybe could do better is reduce the cost of the Append scan<br>
when there&#39;s a run-time pruning object attached. This is a little<br>
tricky as we currently only build that object when creating the final<br>
plan.=C2=A0 To include that in the costs we&#39;d need to move that to the =
Path<br>
generation phase so that we didn&#39;t accidentally reject Paths which<br>
could be cheaper than we first think.<br>
<br>
&gt; Also, how could I contribute to get this partition pruning to work?<br=
>
<br>
The pgsql-hackers mailing list is where all the discussions about that<br>
happen. There is plenty of past discussions on these topics. One such<br>
(fairly) recent discussion is in [1]. There are plenty more, including<br>
some ideas from Robert Haas about how we might cost run-time partition<br>
pruning. That was likely around 2017-2018 range, so you might need to<br>
dig deep to find that.<br>
<br>
David<br>
<br>
[1] <a href=3D"https://www.postgresql.org/message-id/flat/CAApHDvoC7n_oceb%=
3D8z%2BMY8sTgH4xa%2ByAwBxZ4Dxv8pwkT9bOcA%40mail.gmail.com#45314d3d01ef8ad1e=
ebe72111989062c" rel=3D"noreferrer" target=3D"_blank">https://www.postgresq=
l.org/message-id/flat/CAApHDvoC7n_oceb%3D8z%2BMY8sTgH4xa%2ByAwBxZ4Dxv8pwkT9=
bOcA%40mail.gmail.com#45314d3d01ef8ad1eebe72111989062c</a><br>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><fon=
t color=3D"#3333ff"><b>Lauro Ojeda</b></font></div></div></div></div></div>=
</div>

--00000000000099cb5b063ff0e804--