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

Hi!

On 16.10.2024 09:28, Andrei Lepikhov wrote:
> On 10/16/24 01:28, Ba Jinsheng wrote:
>> The major differerence between both query plans is the first one has
>> additional *SORT*. I believe the second query plan is more efficient.
>> Similar to my last report, perhaps we can optimize code to enable it.
> I would like to know if you can improve that case by switching from
> the sorted group to a hashed one.
> I see huge underestimation because of the HAVING clause on an
> aggregate. It would be interesting to correct the prediction and
> observe what will happen.
>
Unfortunately my laptop is not so fast, so my execution time is
*25822.899 ms.*

Initial query plan on my laptop is the same:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=668833.97..668834.22 rows=100 width=71) (actual
time=25814.870..25822.461 rows=9 loops=1)
   ->  Sort  (cost=668833.97..670117.12 rows=513262 width=71) (actual
time=25814.867..25822.454 rows=9 loops=1)
         Sort Key: orders.o_totalprice DESC, orders.o_orderdate
         Sort Method: quicksort  Memory: 25kB
         ->  Finalize GroupAggregate (cost=584343.41..649217.47
rows=513262 width=71) (actual time=25814.736..25822.399 rows=9 loops=1)
               Group Key: customer.c_custkey, orders.o_orderkey
               ->  Gather Merge  (cost=584343.41..638524.51 rows=427718
width=71) (actual time=25814.716..25822.328 rows=9 loops=1)
                     Workers Planned: 2
                     Workers Launched: 2
                     ->  Partial GroupAggregate
(cost=583343.39..588155.22 rows=213859 width=71) (actual
time=25792.566..25792.609 rows=3 loops=3)
                           Group Key: customer.c_custkey, orders.o_orderkey
                           ->  Sort  (cost=583343.39..583878.04
rows=213859 width=44) (actual time=25792.512..25792.527 rows=21 loops=3)
                                 Sort Key: customer.c_custkey,
orders.o_orderkey
                                 Sort Method: quicksort  Memory: 25kB
                                 Worker 0:  Sort Method: quicksort 
Memory: 26kB
                                 Worker 1:  Sort Method: quicksort 
Memory: 26kB
                                 ->  Nested Loop
(cost=458633.58..557830.13 rows=213859 width=44) (actual
time=25480.327..25792.187 rows=21 loops=3)
                                       ->  Parallel Hash Join
(cost=458633.15..492800.08 rows=53450 width=43) (actual
time=25480.163..25791.800 rows=3 loops=3)
                                             Hash Cond:
(orders.o_custkey = customer.c_custkey)
                                             ->  Hash Join
(cost=453626.90..487653.53 rows=53450 width=24) (actual
time=25367.158..25677.611 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.110..237.788 rows=500000 loops=3)
                                                   ->  Hash
(cost=452023.40..452023.40 rows=128280 width=4) (actual
time=25157.711..25157.713 rows=9 loops=3)
                                                         Buckets:
131072  Batches: 1  Memory Usage: 1025kB
                                                         ->
GroupAggregate  (cost=0.43..452023.40 rows=128280 width=4) (actual
time=5918.735..25157.610 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..416242.51 rows=6001659 width=9) (actual
time=0.186..11092.476 rows=6001215 loops=3)
                                             ->  Parallel Hash
(cost=4225.00..4225.00 rows=62500 width=23) (actual
time=110.867..110.868 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.071..49.232 rows=50000 loops=3)
                                       ->  Index Scan using
lineitem_pkey on lineitem  (cost=0.43..1.06 rows=16 width=9) (actual
time=0.096..0.109 rows=7 loops=9)
                                             Index Cond: (l_orderkey =
orders.o_orderkey)
 Planning Time: 5.025 ms
* Execution Time: 25822.899 ms*
(36 rows)

To get a better plan, I ran the query with the AQO [0] extension and it
cut the execution time in half. Unfortunately, AQO doesn't remember the
power of hash nodes, and to be honest, I set the power to the correct
power in the code.
I know this is the wrong way, but I haven't had enough time to fix it
properly. And so here is my query plan:

I also disabled parallelism and my query plan like this:

set max_parallel_maintenance_workers =0;

set max_parallel_workers=0;

set max_parallel_workers_per_gather =0;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=451107.39..451107.48 rows=36 width=71) (actual
time=12582.114..12582.136 rows=9 loops=1)
   ->  Sort  (cost=451107.39..451107.48 rows=36 width=71) (actual
time=12582.112..12582.118 rows=9 loops=1)
         Sort Key: orders.o_totalprice DESC, orders.o_orderdate
         Sort Method: quicksort  Memory: 25kB
         ->  GroupAggregate  (cost=451105.65..451106.46 rows=36
width=71) (actual time=12581.981..12582.082 rows=9 loops=1)
               Group Key: customer.c_custkey, orders.o_orderkey
               ->  Sort  (cost=451105.65..451105.74 rows=36 width=44)
(actual time=12581.951..12581.969 rows=63 loops=1)
                     Sort Key: customer.c_custkey, orders.o_orderkey
                     Sort Method: quicksort  Memory: 28kB
                     ->  Nested Loop  (cost=1.71..451104.72 rows=36
width=44) (actual time=2704.881..12581.823 rows=63 loops=1)
                           ->  Nested Loop (cost=1.28..451093.77 rows=9
width=43) (actual time=2704.826..12581.457 rows=9 loops=1)
                                 ->  Nested Loop (cost=0.86..451089.74
rows=9 width=24) (actual time=2704.741..12581.024 rows=9 loops=1)
                                       ->  GroupAggregate
(cost=0.43..451013.73 rows=9 width=4) (actual time=2697.354..12522.126
rows=9 loops=1)
                                             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..416226.85 rows=6000615
width=9) (actual time=0.030..5515.665 rows=6001215 loops=1)
                                       ->  Index Scan using orders_pkey
on orders  (cost=0.43..8.45 rows=1 width=20) (actual time=6.530..6.530
rows=1 loops=9)
                                             Index Cond: (o_orderkey =
lineitem_1.l_orderkey)
                                 ->  Index Scan using customer_pkey on
customer  (cost=0.42..0.45 rows=1 width=23) (actual time=0.039..0.039
rows=1 loops=9)
                                       Index Cond: (c_custkey =
orders.o_custkey)
                           ->  Index Scan using lineitem_pkey on
lineitem  (cost=0.43..1.06 rows=16 width=9) (actual time=0.026..0.033
rows=7 loops=9)
                                 Index Cond: (l_orderkey =
orders.o_orderkey)
 Planning Time: 1.403 ms
 Execution Time: *12582.321 ms*
(25 rows)

[0] https://github.com/postgrespro/aqo

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:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=722284.69..722284.94 rows=100 width=71) (actual
time=16251.401..16251.413 rows=9 loops=1)
   ->  Sort  (cost=722284.69..723567.84 rows=513262 width=71) (actual
time=16251.399..16251.408 rows=9 loops=1)
         Sort Key: orders.o_totalprice DESC, orders.o_orderdate
         Sort Method: quicksort  Memory: 25kB
         ->  GroupAggregate  (cost=512218.34..702668.18 rows=513262
width=71) (actual time=16227.765..16251.371 rows=9 loops=1)
               Group Key: customer.c_custkey, orders.o_orderkey
               ->  Incremental Sort  (cost=512218.34..692402.94
rows=513262 width=44) (actual time=16227.735..16251.241 rows=63 loops=1)
                     Sort Key: customer.c_custkey, orders.o_orderkey
                     Presorted Key: customer.c_custkey
                     Full-sort Groups: 2  Sort Method: quicksort
Average Memory: 27kB  Peak Memory: 27kB
                     ->  Nested Loop (cost=512217.20..678432.66
rows=513262 width=44) (actual time=16094.663..16251.114 rows=63 loops=1)
                           ->  Merge Join (cost=512216.77..522360.54
rows=128280 width=43) (actual time=16094.596..16250.531 rows=9 loops=1)
                                 Merge Cond: (customer.c_custkey =
orders.o_custkey)
                                 ->  Index Scan using customer_pkey on
customer  (cost=0.42..7524.45 rows=150000 width=23) (actual
time=0.039..148.356 rows=147198 loops=1)
                                 ->  Materialize
(cost=512216.29..512857.69 rows=128280 width=24) (actual
time=16068.493..16068.529 rows=9 loops=1)
                                       ->  Sort
(cost=512216.29..512536.99 rows=128280 width=24) (actual
time=16068.478..16068.496 rows=9 loops=1)
                                             Sort Key: orders.o_custkey
                                             Sort Method: quicksort 
Memory: 25kB
                                             ->  Hash Join
(cost=453626.90..498700.41 rows=128280 width=24) (actual
time=15309.799..16068.390 rows=9 loops=1)
                                                   Hash Cond:
(orders.o_orderkey = lineitem_1.l_orderkey)
                                                   ->  Seq Scan on
orders  (cost=0.00..41136.00 rows=1500000 width=20) (actual
time=0.011..439.488 rows=1500000 loops=1)
                                                   ->  Hash
(cost=452023.40..452023.40 rows=128280 width=4) (actual
time=15104.560..15104.562 rows=9 loops=1)
                                                         Buckets:
131072  Batches: 1  Memory Usage: 1025kB
                                                         ->
GroupAggregate  (cost=0.43..452023.40 rows=128280 width=4) (actual
time=3731.232..15104.495 rows=9 loops=1)
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..416242.51 rows=6001659 width=9) (actual time=0.034..6936.304
rows=6001215 loops=1)
                           ->  Index Scan using lineitem_pkey on
lineitem  (cost=0.43..1.06 rows=16 width=9) (actual time=0.045..0.053
rows=7 loops=9)
                                 Index Cond: (l_orderkey =
orders.o_orderkey)
 Planning Time: 1.782 ms
 Execution Time: *16251.698 ms*
(32 rows)

Looking at the code, I think the problem with cost estimation in
aggregation nodes is caused by incorrect selectivity estimation
(cost_agg function) and can be solved using advanced statistics. But to
be honest, I'm still investigating this.

--
Regards,
Alena Rybakina
Postgres Professional