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

On Wed, Sep 10, 2025 at 11:50 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Wed, 10 Sept 2025 at 16:26, Ashutosh Bapat
> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> >
> > On Wed, Sep 10, 2025 at 4:27 AM David Rowley <dgrowleyml(at)gmail(dot)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!

Generally foreign scans will have a higher cost, including startup
cost. So subpaths with local scans will be preferred. But in case
there's a foreign subpath with a lower cost than local subpath, I
think foreign scan should be preferred.

--
Best Wishes,
Ashutosh Bapat