Re: speeding up planning with partitions

On 2019/03/31 1:06, Amit Langote wrote:
> On Sun, Mar 31, 2019 at 12:11 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Amit Langote <amitlangote09(at)gmail(dot)com> writes:
>>> I think the performance results did prove that degradation due to
>>> those loops over part_rels becomes significant for very large
>>> partition counts. Is there a better solution than the bitmapset that
>>> you have in mind?
>>
>> Hm, I didn't see much degradation in what you posted in
>> <5c83dbca-12b5-1acf-0e85-58299e464a26(at)lab(dot)ntt(dot)co(dot)jp>.
>
> Sorry that I didn't mention the link to begin with, but I meant to
> point to numbers that I reported on Monday this week.
>
>
https://www.postgresql.org/message-id/19f54c17-1619-b228-10e5-ca343be6a4e8%40lab.ntt.co.jp
>
> You were complaining of the bitmapset being useless overhead for small
> partition counts, but the numbers I get tend to suggest that any
> degradation in performance is within noise range, whereas the
> performance benefit from having them looks pretty significant for very
> large partition counts.
>
>> I am curious as to why there seems to be more degradation
>> for hash cases, as per Yoshikazu-san's results in
>> <0F97FA9ABBDBE54F91744A9B37151A512BAC60(at)g01jpexmbkw24>,
>> but whatever's accounting for the difference probably
>> is not that.
>
> I suspected it may have been the lack of bitmapsets, but maybe only
> Imai-san could've confirmed that by applying the live_parts patch too.

Yeah, I forgot to applying live_parts patch. I did same test again which
I did for hash before.
(BTW, thanks for committing speeding up patches!)

[HEAD(428b260)]
nparts TPS
====== =====
2: 13134 (13240, 13290, 13071, 13172, 12896)
1024: 12627 (12489, 12635, 12716, 12732, 12562)
8192: 10289 (10216, 10265, 10171, 10278, 10514)

[HEAD(428b260) + live_parts.diff]
nparts TPS
====== =====
2: 13277 (13112, 13290, 13241, 13360, 13382)
1024: 12821 (12930, 12849, 12909, 12700, 12716)
8192: 11102 (11134, 11158, 11114, 10997, 11109)

Degradations of performance are below.

My test results from above (with live_parts, HEAD(428b260) +
live_parts.diff)
nparts live_parts HEAD
====== ========== ====
2: 13277 13134
1024: 12821 12627
8192: 11102 10289

11102/13277 = 83.6 %

Amit-san's test results (with live_parts)
> nparts v38 HEAD
> ====== ==== ====
> 2 2971 2969
> 8 2980 1949
> 32 2955 733
> 128 2946 145
> 512 2924 11
> 1024 2986 3
> 4096 2702 0
> 8192 2531 OOM

2531/2971 = 85.2 %

My test results I posted before (without live_parts)
> nparts v38 HEAD
> ====== ==== ====
> 0: 10538 10487
> 2: 6942 7028
> 4: 7043 5645
> 8: 6981 3954
> 16: 6932 2440
> 32: 6897 1243
> 64: 6897 309
> 128: 6753 120
> 256: 6727 46
> 512: 6708 12
> 1024: 6063 3
> 2048: 5894 1
> 4096: 5374 OOM
> 8192: 4572 OOM

4572/6942 = 65.9 %

Certainly, using bitmapset contributes to the performance when scanning
one partition(few partitions) from large partitions.

Thanks
--
Imai Yoshikazu