Re: Gather performance analysis

On Mon, Sep 27, 2021 at 10:52 PM Robert Haas <robertmhaas(at)gmail(dot)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 mean how could we justify
this sudden big dip in execution time w.r.t the other pattern.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com