RE: speeding up planning with partitions

On Fri, Mar 29, 2019 at 3:45 PM, Amit Langote wrote:
> Thanks a lot for hacking on the patch. I'm really happy with the direction
> you took for inheritance_planner, as it allows UPDATE/DELETE to use
> partition pruning.

I was astonished by Tom's awesome works and really thanks him.

> Certainly. Note that previously we'd always scan *all* hash partitions
> for UPDATE and DELETE queries, because constraint exclusion can't exclude
> hash partitions due to the shape of their partition constraint.
>
> I ran my usual benchmark with up to 8192 partitions.
>
> N: 2..8192
>
> create table rt (a int, b int, c int) partition by range (a); select 'create
> table rt' || x::text || ' partition of rt for values from (' || (x)::text
> || ') to (' || (x+1)::text || ');' from generate_series(1,
> N) x;
> \gexec
>
> update.sql:
>
> \set param random(1, N)
> update rt set a = 0 where a = :param;
>
> pgbench -n -T 120 -f select.sql
>
> nparts v38 HEAD
> ====== ==== ====
> 2 2971 2969
> 8 2980 1949
> 32 2955 733
> 128 2946 145
> 512 2924 11
> 1024 2986 3
> 4096 2702 0
> 8192 2531 OOM
>
> Obviously, you'll get similar numbers with hash or list partitioning.

I also ran the test for hash partitioning for just make sure.

N: 2..8192

create table ht (a int, b int, c int) partition by hash (a);
select 'create table ht' || x::text ||
' partition of ht for values with (MODULUS N, REMAINDER || (x)::text || ');'
from generate_series(0, N-1) x;
\gexec

update.sql:

\set param random(1, N * 100)
update ht set b = b + 1 where a = :param;

pgbench -n -T 60 -f update.sql

[updating one partition]
nparts v38 HEAD
====== ==== ====
0: 10538 10487
2: 6942 7028
4: 7043 5645
8: 6981 3954
16: 6932 2440
32: 6897 1243
64: 6897 309
128: 6753 120
256: 6727 46
512: 6708 12
1024: 6063 3
2048: 5894 1
4096: 5374 OOM
8192: 4572 OOM

The performance for hash is also improved, though drop rate of performance with large partitions seems higher than that of range partitioning.

Thanks
--
Imai Yoshikazu