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@sss.pgh.pa.us> wrote:
 >> Amit Langote <amitlangote09@gmail.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@lab.ntt.co.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@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