Re: Gather performance analysis - Mailing list pgsql-hackers

From Tomas Vondra
Subject Re: Gather performance analysis
Date
Msg-id 410438b8-6830-a46c-435e-37770e6f9f72@enterprisedb.com
Whole thread Raw
In response to Re: Gather performance analysis  (Amit Kapila <amit.kapila16@gmail.com>)
Responses Re: Gather performance analysis
List pgsql-hackers
On 9/28/21 12:53 PM, Amit Kapila wrote:
> On Tue, Sep 28, 2021 at 12:19 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>>
>> On Mon, Sep 27, 2021 at 10:52 PM Robert Haas <robertmhaas@gmail.com> wrote:
>>
>>>
>>> And most of the time, that's probably a good bet. But, if you do
>>> somehow hit the losing case repeatedly, then you could see a
>>> significant regression. And that might explain Tomas's results.
>>> Perhaps for some reason they just happen to hit that case over and
>>> over again. If that's true, it would be useful to know why it happens
>>> in that case and not others, because then maybe we could avoid the
>>> problem somehow. However, I'm not sure how to figure that out, and I'm
>>> not even entirely sure it's important to figure it out.
>>>
>>
>> Yeah, if it is losing in some cases then it is definitely good to know
>> the reason, I was just looking into the performance numbers shared by
>> Tomas in query-results, I can see the worst case is
>> with 10000000 rows, 10 columns and 4 threads and queue size 64k.
>> Basically, if we see the execution time with head is ~804ms whereas
>> with patch it is ~1277 ms.  But then I just tried to notice the
>> pattern with different queue size so number are like below,
>>
>>                            16k             64k       256k        1024k
>> Head               1232.779    804.24    1134.723    901.257
>> Patch              1371.493    1277.705    862.598    783.481
>>
>> So what I have noticed is that in most of the cases on head as well as
>> with the patch, increasing the queue size make it faster, but with
>> head suddenly for this particular combination of rows, column and
>> thread the execution time is very low for 64k queue size and then
>> again the execution time increased with 256k queue size and then
>> follow the pattern.  So this particular dip in the execution time on
>> the head looks a bit suspicious to me.
>>
> 
> I concur with your observation. Isn't it possible to repeat the same
> test in the same environment to verify these results?
> 

I can repeat any tests we need on that machine, of course.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



pgsql-hackers by date:

Previous
From: "tanghy.fnst@fujitsu.com"
Date:
Subject: RE: Added schema level support for publication.
Next
From: Dilip Kumar
Date:
Subject: Re: Gather performance analysis