On Fri, 26 Sept 2025 at 07:49, Lauro Ojeda <[email protected]> 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
