Re: POC: GROUP BY optimization

On 22/2/2024 13:35, Richard Guo wrote:
> The avg() function on integer argument is commonly used in
> aggregates.sql.  I don't think this is an issue.  See the first test
> query in aggregates.sql.
Make sense
> > it should be parallel to the test cases for utilize the ordering of
> > index scan and subquery scan.
>
> Also, I'm unsure about removing the disabling of the
> max_parallel_workers_per_gather parameter. Have you discovered the
> domination of the current plan over the partial one? Do the cost
> fluctuations across platforms not trigger a parallel plan?
>
>
> The table used for testing contains only 100 tuples, which is the size
> of only one page.  I don't believe it would trigger any parallel plans,
> unless we manually change min_parallel_table_scan_size.
I don't intend to argue it, but just for the information, I frequently
reduce it to zero, allowing PostgreSQL to make a decision based on
costs. It sometimes works much better, because one small table in multi
join can disallow an effective parallel plan.
>
> What's more, I suggest to address here the complaint from [1]. As I
> see,
> cost difference between Sort and IncrementalSort strategies in that
> case
> is around 0.5. To make the test more stable I propose to change it a
> bit
> and add a limit:
> SELECT count(*) FROM btg GROUP BY z, y, w, x LIMIT 10;
> It makes efficacy of IncrementalSort more obvious difference around 10
> cost points.
>
>
> I don't think that's necessary.  With Incremental Sort the final cost
> is:
>
>     GroupAggregate  (cost=1.66..19.00 rows=100 width=25)
>
> while with full Sort it is:
>
>     GroupAggregate  (cost=16.96..19.46 rows=100 width=25)
>
> With the STD_FUZZ_FACTOR (1.01), there is no doubt that the first path
> is cheaper on total cost.  Not to say that even if somehow we decide the
> two paths are fuzzily the same on total cost, the first path still
> dominates because its startup cost is much cheaper.
As before, I won't protest here - it needs some computations about how
much cost can be added by bulk extension of the relation blocks. If
Maxim will answer that it's enough to resolve his issue, why not?

--
regards,
Andrei Lepikhov
Postgres Professional