RE: Speeding up INSERTs and UPDATEs to partitioned tables - Mailing list pgsql-hackers

Thanks David!

I did benchmark with pgbench, and see a speedup for INSERT / UPDATE scenarios.
I used range partition.

Benchmark results are as follows.

1. 11beta2 result

 part_num |   tps_ex   | latency_avg | update_latency | select_latency | insert_latency 
----------+------------+-------------+----------------+----------------+----------------
      100 | 479.456278 |       2.086 |          1.382 |          0.365 |          0.168
      200 | 169.155411 |       5.912 |          4.628 |          0.737 |          0.299
      400 |  24.857495 |       40.23 |         36.606 |          2.252 |          0.881
      800 |   6.718104 |     148.853 |        141.471 |          5.253 |          1.433
     1600 |   1.934908 |     516.825 |        489.982 |         21.102 |          3.701
     3200 |   0.456967 |    2188.362 |       2101.247 |         72.784 |          8.833
     6400 |   0.116643 |    8573.224 |        8286.79 |        257.904 |         14.949


2. 11beta2 + patch1 + patch2

patch1: Allow direct lookups of AppendRelInfo by child relid
        commit 7d872c91a3f9d49b56117557cdbb0c3d4c620687
patch2: 0001-Speed-up-INSERT-and-UPDATE-on-partitioned-tables.patch

 part_num |   tps_ex    | latency_avg | update_latency | select_latency | insert_latency 
----------+-------------+-------------+----------------+----------------+----------------
      100 | 1224.430344 |       0.817 |          0.551 |          0.085 |          0.048
      200 |  689.567511 |        1.45 |           1.12 |          0.119 |           0.05
      400 |  347.876616 |       2.875 |          2.419 |          0.185 |          0.052
      800 |  140.489269 |       7.118 |          6.393 |          0.329 |          0.059
     1600 |   29.681672 |      33.691 |         31.272 |          1.517 |          0.147
     3200 |    7.021957 |     142.412 |          136.4 |          4.033 |          0.214
     6400 |    1.462949 |     683.557 |        669.187 |          7.677 |          0.264


benchmark script:

\set aid random(1, 100 * 1)
\set delta random(-5000, 5000)
BEGIN;
UPDATE test.accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM test.accounts WHERE aid = :aid;
INSERT INTO test.accounts_history (aid, delta, mtime) VALUES (:aid, :delta, CURRENT_TIMESTAMP);
END;

partition key is aid.

-----Original Message-----
From: David Rowley [mailto:david.rowley@2ndquadrant.com] 
Sent: Thursday, July 05, 2018 6:19 PM
To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Speeding up INSERTs and UPDATEs to partitioned tables

On 5 July 2018 at 18:39, Kato, Sho <kato-sho@jp.fujitsu.com> wrote:
> postgres=# create table a(i int) partition by range(i); CREATE TABLE 
> postgres=# create table a_1 partition of a for values from(1) to 
> (200); CREATE TABLE postgres=# create table a_2 partition of a for 
> values from(200) to (400); server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.

Hi,

Thanks for testing. I'm unable to reproduce this on beta2 or master as of f61988d16.

Did you try make clean then building again?  The 0001 patch does change PartitionDescData, so if you've not rebuilt all
.cfiles which use that then that might explain your crash.
 

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services