Re: speeding up planning with partitions

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