public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: Ba Jinsheng <[email protected]>
To: [email protected] <[email protected]>
To: [email protected]
Subject: Re: Performance of Query 60 on TPC-DS Benchmark
Date: Sun, 24 Nov 2024 19:04:17 +0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <SEZPR06MB649422CDEBEBBA3915154EE58A232@SEZPR06MB6494.apcprd06.prod.outlook.com>
References: <SEZPR06MB649422CDEBEBBA3915154EE58A232@SEZPR06MB6494.apcprd06.prod.outlook.com>
On 22/11/2024 18:12, Ba Jinsheng wrote:
> I think the key difference is that the patch disables the usage of Hash
> Join, which incurs a worse performance.
Discovering your case a little more I found out the origins of the
problem: Memoize+NestLoop was not chosen because top-query LIMIT node
wasn't counted in estimation on lower levels of the query. At first, I
found that join prediction is overestimated, that is unusual. Look at this:
-> Merge Semi Join (cost=3611.16..3694.10 rows=2107 width=21) (actual
time=28.195..30.243 rows=498 loops=2)
Merge Cond: (item_2.i_item_id = item_3.i_item_id)
-> Sort (cost=2051.70..2078.17 rows=10588 width=21) (actual
time=14.113..14.625 rows=2416 loops=2)
Sort Key: item_2.i_item_id
Sort Method: quicksort Memory: 938kB
Worker 0: Sort Method: quicksort Memory: 247kB
-> Parallel Seq Scan on item item_2 (cost=0.00..1343.88
rows=10588 width=21) (actual time=0.029..5.954 rows=9000 loops=2)
-> Sort (cost=1559.47..1563.93 rows=1786 width=17) (actual
time=14.072..14.247 rows=950 loops=2)
Sort Key: item_3.i_item_id
Sort Method: quicksort Memory: 49kB
Worker 0: Sort Method: quicksort Memory: 49kB
-> Seq Scan on item item_3 (cost=0.00..1463.00 rows=1786
width=17) (actual time=0.018..12.638 rows=1786 loops=2)
Filter: (i_category = 'Children'::bpchar)
Rows Removed by Filter: 16214
Because of that the Memoize node wasn't chosen. Executing this specific
part of the query:
SET max_parallel_workers_per_gather = 1;
SET parallel_setup_cost = 0.001;
SET parallel_tuple_cost = 0.00005;
SET min_parallel_table_scan_size = 0;
EXPLAIN (ANALYZE)
SELECT * FROM item i1
WHERE i_item_id IN (SELECT i_item_id FROM item i2 WHERE i2.i_category IN
('Children'));
I found that prediction was correct:
Merge Semi Join (cost=3611.16..3694.10 rows=2107 width=21)
(actual time=19.878..26.321 rows=1931 loops=2)
So, top-level nodes just didn't pull more tuples than possible because
of LIMIT. If you remove LIMIT 100 from the query, you can see that your
plan (NestLoop+Memoize) works 24s, much worse than the 3s Postgres (with
HashJoin) created without your changes.
In toto, this example demonstrates the problem of planning queries that
need only fractional results.
I may be wrong, but is this a problem of an Append node?
--
regards, Andrei Lepikhov
view thread (5+ messages) latest in thread
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Performance of Query 60 on TPC-DS Benchmark
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox