Horton

RE: speeding up planning with partitions

Imai, Yoshikazu <imai.yoshikazu@jp.fujitsu.com>

View thread View on postgr.es
From: "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>
To: 'Amit Langote' <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>, David Rowley <david.rowley@2ndquadrant.com>, Amit Langote <amitlangote09@gmail.com>, "Pg Hackers" <pgsql-hackers@postgresql.org>
Date: 2019-01-28T01:44:28Z
Lists: pgsql-hackers 
On Thu, Jan 24, 2019 at 6:10 AM, Imai, Yoshikazu wrote:
> updating partkey case:
> 
> part-num  master     0001     0002     0003     0004
> 1        8215.34  7924.99  7931.15  8407.40  8475.65
> 2        7137.49  7026.45  7128.84  7583.08  7593.73
> 4        5880.54  5896.47  6014.82  6405.33  6398.71
> 8        4222.96  4446.40  4518.54  4802.43  4785.82
> 16       2634.91  2891.51  2946.99  3085.81  3087.91
> 32        935.12  1125.28  1169.17  1199.44  1202.04
> 64        352.37   405.27   417.09   425.78   424.53
> 128       236.26   310.01   307.70   315.29   312.81
> 256        65.36    86.84    87.67    84.39    89.27
> 512        18.34    24.84    23.55    23.91    23.91
> 1024        4.83     6.93     6.51     6.45     6.49

I also tested with non-partitioned table case.

updating partkey case:

part-num  master     0001     0002     0003     0004
0        10956.7  10370.5  10472.6  10571.0  10581.5
1        8215.34  7924.99  7931.15  8407.40  8475.65 
...
1024        4.83     6.93     6.51     6.45     6.49


In my performance results, it seems update performance degrades in non-partitioned case with v17-patch applied.
But it seems this degrades did not happen at v2-patch.

On Thu, Aug 30, 2018 at 1:45 AM, Amit, Langote wrote:
> UPDATE:
> 
> nparts  master    0001    0002   0003
> ======  ======    ====    ====   ====
> 0         2856    2893    2862   2816

Does this degradation only occur in my tests? Or if this result is correct, what may causes the degradation?

--
Yoshikazu Imai

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.