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

On Wed, Sep 10, 2025 at 4:27 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Mon, 8 Sept 2025 at 22:05, Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
> > I guess they mentioned that the Postgres optimiser doesn't care about
> > the order of Append's subplans. It is a little sad in some cases. The
> > most critical case is when we have a limitation on the number of tuples
> > returned. In this case, the optimiser could consider the following
> > strategies:
> > 1. Prefer scanning local partitions to foreign ones.
> > 2. Pick first partitions with less startup costs and 'high probability'
> > to obtain all necessary tuples from a minimum set of partitions.
> >
> > Postgres arranges clauses inside a long expression according to
> > evaluation cost (see order_qual_clauses). So, why not do similar stuff
> > for subplans?
>
> 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.

--
Best Wishes,
Ashutosh Bapat