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

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.

David