Re: speeding up planning with partitions

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

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