Re: MergeAppend could consider sorting cheapest child path - Mailing list pgsql-hackers

On 2/9/2025 03:27, Richard Guo wrote:
> On Tue, Sep 2, 2025 at 5:26 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>> I have another idea.  What if we allow MergeAppend paths only when at
>> least one subpath is preordered.  This trick also allow us to exclude
>> MergeAppend(Sort) dominating Sort(Append).  I see the regression tests
>> changes now have much less volume and looks more reasonable.  What do
>> you think?
> 
> I skimmed through the test case changes, and I'm not sure all of them
> are actual improvements.  For example:
> 
>            ->  Append
> -               ->  Foreign Scan on ftprt1_p1 t1_1
> +               ->  Sort
> +                     Sort Key: t1_1.a
> +                     ->  Foreign Scan on ftprt1_p1 t1_1
>                  ->  Foreign Scan on ftprt1_p2 t1_2
> 
> It seems that this patch moves the sort operation for ftprt1_p1 from
> the remote server to local.  I'm not sure if this is an improvement,
> or why it applies only to ftprt1_p1 and not to ftprt1_p2 (they have
> very similar statistics).
I had a look into this case. The next stuff happens.
Initially, within generate_orderedappend_paths, the planner creates an 
Append according to the 'match_partition_order' strategy, which 
dominates the others.
Next, pathlists of 'Foreign Scan on ftprt1_p1' and 'Foreign Scan on 
ftprt1_p2' are different: the first one contains two paths:
1. startup_cost: 100.000, total_cost: 103.090, pathkeys: false
2. startup_cost: 102.880, total_cost: 103.110, pathkeys: true

And the second subpath has only one option to scan:
startup_cost: 100.000, total_cost: 103.660, pathkeys: true

Before, the optimiser always chose the path with pathkeys. However, this 
patch attempts to do its best by comparing ForeignScan+Sort and ForeignScan.
Comparing the total path with the explicit Sort and pre-sorted one, we have:
- ForeignScan+Sort: startup_cost: 103.100, total_cost: 103.105
- Presorted: startup_cost: 102.880, total_cost: 103.110
And here is the issue: a difference in the third sign after decimal 
point. Let's check remote estimations with and without Sort:

With:
LockRows (cost=2.88..2.90 rows=1 width=25)
-> Sort (cost=2.88..2.89 rows=1 width=25)
Sort Key: t1.a
-> Seq Scan on public.fprt1_p1 t1 (cost=0.00..2.88 ...

Without:
LockRows (cost=0.00..2.88 rows=1 width=25)
-> Seq Scan on public.fprt1_p1 t1 (cost=0.00..2.88 ...

As you can see, according to these estimations, LockRows costs nothing 
without sorting and 0.1 with Sort. So, fluctuation was added by 
EXPLAIN's rounding.

What to do? At first, we can do nothing and just correct the output. But 
I don't like unstable tests. We can adjust the query slightly to 
increase the estimations or improve the estimation using extended 
statistics. I prefer the more elegant variant with extended statistics.
See the attachment for a sketch on how to stabilise the output. With 
this patch applied before this feature, the test output stays the same.

> 
> Besides, I noticed that some plans have changed from an "Index Scan
> with Index Cond" to a "Seq Scan with Filter + Sort".  I'm also not
> sure whether this change results in better performance.
As you know, according to the cost model, SeqScan looks better on scans 
of tiny tables and full scans. I didn't delve as deeply into these cases 
yet as I did in the previous one, but it's clear that we're still seeing 
the issue with tiny tables.

-- 
regards, Andrei Lepikhov