From: Alena Rybakina <a.rybakina@postgrespro.ru>
To: Ba Jinsheng <bajinsheng@u.nus.edu>
To: Andrei Lepikhov <lepihov@gmail.com>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Performance Issue on Query 18 of TPC-H Benchmark
Date: Wed, 16 Oct 2024 19:42:52 +0300
Message-ID: <955eefa0-2158-4604-b79b-68a087646e91@postgrespro.ru> (raw)
In-Reply-To: <SEZPR06MB649463AD78819D69D1C060C28A462@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>
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
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: a.rybakina@postgrespro.ru, bajinsheng@u.nus.edu, lepihov@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Performance Issue on Query 18 of TPC-H Benchmark
In-Reply-To: <955eefa0-2158-4604-b79b-68a087646e91@postgrespro.ru>
* 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