Horton

Re: speeding up planning with partitions

Amit Langote <langote_amit_f8@lab.ntt.co.jp>

View thread View on postgr.es
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>
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-29T10:11:15Z
Lists: pgsql-hackers 
Imai-san,

On 2019/01/28 10:44, Imai, Yoshikazu wrote:
> 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?

I re-ran tests with v18 using the following setup [1]:

create table ht (a int, b int) partition by hash (b);
create table ht_0 partition of ht for values with (modulus N, remainder 0);
...
$ cat update-noprune.sql
update ht set a = 0;

pgbench -n -T 60 -f update-noprune,sql

TPS:

nparts  master    0001    0002   0003   0004
======  ======    ====    ====   ====   ====
0       4408      4335    4423   4379   4314
1       3883      3873    3679   3856   4007
2       3495      3476    3477   3500   3627

I can see some degradation for small number of partitions, but maybe it's
just noise?  At least, I don't yet have a systematic explanation for that
happening.

Thanks,
Amit

[1] I changed the compile flags in build scripts to drop
-DCATCACHE_FORCE_RELEASE, which would cause many syscache misses in my
test runs

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.