Re: Query Performance Degradation Due to Partition Scan Order – PostgreSQL v17.6

On Wed, 10 Sept 2025 at 16:26, Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Wed, Sep 10, 2025 at 4:27 AM David Rowley <dgrowleyml@gmail.com> wrote:
> > This seems quite separate from what's being complained about here. It
> > might be beneficial to reconsider whether we should do some sort of
> > sorting on startup_subpaths inside add_paths_to_append_rel(). I
> > imagine that it might make some sense to sort that list so the path
> > with the cheapest startup cost is first, then put the remainder of the
> > list in order of cheapest total cost per tuple. I suspect that would
> > result in Foreign partitions being scanned last...
>
> If there's LIMIT without ORDER BY, we could order the list of subpaths
> by the number of rows in descending order or cost per row in ascending
> order. That way there are more chances of scanning fewer partitions
> quicker.

Wouldn't that amount to favouring scanning some large foreign
partition over a smaller local partition? My interpretation of
Andrei's "Prefer scanning local partitions to foreign ones" statement
is that was what we shouldn't be doing!

David