Message-ID: <978176f8-14a5-4f4d-8925-07d2e7dec4ce@gmail.com>
Date: Sat, 12 Oct 2024 08:53:19 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Question of Parallel Hash Join on TPC-H Benchmark
To: Ba Jinsheng <bajinsheng@u.nus.edu>
References: 
 <SEZPR06MB64945FE5A48A367A8C0EE8FC8A782@SEZPR06MB6494.apcprd06.prod.outlook.com>
 <SEZPR06MB6494C5B80077EC08B21772888A782@SEZPR06MB6494.apcprd06.prod.outlook.com>
 <20f9671d-6f40-429b-a749-a2f53086e0dd@vondra.me>
 <3c7df680-97e6-4f20-ab27-65fb8282f95e@Spark>
 <SEZPR06MB6494BD3DDF5B03700032C2C98A782@SEZPR06MB6494.apcprd06.prod.outlook.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
Cc: Tomas Vondra <tomas@vondra.me>, Zhang Mingli <zmlpostgres@gmail.com>,
 "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
In-Reply-To: 
 <SEZPR06MB6494BD3DDF5B03700032C2C98A782@SEZPR06MB6494.apcprd06.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/978176f8-14a5-4f4d-8925-07d2e7dec4ce%40gmail.com>
Precedence: bulk

On 10/10/2024 23:52, Ba Jinsheng wrote:
> Thanks for your replies!
> 
> 
> I configured these variables and did get a worse query plan:
Could you provide SQL dump and settings to play with this case locally?
I would discover what we need to transform Subplan 1 into a SEMI JOIN.

Also, I usually force parallel workers with settings like below:

max_parallel_workers_per_gather = 32
min_parallel_table_scan_size = 0
min_parallel_index_scan_size = 0
max_worker_processes = 64
parallel_setup_cost = 0.001
parallel_tuple_cost = 0.0001

just to be sure, it's not the case when something down the plan tree 
forbids generating a parallel query plan.

P.S.
To discover other options, the AQO extension may be helpful. It stores 
cardinalities from all plan nodes after execution and uses them to 
calculate selectivities on the subsequent execution.

-- 
regards, Andrei Lepikhov