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