Re: making update/delete of inheritance trees scale better

On Wed, Mar 31, 2021 at 9:54 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> I reran some of the performance tests I did earlier (I've attached the
> modified test running script for reference):

For archives' sake, noticing a mistake in my benchmarking script, I
repeated the tests. Apparently, all pgbench runs were performed with
40 column tables, not 10, 20, and 40 as shown in the results.

> pgbench -n -T60 -M{simple|prepared} -f nojoin.sql
>
> nojoin.sql:
>
> \set a random(1, 1000000)
> update test_table t set b = :a where a = :a;
>
> ...and here are the tps figures:
>
> -Msimple
>
> nparts 10cols 20cols 40cols
>
> master:
> 64 10112 9878 10920
> 128 9662 10691 10604
> 256 9642 9691 10626
> 1024 8589 9675 9521
>
> patched:
> 64 13493 13463 13313
> 128 13305 13447 12705
> 256 13190 13161 12954
> 1024 11791 11408 11786
>
> No variation across various column counts, but the patched improves
> the tps for each case by quite a bit.

-Msimple

pre-86dc90056:
nparts 10cols 20cols 40cols

64 11345 10650 10327
128 11014 11005 10069
256 10759 10827 10133
1024 9518 10314 8418

post-86dc90056:
10cols 20cols 40cols

64 13829 13677 13207
128 13521 12843 12418
256 13071 13006 12926
1024 12351 12036 11739

My previous assertion that the tps does vary across different column
counts seems to hold in this case, that is, -Msimple mode.

> -Mprepared (plan_cache_mode=force_generic_plan)
>
> master:
> 64 2286 2285 2266
> 128 1163 1127 1091
> 256 531 519 544
> 1024 77 71 69
>
> patched:
> 64 6522 6612 6275
> 128 3568 3625 3372
> 256 1847 1710 1823
> 1024 433 427 386
>
> Again, no variation across columns counts.

-Mprepared

pre-86dc90056:
10cols 20cols 40cols

64 3059 2851 2154
128 1675 1366 1100
256 685 658 544
1024 126 85 76

post-86dc90056:
10cols 20cols 40cols

64 7665 6966 6444
128 4211 3968 3389
256 2205 2020 1783
1024 545 499 389

In the -Mprepared case however, it does vary, both before and after
86dc90056. For the post-86dc90056 case, I suspect it's because
ExecBuildUpdateProjection(), whose complexity is O(number-of-columns),
being performed for *all* partitions in ExecInitModifyTable(). In the
-Msimple case, it would always be for only one partition, so it
doesn't make that much of a difference to ExecInitModifyTable() time.

> tps drops as partition
> count increases both before and after applying the patches, although
> patched performs way better, which is mainly attributable to the
> ability of UPDATE to now utilize runtime pruning (actually of the
> Append under ModifyTable). The drop as partition count increases can
> be attributed to the fact that with a generic plan, there are a bunch
> of steps that must be done across all partitions, such as
> AcauireExecutorLocks(), ExecCheckRTPerms(), per-result-rel
> initializations in ExecInitModifyTable(), etc., even with the patched.
> As mentioned upthread, [1] can help with the last bit.

Here are the numbers after applying that patch:

10cols 20cols 40cols

64 17185 17064 16625
128 12261 11648 11968
256 7662 7564 7439
1024 2252 2185 2101

With the patch, ExecBuildUpdateProjection() will be called only once
irrespective of the number of partitions, almost like the -Msimple
case, so the tps across column counts does not vary by much.

--
Amit Langote
EDB: http://www.enterprisedb.com