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

On Mon, Sep 15, 2025 at 3:19 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Wed, 10 Sept 2025 at 21:18, Ashutosh Bapat
> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> > > > 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.
>
> I'm still stuck on why to you'd want to sort on the number of rows in
> descending order. What does that have to do with foreign scans?
>
> Otherwise, if the foreign scan comes out cheaper per row, then doing
> those first sounds ok to me. It's all about cost per row in my view.
> Foreign or local scan has no relevance to what I was proposing.

I agree that cost per row is a better parameter to order the subpaths.
I am fine if we go ahead with that.

Descending ordering by number rows definitely assures there are fewer
subplans to execute, so saving any startup and initial work on rest of
the subpaths, if there are many. If we order by cost per row, we may
end up starting and cleaning many cheaper subpaths which may take
longer in practice compared to starting and cleaning up fewer
subpaths.

--
Best Wishes,
Ashutosh Bapat