Re: Performance Issue on Query 18 of TPC-H Benchmark

On 10/17/24 01:26, Ba Jinsheng wrote:
>  >I believe you are not allowing the optimizer to generate a different 
> aggregation path (Group Aggregate) because it requires a sort operation. 
> So I think this is not correct.
> 
> Yes, this is what I did. I though it is what you were asking? I have not 
> found another way to enforce HashAggregate, so I directly modified the 
> code. Can you eliberate why it is incorrect?
As I see, the main problem lies in the first aggregate (with HAVING 
clause) where hash aggregation seems preferable. To disable that, you 
can use some trick:
SET enable_hashagg = 'on';
SET enable_sort = 'off';
SET work_mem = '1GB';

In my case the optimiser have built aggregation:
->  HashAggregate  (cost=202639.35..208346.45 rows=126825 width=4)
     (actual time=3540.761..4224.547 rows=9 loops=3)
     Group Key: lineitem_1.l_orderkey
       Filter: (sum(lineitem_1.l_quantity) > '314'::numeric)
       Batches: 1  Memory Usage: 638993kB
       Rows Removed by Filter: 1499991
       Worker 0:  Batches: 1  Memory Usage: 638993kB
       Worker 1:  Batches: 1  Memory Usage: 638993kB
       ->  Seq Scan on lineitem lineitem_1
       (cost=0.00..172626.23 rows=6002623 width=9)
       (actual time=0.014..675.552 rows=6001215 loops=3)

Not sure it is the best plan possible in this case. I know only about 
re-optimisation feature which can provide correct number of groups 
estimation to aggregates as well as cardinality and work_mem and give 
the optimiser all correct estimations. But it is still an enterprise 
feature :(.

> Can I understand disabling parallelism is a good setup for finding 
> performance issues?
I usually use such such a switch to identify problems. Parallel workers 
use HashJoin more frequently and it sometimes cause non-linear behaviour 
of execution time, compared to sequental plan. At the same time, they 
donn't support parameterised paths and it may end up in interesting 
performance jumps ...

BTW, I also wonder why do you report to pgsql-bugs in presence of better 
fitted pgsql-performance thread?

-- 
regards, Andrei Lepikhov