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

On 2018-Jul-11, Alvaro Herrera wrote:
> That commit is also in pg11, though -- just not in beta2.  So we still don't know how much of an improvement patch2
isby itself :-)
 

Oops! I benchmarked with 11beta2 + 0001-Speed​​-up-INSERT-and-UPDATE-on-partitioned-tables.patch.
Results are as follows.

Performance seems to be improved.

part_num | latency_avg |   tps_ex   | update_latency | select_latency | insert_latency 
----------+-------------+------------+----------------+----------------+----------------
      100 |        2.09 | 478.379516 |          1.407 |           0.36 |          0.159
      200 |       5.871 | 170.322179 |          4.621 |          0.732 |          0.285
      400 |      39.029 |  25.622384 |         35.542 |          2.273 |          0.758
      800 |     142.624 |   7.011494 |        135.447 |           5.04 |          1.388
     1600 |     559.872 |   1.786138 |        534.301 |         20.318 |          3.122
     3200 |    2161.834 |   0.462574 |       2077.737 |         72.804 |          7.037
     6400 |     8282.38 |   0.120739 |       7996.212 |        259.406 |         14.514

Thanks

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

On 2018-Jul-11, David Rowley wrote:

> On 6 July 2018 at 21:25, Kato, Sho <kato-sho@jp.fujitsu.com> wrote:
> > 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

> Just a note to say that the "Allow direct lookups of AppendRelInfo by 
> child relid" patch is already in master. It's much more relevant to be 
> testing with master than pg11. This patch is not intended for pg11.

That commit is also in pg11, though -- just not in beta2.  So we still don't know how much of an improvement patch2 is
byitself :-)
 

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services