Re: speeding up planning with partitions

On Sun, Mar 31, 2019 at 11:45 AM Imai Yoshikazu <yoshikazu_i443@live.jp> wrote:
> 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:
>  >> 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!)

Thanks a lot for committing, Tom.  I wish you had listed yourself as
an author though.

I will send the patch for get_relation_constraints() mentioned
upthread tomorrow.

> [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-san for testing.

Regards,
Amit