Performance of SELECT in a table partitioned into a lot

From: "Kato, Sho" <kato-sho(at)jp(dot)fujitsu(dot)com>
To: "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Performance of SELECT in a table partitioned into a lot
Date: 2018-12-21 08:43:47
Message-ID: 25C1C6B2E7BE044889E4FE8643A58BA963D9E48D@G01JPEXMBKW03
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 than another 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

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2018-12-21 08:45:24 Re: Speeding up creating UPDATE/DELETE generic plan for partitioned table into a lot
Previous Message Nagaura, Ryohei 2018-12-21 08:34:18 RE: [suggestion]support UNICODE host variables in ECPG