MIME-Version: 1.0
References: 
 <CAG3nGZ-Pi=ExcSiRpCmjJWpqT+Ugd4p+xHC-K7M33kvAh4xZNQ@mail.gmail.com>
In-Reply-To: 
 <CAG3nGZ-Pi=ExcSiRpCmjJWpqT+Ugd4p+xHC-K7M33kvAh4xZNQ@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 26 Sep 2025 08:17:50 +1200
Message-ID: 
 <CAApHDvrydoBmh5MJQUMpvqaxNOioB2U-jTGahQevYjT_YFfDCw@mail.gmail.com>
Subject: Re: Partition pruning is not happening (even in PG18)
To: Lauro Ojeda <lauro.ojeda@gmail.com>
Cc: Pgsql-performance@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvrydoBmh5MJQUMpvqaxNOioB2U-jTGahQevYjT_YFfDCw%40mail.gmail.com>
Precedence: bulk

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