From: Andrei Lepikhov <lepihov@gmail.com>
To: Ba Jinsheng <bajinsheng@u.nus.edu>
To: Alena Rybakina <a.rybakina@postgrespro.ru>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Performance Issue on Query 18 of TPC-H Benchmark
Date: Thu, 17 Oct 2024 09:51:12 +0700
Message-ID: <98398f84-6079-47f9-958c-c7e323228cc7@gmail.com> (raw)
In-Reply-To: <SEZPR06MB6494FDE43CEBAAEEEB732C918A462@SEZPR06MB6494.apcprd06.prod.outlook.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: lepihov@gmail.com, bajinsheng@u.nus.edu, a.rybakina@postgrespro.ru, pgsql-bugs@lists.postgresql.org
Subject: Re: Performance Issue on Query 18 of TPC-H Benchmark
In-Reply-To: <98398f84-6079-47f9-958c-c7e323228cc7@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox