Thread: Performance of SELECT in a table partitioned into a lot

Performance of SELECT in a table partitioned into a lot

From
"Kato, Sho"
Date:
Hi,

I compared INSERT/UPDATE/DELETE/SELECT throughput with PostgreSQL and another dbms.
For INSERT/DELETE/UPDATE, PostgreSQL performance is superior, but for SELECT, PostgreSQL performance is slightly lower
thananother dbms.
 

Because information may be missing, it may be difficult, but do you know this reason?
Also, if you know where I need to find out, please let me know.

*table information(e.g. 8192 partitions, each partition has 1,000 records)* 

testdb=# \d test.accounts
                           Partitioned table "test.accounts"
  Column  |  Type   | Collation | Nullable |                  Default                   
----------+---------+-----------+----------+--------------------------------------------
 aid      | integer |           | not null | nextval('test.accounts_aid_seq'::regclass)
 abalance | integer |           |          | 
Partition key: RANGE (aid)
Indexes:
    "accounts_ix" btree (aid)
Number of partitions: 8192 (Use \d+ to list them.)

*interface*
JDBC(postgresql-42.2.4.jar)
Use PreparedStatement

*database tuning*
plan_cache_mode = force_custom_plan
max_worker_processes = 0
max_parallel_workers_per_gather = 0
max_parallel_workers = 0

*SQL*
SELECT abalance FROM test.accounts WHERE aid = $1;

$1 is random(npart * 1000)

*Other setting*
Benchmark is executed with a single session.

*Benchmark results*
I use master(commit 71a05b2232 Wed Dec 5) + v8 patch[1] + v1 patch[2].

npart PostgreSQL another dbms
----- ---------- ------------
0     6314.7     6580.3
2     5761.9     6390.6
4     5916       6279.3
8     5884.1     6000.7
16    5887.7     6296
32    5868.3     6274.4
64    5826.5     6248.6
128   5807.4     6208.9
256   5748.7     6241.4
512   5699.8     6204.6
1024  5625.9     6174.1
2048  5540.5     6159.3
4096  5393.3     6060
8192  5251.3     6093.4

[1]:https://www.postgresql.org/message-id/9d7c5112-cb99-6a47-d3be-cf1ee6862a1d@lab.ntt.co.jp
[2]:https://www.postgresql.org/message-id/CAKJS1f-=FnMqmQP6qitkD+xEddxw22ySLP-0xFk3JAqUX2yfMw@mail.gmail.com

regards,

Sho Kato