Message-ID: <98398f84-6079-47f9-958c-c7e323228cc7@gmail.com>
Date: Thu, 17 Oct 2024 09:51:12 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Performance Issue on Query 18 of TPC-H Benchmark
To: Ba Jinsheng <bajinsheng@u.nus.edu>,
 Alena Rybakina <a.rybakina@postgrespro.ru>,
 "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
References: 
 <SEZPR06MB6494B9CADF2FB15CDBCC7BBF8A452@SEZPR06MB6494.apcprd06.prod.outlook.com>
 <44b42d35-2283-4a7f-aa2d-a9abe2dcf1b6@gmail.com>
 <45323a23-47d9-46f5-8e1b-8f0c961a4fad@postgrespro.ru>
 <SEZPR06MB649463AD78819D69D1C060C28A462@SEZPR06MB6494.apcprd06.prod.outlook.com>
 <955eefa0-2158-4604-b79b-68a087646e91@postgrespro.ru>
 <SEZPR06MB6494FDE43CEBAAEEEB732C918A462@SEZPR06MB6494.apcprd06.prod.outlook.com>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <SEZPR06MB6494FDE43CEBAAEEEB732C918A462@SEZPR06MB6494.apcprd06.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/98398f84-6079-47f9-958c-c7e323228cc7%40gmail.com>
Precedence: bulk

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