RE: How to make partitioning scale better for larger numbers ofpartitions

Tsunakawa-san

>Kato-san, could you try pgbench -M prepared?

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.

In perf report, AllocSetAlloc, hash_search_with_hash_value and hash_any appeared in all SQL.

Details are as follows.

pgbench results
--------------

transaction type: update.sql
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
duration: 180 s
number of transactions actually processed: 12295
latency average = 14.641 ms
tps = 68.302806 (including connections establishing)
tps = 68.303430 (excluding connections establishing)
statement latencies in milliseconds:
         0.009  \set aid random(1, 1100 * 1)
         0.004  \set delta random(-5000, 5000)
         0.026  BEGIN;
        14.089  UPDATE test.accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.513  END;

transaction type: select.sql
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
duration: 180 s
number of transactions actually processed: 45145
latency average = 3.996 ms
tps = 250.272094 (including connections establishing)
tps = 250.274404 (excluding connections establishing)
statement latencies in milliseconds:
         0.750  \set aid random(1, 1100 * 1)
         0.714  \set delta random(-5000, 5000)
         0.023  BEGIN;
         2.262  SELECT abalance FROM test.accounts WHERE aid = :aid;
         0.247  END;

transaction type: insert.sql
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
duration: 180 s
number of transactions actually processed: 34894
latency average = 5.158 ms
tps = 193.855216 (including connections establishing)
tps = 193.857020 (excluding connections establishing)
statement latencies in milliseconds:
         1.007  \set aid random(1, 1100 * 1)
         0.802  \set delta random(-5000, 5000)
         0.025  BEGIN;
         2.963  INSERT INTO test.accounts_history (aid, delta, mtime) VALUES (:aid, :delta, CURRENT_TIMESTAMP);
         0.360  END;

Top 10 of perf report
---------------------
UPDATE:
 11.86%  postgres  postgres           [.] list_nth
 10.23%  postgres  postgres           [.] ExecOpenScanRelation
  7.47%  postgres  postgres           [.] list_member_int
  4.78%  postgres  postgres           [.] AllocSetAlloc
  3.61%  postgres  postgres           [.] palloc0
  3.09%  postgres  postgres           [.] hash_search_with_hash_value
  2.33%  postgres  postgres           [.] ResourceArrayAdd
  1.99%  postgres  postgres           [.] hash_any
  1.83%  postgres  postgres           [.] copyObjectImpl
  1.64%  postgres  postgres           [.] SearchCatCache1

SELECT:
 33.60%  postgres  postgres           [.] hash_search_with_hash_value
 13.02%  postgres  postgres           [.] hash_any
  5.30%  postgres  postgres           [.] LockAcquireExtended
  5.16%  postgres  postgres           [.] LockReleaseAll
  4.00%  postgres  postgres           [.] hash_seq_search
  3.84%  postgres  postgres           [.] LWLockRelease
  3.81%  postgres  postgres           [.] AllocSetAlloc
  3.23%  postgres  postgres           [.] LWLockAcquire
  2.55%  postgres  postgres           [.] SetupLockInTable
  1.90%  postgres  postgres           [.] AcquireExecutorLocks

INSERT:
 21.86%  postgres  postgres           [.] hash_search_with_hash_value
  6.36%  postgres  postgres           [.] hash_any
  4.95%  postgres  postgres           [.] AllocSetAlloc
  4.08%  postgres  postgres           [.] LWLockRelease
  3.83%  postgres  postgres           [.] LWLockAcquire
  3.26%  postgres  postgres           [.] SearchCatCache
  3.15%  postgres  postgres           [.] oid_cmp
  2.78%  postgres  postgres           [.] LockReleaseAll
  2.76%  postgres  postgres           [.] pg_qsort
  2.41%  postgres  postgres           [.] SearchCatCache1


-----Original Message-----
From: Tsunakawa, Takayuki/綱川 貴之 
Sent: Friday, July 13, 2018 2:49 PM
To: 'Amit Langote' <Langote_Amit_f8@lab.ntt.co.jp>; Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>; PostgreSQL mailing lists
<pgsql-hackers@postgresql.org>
Subject: RE: How to make partitioning scale better for larger numbers of partitions

From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
> For SELECT/UPDATE/DELETE, overhead of partitioning in the planning phase
> is pretty significant and gets worse as the number of partitions grows.
> I
> had intended to fix that in PG 11, but we could only manage to get part
> of
> that work into PG 11, with significant help from David and others.  So,
> while PG 11's overhead of partitioning during planning is less than PG
> 10's due to improved pruning algorithm, it's still pretty far from ideal,
> because it isn't just the pruning algorithm that had overheads.  In fact,
> PG 11 only removes the pruning overhead for SELECT, so UPDATE/DELETE still
> carry the overhead that was in PG 10.

David has submitted multiple patches for PG 12, one of which speeds up pruning of UPDATE/DELETE (I couldn't find it in
thecurrent CF, though.)  What challenges are there for future versions, and which of them are being addressed by
patchesin progress for PG 12, and which issues are untouched?
 

> The overheads I mention stem from
> the fact that for partitioning we still rely on the old planner code
> that's used to perform inheritance planning, which requires to lock and
> open *all* partitions.  We have so far been able to refactor just enough
> to use the new code for partition pruning, but there is much refactoring
> work left to avoid needlessly locking and opening all partitions.

I remember the issue of opening and locking all partitions was discussed before.  Does this relate only to the planning
phase?

Kato-san, could you try pgbench -M prepared?


Regards
Takayuki Tsunakawa