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.
>
>
The plan generation stage is where the costing sum is figured out whereas
the "never executed" marking/detection is in the execution stage.
So this doesnt look to be a costing bug for the above scenario where the
planner was unable to prune partitions early.
> 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 wrote:
>
>> On Fri, 26 Sept 2025 at 07:49, Lauro Ojeda 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*
>
--
--
Regards,
Chetan
