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