MIME-Version: 1.0
References: 
 <CAG3nGZ-Pi=ExcSiRpCmjJWpqT+Ugd4p+xHC-K7M33kvAh4xZNQ@mail.gmail.com>
 <CAApHDvrydoBmh5MJQUMpvqaxNOioB2U-jTGahQevYjT_YFfDCw@mail.gmail.com>
 <CAG3nGZ-PpRarYRqbMzLZoVkma+ifSmNjmuPSL0tT3_opMZ1Dxg@mail.gmail.com>
In-Reply-To: 
 <CAG3nGZ-PpRarYRqbMzLZoVkma+ifSmNjmuPSL0tT3_opMZ1Dxg@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 30 Sep 2025 09:08:16 +1300
Message-ID: 
 <CAApHDvrB5WcbU_DqyGtejDyepqpdFNGJhdea+8xuxwNwQ50kFQ@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"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvrB5WcbU_DqyGtejDyepqpdFNGJhdea%2B8xuxwNwQ50kFQ%40mail.gmail.com>
Precedence: bulk

On Tue, 30 Sept 2025 at 02:49, Lauro Ojeda <lauro.ojeda@gmail.com> wrote:
> By looking into it, I have the impression there is a bug in the costing s=
um in that situation, where the cost of the "never executed" partitions sho=
uld be deducted from the final cost estimation, which would make pruning to=
 be the preferred option in this case.
>
> Are my assumptions correct?

The "never executed" part is not determined at planning time. It's
only something the executor gets to figure out, and by that time, it's
no good adjusting the plan's costs since the planner has already
decided on what it thinks the best plan is and it's too late to change
that.

> Is there anything I could do to influence the planner to dismiss the cost=
 of "never executed" scans?

Not in the general sense, but for nodes that are "never executed" due
to run-time partition pruning. It would be possible to make some
assumptions during planning. The planner would need to do some extra
work during planning to figure out if an AppendPath or MergeAppend
path has run-time prunable parameters.  If those parameters plus any
non-parameterized quals result in pruning being possible at run-time,
then *maybe* there's something we can do better. For equi joins, you
could assume that only 1 of the Append children will be scanned.  The
planner will have no idea which one, but it could do something like
total_append_cost / number_of_append_children. For other join types,
it's much less clear how that would work. There have been previous
proposals to do something with DEFAULT_INEQ_SEL or maybe
DEFAULT_RANGE_INEQ_SEL. I've forgotten the exact details as it was
about 8 years ago.

David