RE: How to make partitioning scale better for larger numbers ofpartitions - Mailing list pgsql-hackers

On 2018/07/16 13:16, Tsunakawa-san wrote:
>Thanks.  And what does the comparison look like between the unpartitioned case and various partition counts?  What's
theperformance characteristics in terms of the latency and partition count?   I thought that's what you tried to reveal
first?

In unpartitioned table, latency of SELECT/UPDATE statement is close to O(n), where n is number of records.
Latency of INSERT statements is close to O(1).

In partitioned table, up to 400 partitions, latency of SELECT/INSERT statement is close to O(log n), where n is the
numberof partitions.
 
Between 400 and 6400 partitions, latency is close to O(n).
Up to 400 partitions, latency of UPDATE statement is close to O(n).
Between 400 and 6400 partitions, latency of UPDATE statement seems to O(n^2).

Details are as follows.

unpartitioned table result (prepared mode)
------------------------------------------

 scale | latency_avg |   tps_ex    | update_latency | select_latency | insert_latency 
-------+-------------+-------------+----------------+----------------+----------------
   100 |        0.24 | 4174.395738 |          0.056 |          0.051 |           0.04
   200 |       0.258 | 3873.099014 |          0.065 |          0.059 |           0.04
   400 |        0.29 | 3453.171112 |          0.081 |          0.072 |          0.041
   800 |       0.355 | 2814.936942 |          0.112 |          0.105 |          0.041
  1600 |       0.493 | 2027.702689 |           0.18 |          0.174 |          0.042
  3200 |       0.761 | 1313.532458 |          0.314 |          0.307 |          0.043
  6400 |       1.214 |  824.001431 |           0.54 |          0.531 |          0.043


partitioned talble result (prepared mode)
-----------------------------------------

 num_part | latency_avg |   tps_ex    | update_latency | select_latency | insert_latency 
----------+-------------+-------------+----------------+----------------+----------------
      100 |       0.937 | 1067.473258 |          0.557 |          0.087 |          0.123
      200 |        1.65 |  606.244552 |          1.115 |          0.121 |          0.188
      400 |       3.295 |  303.491681 |          2.446 |           0.19 |          0.322
      800 |       8.102 |  123.422456 |          6.553 |          0.337 |          0.637
     1600 |      36.996 |   27.030027 |         31.528 |          1.621 |          2.455
     3200 |     147.998 |    6.756922 |        136.136 |           4.21 |           4.94
     6400 |     666.947 |    1.499383 |        640.879 |          7.631 |          9.642

regards,
-----Original Message-----
From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com] 
Sent: Monday, July 16, 2018 1:16 PM
To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>
Cc: 'Amit Langote' <Langote_Amit_f8@lab.ntt.co.jp>; PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Subject: RE: How to make partitioning scale better for larger numbers of partitions

From: Kato, Sho [mailto:kato-sho@jp.fujitsu.com]
> I did pgbench -M prepared and perf record.
> 
> UPDATE latency in prepared mode is 95% shorter than in simple mode.
> SELECT latency in prepared mode is 54% shorter than in simple mode.
> INSERT latency in prepared mode is 8% shorter than in simple mode.

Thanks.  And what does the comparison look like between the unpartitioned case and various partition counts?  What's
theperformance characteristics in terms of the latency and partition count?   I thought that's what you tried to reveal
first?

Regards
Takayuki Tsunakawa