Re: [HACKERS] path toward faster partition pruning

On 2018/02/06 18:55, Amit Langote wrote:
>> How fast is this patch these days, compared with the current approach?
>>  It would be good to test both when nearly all of the partitions are
>> pruned and when almost none of the partitions are pruned.
> 
> I will include some performance numbers in my next email, which hopefully
> should not be later than Friday this week.

Here is the latest set of patches.  I can see about 2x speedup in planning
time for various partition counts, although it grows linearly as the
partition count grows (same as with HEAD).  Detailed performance figures
follow.

* Partitioned table schema:

H:
create table ht (a int, b int) partition by hash (b);
create table ht_* partition of ht for values with (modulus N, ...)

L:
create table lt (a int, b int) partition by list (b);
create table lt_1 partition of lt for values in (1)
..
create table lt_N partition of lt for values in (N)

R:
create table rt (a int, b int) partition by range (b);
create table rt_1 partition of rt for values from (1) to (<step>)
..
create table rt_N partition of rt for values in (N-1 * <step>) to (N * <step>)

* Queries

Prunes every partition but 1: select * from table_name where b = 1
Prunes none: select * from table_name where b >= 1


* Planning time in milliseconds (average of 5 runs).

On HEAD:

parts H-prune   H-noprune    L-prune   L-noprune   R-prune    R-noprune
   8     1.50        1.42       1.60        1.55      1.77         1.75
  16     2.49        2.37       2.32        2.65      3.29         3.07
  32     3.96        4.49       3.83        4.14      5.06         5.70
  64     8.02        7.51       7.14        7.34      9.37        10.02
 128    14.47       14.19      13.31       13.99     18.09        18.86
 256    24.76       27.63      25.59       27.87     34.15        37.19
 512    50.36       55.92      52.56       54.76     69.34        72.55
1024   102.94      110.59     104.97      110.41    136.89       146.54

Patched:

parts H-prune    H-noprune   L-prune   L-noprune   R-prune   R-noprune
   8     1.49         0.90      0.87        0.74      0.84        1.09
  16     2.01         1.50      1.42        1.68      1.42        1.41
  32     2.63         2.47      2.08        2.69      2.73        2.81
  64     5.62         4.66      4.45        4.96      4.92        5.08
 128    11.28         9.65      9.00        9.60      8.68        9.91
 256    18.36        18.49     17.11       18.39     17.47       18.43
 512    33.88        36.89     34.06       36.52     34.01       37.26
1024    66.40        72.75     66.37       73.40     67.06       67.06


Attached v25 patches.

0001-Modify-bound-comparision-functions-to-accept-mem.patch

This is Ashutosh's patch that he posted on the "advanced partition
matching algorithm for partition-wise join" thread.

0002-Refactor-partition-bound-search-functions.patch

This is similar to 0001.  Whereas 0001 modifies just the comparison
functions, this one modifies the partition bound search functions, because
the main pruning patch uses the search functions.

0003-Add-parttypid-partcollation-partsupfunc-to-Parti.patch

This adds some of the fields to PartitionScheme that were needed by the
main pruning patch.

The above 3 patches do what they do, because we'd like the main pruning to
patch to add its functionality by relying on whatever information is made
available in the partitioned table's RelOptInfo.

0004-Faster-partition-pruning.patch

The main patch that adds src/backend/optimizer/util/partprune.c, a module
to provide the functionality that will replace the current approach of
calling relation_excluded_by_constraints() for each partition.

Sorry, but there is still this big TODO here, which I'll try to fix early
next week.

+ * partprune.c
+ *      Provides functions to prune partitions of a partitioned table by
+ *      comparing provided set of clauses with the table's partitions'
+ *      boundaries
+ *
+ * TODO: write a longer description of things in this file


0005-Add-only-unpruned-partitioned-child-rels-to-part.patch

This one teaches the planner to put *only* the un-pruned partitioned child
tables into partitioned_rels list of certain plan nodes.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/CAFjFpRctst136uN2BvbWLAkS7w278XmKY4_PUB%2Bxk-%2BNezNq8g%40mail.gmail.com