pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Alena Rybakina <a.rybakina@postgrespro.ru>
To: Ba Jinsheng <bajinsheng@u.nus.edu>
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 02:21:09 +0300
Message-ID: <38109579-3508-4a79-af55-8eb05ed1d7f0@postgrespro.ru> (raw )
In-Reply-To: <SEZPR06MB6494B9CADF2FB15CDBCC7BBF8A452@SEZPR06MB6494.apcprd06.prod.outlook.com >
References: <SEZPR06MB6494B9CADF2FB15CDBCC7BBF8A452@SEZPR06MB6494.apcprd06.prod.outlook.com >
Hi!
On 15.10.2024 21:28, Ba Jinsheng wrote:
> For this query 18 of TPC-H benchmark:
> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice,
> sum(l_quantity) from CUSTOMER, ORDERS, LINEITEM where o_orderkey in (
> select l_orderkey from LINEITEM group by l_orderkey having
> sum(l_quantity) > 314 ) and c_custkey = o_custkey and o_orderkey =
> l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate,
> o_totalprice order by o_totalprice desc, o_orderdate limit 100;
>
>
> Based on 1GB data
> (https://drive.google.com/file/d/1ZBLHanIRwxbaMQIhRUSPv4I7y8g_0AWi/view?usp=drive_link
> <https://drive.google.com/file/d/1ZBLHanIRwxbaMQIhRUSPv4I7y8g_0AWi/view?usp=drive_link ;),
> its query plan (EXPLAIN ANALYZE) is as follows:
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=666783.65..*666783.90 *rows=100 width=71) (actual
> time=2511.074..2517.146 rows=9 loops=1)
> -> Sort (cost=666783.65..668052.46 rows=507522 width=71) (actual
> time=2511.073..2517.144 rows=9 loops=1)
> Sort Key: orders.o_totalprice DESC, orders.o_orderdate
> Sort Method: quicksort Memory: 25kB
> -> Finalize GroupAggregate (cost=583237.80..647386.53
> rows=507522 width=71) (actual time=2511.057..2517.137 rows=9 loops=1)
> Group Key: customer.c_custkey, orders.o_orderkey
> -> Gather Merge (cost=583237.80..636813.14
> rows=422936 width=71) (actual time=2511.053..2517.128 rows=9 loops=1)
> Workers Planned: 2
> Workers Launched: 2
> -> Partial GroupAggregate
> (cost=582237.78..586995.81 rows=211468 width=71) (actual
> time=2507.789..2507.795 rows=3 loops=3)
> Group Key: customer.c_custkey,
> orders.o_orderkey
> ->* Sort (cost=582237.78..582766.45
> rows=211468 width=44) (actual time=2507.783..2507.786 rows=21 loops=3)*
> Sort Key: customer.c_custkey,
> orders.o_orderkey
> Sort Method: quicksort Memory: 26kB
> Worker 0: Sort Method: quicksort
> Memory: 26kB
> Worker 1: Sort Method: quicksort
> Memory: 25kB
> -> Nested Loop
> (cost=458569.18..557026.85 rows=211468 width=44) (actual
> time=2464.821..2507.757 rows=21 loops=3)
> -> Parallel Hash Join
> (cost=458568.75..492734.09 rows=52844 width=43) (actual
> time=2464.793..2507.699 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=2433.507..2476.356 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.024..33.467 rows=500000 loops=3)
> -> Hash
> (cost=451977.19..451977.19 rows=126825 width=4) (actual
> time=2412.836..2412.836 rows=9 loops=3)
> Buckets: 131072 Batches: 1 Memory Usage: 1025kB
> ->
> GroupAggregate (cost=0.43..451977.19 rows=126825 width=4) (actual
> time=708.272..2412.797 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.052..1331.820 rows=6001215 loops=3)*
> -> Parallel Hash
> (cost=4225.00..4225.00 rows=62500 width=23) (actual
> time=30.683..30.683 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.019..11.368 rows=50000 loops=3)
> -> Index Scan using
> lineitem_pkey on lineitem (cost=0.43..1.06 rows=16 width=9) (actual
> time=0.016..0.017 rows=7 loops=9)
> Index Cond: (l_orderkey =
> orders.o_orderkey)
> Planning Time: 0.833 ms
> Execution Time: 2517.189 ms
> (36 rows)
>
>
> While I found that if we disable the path sorting here:
> diff --git a/src/backend/optimizer/plan/planner.c
> b/src/backend/optimizer/plan/planner.c
> index 0c7273b9cc..91320f473a 100644
> --- a/src/backend/optimizer/plan/planner.c
> +++ b/src/backend/optimizer/plan/planner.c
> @@ -6928,7 +6928,7 @@ make_ordered_path(PlannerInfo *root, RelOptInfo
> *rel, Path *path,
> path->pathkeys,
> &presorted_keys);
> - if (!is_sorted)
> + if (false)
> {
> /*
> * Try at least sorting the cheapest path and also try
> incrementally
>
> Both the performance and estimated cost are reduced around 40%:
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=390889.59..*390889.84 *rows=100 width=71) (actual
> time=1652.572..1655.298 rows=9 loops=1)
> -> Sort (cost=390889.59..392158.39 rows=507522 width=71) (actual
> time=1652.571..1655.296 rows=9 loops=1)
> Sort Key: orders.o_totalprice DESC, orders.o_orderdate
> Sort Method: quicksort Memory: 25kB
> -> Finalize GroupAggregate (cost=215938.45..371492.46
> rows=507522 width=71) (actual time=1651.864..1655.256 rows=9 loops=1)
> Group Key: customer.c_custkey, orders.o_orderkey
> -> Gather (cost=215938.45..360919.08 rows=422936
> width=71) (actual time=1651.670..1655.245 rows=9 loops=1)
> Workers Planned: 2
> Workers Launched: 2
> -> Partial GroupAggregate
> (cost=214938.45..317625.48 rows=211468 width=71) (actual
> time=1616.827..1648.580 rows=3 loops=3)
> Group Key: customer.c_custkey,
> orders.o_orderkey
> -> Nested Loop (cost=214938.45..313396.12
> rows=211468 width=44) (actual time=1609.796..1648.571 rows=21 loops=3)
> -> Parallel Hash Join
> (cost=214938.02..249103.36 rows=52844 width=43) (actual
> time=1609.777..1648.532 rows=3 loops=3)
> Hash Cond: (orders.o_custkey =
> customer.c_custkey)
> -> Hash Join
> (cost=209931.77..243958.39 rows=52844 width=24) (actual
> time=1573.950..1612.634 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.016..32.211 rows=500000 loops=3)
> -> Hash
> (cost=208346.45..208346.45 rows=126825 width=4) (actual
> time=1549.849..1549.849 rows=9 loops=3)
> Buckets: 131072
> Batches: 1 Memory Usage: 1025kB
> -> GroupAggregate
> (cost=0.00..208346.45 rows=126825 width=4) (actual
> time=334.397..1549.837 rows=9 loops=3)
> Group Key:
> lineitem_1.l_orderkey
> Filter: (sum(lineitem_1.l_quantity) > '314'::numeric)
> Rows Removed
> by Filter: 1500388
> * -> Seq Scan on lineitem lineitem_1 (cost=0.00..172626.23
> rows=6002623 width=9) (actual time=0.051..438.226 rows=6001215 loops=3)*
> -> Parallel Hash
> (cost=4225.00..4225.00 rows=62500 width=23) (actual
> time=34.762..34.763 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.011..10.406 rows=50000 loops=3)
> -> Index Scan using lineitem_pkey on
> lineitem (cost=0.43..1.06 rows=16 width=9) (actual time=0.009..0.011
> rows=7 loops=9)
> Index Cond: (l_orderkey =
> orders.o_orderkey)
> Planning Time: 1.644 ms
> Execution Time: 1655.490 ms
> (31 rows)
>
> 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 also tried 10 GB data, in which the execution time is reduced from
> *30s* to *16s*, but the estimated cost is increased. I can provide
> more info if you need.
>
>
> Best regards,
>
> Jinsheng Ba
>
> Notice: This email is generated from the account of an NUS alumnus.
> Contents, views, and opinions therein are solely those of the sender.
can you tell me more information about the user and the name of your
dump database? I can't log into the database.
$ my/inst/bin/psql -U postgres -d postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
FATAL: role "postgres" does not exist
--
Regards,
Alena Rybakina
Postgres Professional
view thread (12+ messages) latest in thread
Message-ID: <38109579-3508-4a79-af55-8eb05ed1d7f0@postgrespro.ru>
Permalink: ../38109579-3508-4a79-af55-8eb05ed1d7f0@postgrespro.ru/
Also on: postgresql.org/message-id/38109579-3508-4a79-af55-8eb05ed1d7f0@postgrespro.ru
copy link · copy postgr.es
reply 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, pgsql-bugs@lists.postgresql.org
Subject: Re: Performance Issue on Query 18 of TPC-H Benchmark
In-Reply-To: <38109579-3508-4a79-af55-8eb05ed1d7f0@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