Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t176w-00EnHw-7g for pgsql-bugs@arkaria.postgresql.org; Wed, 16 Oct 2024 16:42:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t176u-0064qD-EN for pgsql-bugs@arkaria.postgresql.org; Wed, 16 Oct 2024 16:42:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t176t-0064pc-Ug for pgsql-bugs@lists.postgresql.org; Wed, 16 Oct 2024 16:42:56 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t176r-001O2a-QK for pgsql-bugs@lists.postgresql.org; Wed, 16 Oct 2024 16:42:55 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1729096973; bh=syWYtd3NrnYjPSZQXtXUEdfPaCiuB7yKgNN326Yj/Ws=; h=Message-ID:Date:User-Agent:Subject:To:References:From:In-Reply-To: From; b=tE4C11q4DEhYcIujSaME9gsvByMqil/ZzjqH7vTUdzsyfop+6vNFce9Ruiqv4b8tA zCG15Y1VylK1QAtk5Uc1LPyXN2FY4jGsPqHgKvd5LFicC2hwxq2oFlPVnxrPagkFxk r+2gPitpefSc1l336A31wJOabXfJX837w84Rcwz9NS3OLitGfRpMUkicZfz0SH2+sV crUzG6Di0jm1qCNzDHID76MpMyXHXKP+8xD7aZV4vZN/JiBQWs1ZS+b1z/qguobLHM E06JDPUBt/Eahaggwf1UNpOjTHDe9wJ6OVUuHiflS1XcATTUDPdEECkaflIWuuUxMn 5INAufxuzC3Yg== Received: from [172.30.33.154] (unknown [172.30.33.154]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (Client did not present a certificate) (Authenticated sender: a.rybakina@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 150C86018A; Wed, 16 Oct 2024 19:42:53 +0300 (MSK) Content-Type: multipart/alternative; boundary="------------m0prjTyfVfyCJoz6Gx0dsjSi" Message-ID: <955eefa0-2158-4604-b79b-68a087646e91@postgrespro.ru> Date: Wed, 16 Oct 2024 19:42:52 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Performance Issue on Query 18 of TPC-H Benchmark To: Ba Jinsheng , Andrei Lepikhov , "pgsql-bugs@lists.postgresql.org" References: <44b42d35-2283-4a7f-aa2d-a9abe2dcf1b6@gmail.com> <45323a23-47d9-46f5-8e1b-8f0c961a4fad@postgrespro.ru> Content-Language: en-US From: Alena Rybakina In-Reply-To: X-KSMG-AntiPhishing: NotDetected X-KSMG-AntiSpam-Interceptor-Info: not scanned X-KSMG-AntiSpam-Status: not scanned, disabled by settings X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2024/10/16 12:08:00 #26762169 X-KSMG-AntiVirus-Status: NotDetected, skipped X-KSMG-LinksScanning: not scanned, disabled by settings X-KSMG-Message-Action: skipped X-KSMG-Rule-ID: 1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------m0prjTyfVfyCJoz6Gx0dsjSi Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 16.10.2024 17:56, Ba Jinsheng wrote: > >I would like to know if you can improve that case by switching from > the sorted group to a hashed one. > > I used this patch to enable the first HashAggregate only in the query > plan: > diff --git a/src/backend/optimizer/plan/planner.c > b/src/backend/optimizer/plan/planner.c > index 0c7273b9cc..b410452df1 100644 > --- a/src/backend/optimizer/plan/planner.c > +++ b/src/backend/optimizer/plan/planner.c > @@ -6983,8 +6983,9 @@ add_paths_to_grouping_rel(PlannerInfo *root, > RelOptInfo *input_rel, >         bool            can_sort = (extra->flags & > GROUPING_CAN_USE_SORT) != 0; >         List       *havingQual = (List *) extra->havingQual; >         AggClauseCosts *agg_final_costs = &extra->agg_final_costs; > - > -       if (can_sort) > +       static int call_count = 0; > +       call_count++; > +       if (can_sort && call_count != 2) >         { >                 /* >                  * Use any available suitably-sorted path as input, > and also consider > > And got this query plan: >                                    QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >  Limit  (cost=693083.48..*693083.73 *rows=100 width=71) (actual > time=2624.282..2629.392 rows=9 loops=1) >    ->  Sort  (cost=693083.48..694352.29 rows=507522 width=71) (actual > time=2624.281..2629.390 rows=9 loops=1) >          Sort Key: orders.o_totalprice DESC, orders.o_orderdate >          Sort Method: quicksort  Memory: 25kB >          ->  HashAggregate  (cost=658421.05..673686.36 rows=507522 > width=71) (actual time=2624.162..2629.346 rows=9 loops=1) >                Group Key: customer.c_custkey, orders.o_orderkey >                Planned Partitions: 32  Batches: 1  Memory Usage: 793kB >                ->  Gather  (cost=459569.18..608779.05 rows=507522 > width=44) (actual time=2623.805..2629.229 rows=63 loops=1) >                      Workers Planned: 2 >                      Workers Launched: 2 >                      ->  Nested Loop  (cost=458569.18..557026.85 > rows=211468 width=44) (actual time=2581.717..2620.494 rows=21 loops=3) >                            ->  Parallel Hash Join >  (cost=458568.75..492734.09 rows=52844 width=43) (actual > time=2581.704..2620.448 rows=3 loops=3) >                                  Hash Cond: (orders.o_custkey = > customer.c_custkey) >                                  ->  Hash Join >  (cost=453562.50..487589.13 rows=52844 width=24) (actual > time=2541.024..2579.759 rows=3 loops=3) >                                        Hash Cond: (orders.o_orderkey = > lineitem_1.l_orderkey) >                                        ->  Parallel Seq Scan on orders >  (cost=0.00..32386.00 rows=625000 width=20) (actual time=0.028..32.135 > rows=500000 loops=3) >                                        ->  Hash >  (cost=451977.19..451977.19 rows=126825 width=4) (actual > time=2515.787..2515.788 rows=9 loops=3) >                                              Buckets: 131072  Batches: > 1  Memory Usage: 1025kB >                                              ->  GroupAggregate >  (cost=0.43..451977.19 rows=126825 width=4) (actual > time=608.052..2515.758 rows=9 loops=3) >                                                    Group Key: > lineitem_1.l_orderkey >                                                    Filter: > (sum(lineitem_1.l_quantity) > '314'::numeric) >                                                    Rows Removed by > Filter: 1499991 >                                                    ->  Index Scan > using lineitem_pkey on lineitem lineitem_1  (cost=0.43..416256.96 > rows=6002623 width=9) (actual time=0.043..1399.708 rows=6001215 loops=3) >                                  ->  Parallel Hash >  (cost=4225.00..4225.00 rows=62500 width=23) (actual > time=39.601..39.602 rows=50000 loops=3) >                                        Buckets: 262144  Batches: 1 >  Memory Usage: 10304kB >                                        ->  Parallel Seq Scan on > customer  (cost=0.00..4225.00 rows=62500 width=23) (actual > time=0.032..15.561 rows=50000 loops=3) >                            ->  Index Scan using lineitem_pkey on > lineitem  (cost=0.43..1.06 rows=16 width=9) (actual time=0.012..0.014 > rows=7 loops=9) >                                  Index Cond: (l_orderkey = > orders.o_orderkey) >  Planning Time: 1.850 ms >  Execution Time: *2630.023* ms > (30 rows) > > Compared to the query plan with GroupAggregate, both estimated cost > and execution time are similar and have no significant difference. > > To be honest, I don't quite understand what you are showing. 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. > > >I think it was more interesting when I turned off parallelism and > tried to build a query plan without AQO, and the execution time there > was significantly reduced: > Turning off parallelism only brings this significant performance > improvement? > > You may notice that disabling parallelism results in improved cardinality estimation and therefore a better query plan, since the optimizer selects paths based on their cost. If parallelism is disabled, query plan have become more correct. -- Regards, Alena Rybakina Postgres Professional --------------m0prjTyfVfyCJoz6Gx0dsjSi Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 16.10.2024 17:56, Ba Jinsheng wrote:
>I would like to know if you can improve that case by switching from the sorted group to a hashed one.

I used this patch to enable the first HashAggregate only in the query plan:
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 0c7273b9cc..b410452df1 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -6983,8 +6983,9 @@ add_paths_to_grouping_rel(PlannerInfo *root, RelOptInfo *input_rel,
        bool            can_sort = (extra->flags & GROUPING_CAN_USE_SORT) != 0;
        List       *havingQual = (List *) extra->havingQual;
        AggClauseCosts *agg_final_costs = &extra->agg_final_costs;
-
-       if (can_sort)
+       static int call_count = 0;
+       call_count++;
+       if (can_sort && call_count != 2)
        {
                /*
                 * Use any available suitably-sorted path as input, and also consider

And got this query plan:
                                                                                                 QUERY PLAN                                                                                                  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=693083.48..693083.73 rows=100 width=71) (actual time=2624.282..2629.392 rows=9 loops=1)
   ->  Sort  (cost=693083.48..694352.29 rows=507522 width=71) (actual time=2624.281..2629.390 rows=9 loops=1)
         Sort Key: orders.o_totalprice DESC, orders.o_orderdate
         Sort Method: quicksort  Memory: 25kB
         ->  HashAggregate  (cost=658421.05..673686.36 rows=507522 width=71) (actual time=2624.162..2629.346 rows=9 loops=1)
               Group Key: customer.c_custkey, orders.o_orderkey
               Planned Partitions: 32  Batches: 1  Memory Usage: 793kB
               ->  Gather  (cost=459569.18..608779.05 rows=507522 width=44) (actual time=2623.805..2629.229 rows=63 loops=1)
                     Workers Planned: 2
                     Workers Launched: 2
                     ->  Nested Loop  (cost=458569.18..557026.85 rows=211468 width=44) (actual time=2581.717..2620.494 rows=21 loops=3)
                           ->  Parallel Hash Join  (cost=458568.75..492734.09 rows=52844 width=43) (actual time=2581.704..2620.448 rows=3 loops=3)
                                 Hash Cond: (orders.o_custkey = customer.c_custkey)
                                 ->  Hash Join  (cost=453562.50..487589.13 rows=52844 width=24) (actual time=2541.024..2579.759 rows=3 loops=3)
                                       Hash Cond: (orders.o_orderkey = lineitem_1.l_orderkey)
                                       ->  Parallel Seq Scan on orders  (cost=0.00..32386.00 rows=625000 width=20) (actual time=0.028..32.135 rows=500000 loops=3)
                                       ->  Hash  (cost=451977.19..451977.19 rows=126825 width=4) (actual time=2515.787..2515.788 rows=9 loops=3)
                                             Buckets: 131072  Batches: 1  Memory Usage: 1025kB
                                             ->  GroupAggregate  (cost=0.43..451977.19 rows=126825 width=4) (actual time=608.052..2515.758 rows=9 loops=3)
                                                   Group Key: lineitem_1.l_orderkey
                                                   Filter: (sum(lineitem_1.l_quantity) > '314'::numeric)
                                                   Rows Removed by Filter: 1499991
                                                   ->  Index Scan using lineitem_pkey on lineitem lineitem_1  (cost=0.43..416256.96 rows=6002623 width=9) (actual time=0.043..1399.708 rows=6001215 loops=3)
                                 ->  Parallel Hash  (cost=4225.00..4225.00 rows=62500 width=23) (actual time=39.601..39.602 rows=50000 loops=3)
                                       Buckets: 262144  Batches: 1  Memory Usage: 10304kB
                                       ->  Parallel Seq Scan on customer  (cost=0.00..4225.00 rows=62500 width=23) (actual time=0.032..15.561 rows=50000 loops=3)
                           ->  Index Scan using lineitem_pkey on lineitem  (cost=0.43..1.06 rows=16 width=9) (actual time=0.012..0.014 rows=7 loops=9)
                                 Index Cond: (l_orderkey = orders.o_orderkey)
 Planning Time: 1.850 ms
 Execution Time: 2630.023 ms
(30 rows)

Compared to the query plan with GroupAggregate, both estimated cost and execution time are similar and have no significant difference.


To be honest, I don't quite understand what you are showing. 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.

>I think it was more interesting when I turned off parallelism and tried to build a query plan without AQO, and the execution time there was significantly reduced:
Turning off parallelism only brings this significant performance improvement?


You may notice that disabling parallelism results in improved cardinality estimation and therefore a better query plan, since the optimizer selects paths based on their cost. If parallelism is disabled, query plan have become more correct.
-- 
Regards,
Alena Rybakina
Postgres Professional
--------------m0prjTyfVfyCJoz6Gx0dsjSi--