RE: Speeding up INSERTs and UPDATEs to partitioned tables

From: "Kato, Sho" <kato-sho(at)jp(dot)fujitsu(dot)com>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: RE: Speeding up INSERTs and UPDATEs to partitioned tables
Date: 2018-07-18 09:44:29
Message-ID: 25C1C6B2E7BE044889E4FE8643A58BA963AAC2AC@G01JPEXMBKW03
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 is by 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(at)2ndquadrant(dot)com]
Sent: Wednesday, July 11, 2018 10:30 PM
To: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Cc: Kato, Sho/加藤 翔 <kato-sho(at)jp(dot)fujitsu(dot)com>; PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)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(at)jp(dot)fujitsu(dot)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 by itself :-)

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrey Klychkov 2018-07-18 09:44:30 Re[2]: Alter index rename concurrently to
Previous Message Peter Eisentraut 2018-07-18 09:30:59 Re: documentation about explicit locking