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-28T05:59:01Z
Lists: pgsql-hackers 
Imai-san,

Thanks for testing.

On 2019/01/24 15:09, Imai, Yoshikazu wrote:
> [pgbench commands]
> pgbench -n -f update.sql -T 30 postgres
> 
> [update.sql(updating partkey case)]
> update rt set a = 1;
> 
> [update.sql(updating non-partkey case)]
> update rt set b = 1;
> 
> [results]
> 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 
> 
> 
> updating non-partkey case:
> 
> part-num   master    0001     0002     0003      0004
> 1        8862.58  8421.49  8575.35  9843.71  10065.30   
> 2        7715.05  7575.78  7654.28  8800.84   8720.60   
> 4        6249.95  6321.32  6470.26  7278.14   7280.10   
> 8        4514.82  4730.48  4823.37  5382.93   5341.10   
> 16       2815.21  3123.27  3162.51  3422.36   3393.94   
> 32        968.45  1702.47  1722.38  1809.89   1799.88   
> 64        364.17   420.48   432.87   440.20    435.31   
> 128       119.94   148.77   150.47   152.18    143.35   
> 256        45.09    46.35    46.93    48.30     45.85   
> 512         8.74    10.59    10.23    10.27     10.13   
> 1024        2.28     2.60     2.56     2.57      2.51   
> 
> 
> Looking at the results, if we only apply 0001 or 0001 + 0002 and if number of partition is few like 1 or 2, performance degrades compare to master(A maximum reduction is about 5%, which is 8863->8421).
> In all other cases, performance improves compare to master.

Just to be clear, these are cases where pruning *doesn't* occur, though
we'll still need to at least figure out why the degradation occurs for
small number of partitions.

Thanks,
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.