Re: making update/delete of inheritance trees scale better - Mailing list pgsql-hackers

On Wed, Mar 31, 2021 at 9:54 PM Amit Langote <amitlangote09@gmail.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