Horton

Re: speeding up planning with partitions

Amit Langote <amitlangote09@gmail.com>

View thread View on postgr.es
From: Amit Langote <amitlangote09@gmail.com>
To: Imai Yoshikazu <yoshikazu_i443@live.jp>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, David Rowley <david.rowley@2ndquadrant.com>, "jesper.pedersen@redhat.com" <jesper.pedersen@redhat.com>, "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-03-31T05:40:46Z
Lists: pgsql-hackers 
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

Commits

  1. Clean up handling of constraint_exclusion and enable_partition_pruning.

  2. Add test case exercising formerly-unreached code in inheritance_planner.

  3. Speed up planning when partitions can be pruned at plan time.

  4. Avoid crash in partitionwise join planning under GEQO.

  5. Avoid passing query tlist around separately from root->processed_tlist.

  6. Build "other rels" of appendrel baserels in a separate step.

  7. Get rid of duplicate child RTE for a partitioned table.

  8. Rearrange make_partitionedrel_pruneinfo to avoid work when we can't prune.

  9. Don't copy PartitionBoundInfo in set_relation_partition_info.

  10. Move building of child base quals out into a new function

  11. Reorganize planner code moved in b60c39759908

  12. Move inheritance expansion code into its own file

  13. Fix inherited UPDATE/DELETE with UNION ALL subqueries.

  14. Rearrange planner to save the whole PlannerInfo (subroot) for a subquery.