ひとりごと
DB初心者が自身の備忘を兼ねて、Postgresqlで学んだことをアウトプットしようと思います
今回の目的
日々の業務でお客様が使用しているDBをいじることが多いのですが、
特定のテーブルが年々肥大化しており、ちょっとしたselectの応答が遅くなっていて困っています。（え、こんなに待たなきゃいけないの？ってくらい）
何か手段はないのかな？と調べたところ、Partitionというテーブルを分割する機能があるようなので、そちらの効果を簡単に検証してみて、お客様の提案活動に繋げてみたいな。という思いです
Partitionとはなんぞ
簡単にはPartitionについて、調べてみました。
（といっても、以下の記事がわかりやすいので、こちらでは紹介だけ）
パーティショニングの概要
1.partitionの種類
| No | 種類 | 特徴 |
|---|---|---|
| 1 | range | 202010~202011といった範囲（期間）ごとにテーブルを分割 |
| 2 | list | 東京都、埼玉県、神奈川県といった種類ごとにテーブルを分割 |
| 3 | hash | 指定した分割数でテーブルを均等になるように分割 |
2.hash partitionについてもう少し詳しく
「指定した分割数でテーブルを均等になるように分割 」って何？ですよね
例えば、こんな感じのテーブルがあったとします。
| id | 所属部署 | 社員名 |
|---|---|---|
| 1 | 経理 | 太郎 |
| 2 | 営業 | 次郎 |
| 3 | 総務 | 三郎 |
| 4 | 総務 | 三郎 |
| 5 | 総務 | 三郎 |
これをHash partitionで「３つのpartitonに分ける」とする場合、
以下のような考え方で３テーブルに分別されます。
- 1は3で割ると1余るのでテーブルAに格納
- 2は3で割ると2余るのでテーブルBに格納
- 3は3で割ると0余るのでテーブルCに格納
- 4は3で割ると1余るのでテーブルAに格納
対象idを分割テーブル数で割ったあまりを使って、partitionテーブルに分別していく感じ
実際にpartitionテーブルを作って、性能比較してみる
まずは２つのテーブルを用意
partitionを有効にする以外は全く同じカラム・レコードのテーブルを用意しました
#省略していますが、各1000レコード用意してます。
-
partition_player
id 所属部署 社員名 1 営業 太郎 2 営業 次郎 3 総務 三郎 4 総務 三郎 5 総務 三郎 1000 総務 千郎 パーティション作成のSQL
CREATE TABLE player_01 PARTITION OF partition_player FOR VALUES WITH (modulus 3, remainder 0) CREATE TABLE player_02 PARTITION OF partition_player FOR VALUES WITH (modulus 3, remainder 0) CREATE TABLE player_03 PARTITION OF partition_player FOR VALUES WITH (modulus 3, remainder 0)
-
employee
id 所属部署 社員名 1 営業 太郎 2 営業 次郎 3 総務 三郎 4 総務 三郎 5 総務 三郎 1000 総務 千郎
用意したテーブルに対して同じクエリを投げてみる
- 実行したクエリ
explain analyze select * from practice.players_partitions where id < 100
-
partitionあり統計
"Append (cost=0.00..20.68 rows=97 width=70) (actual time=0.060..0.350 rows=99 loops=1)" " -> Seq Scan on players_01 players_partitions_1 (cost=0.00..5.96 rows=29 width=70) (actual time=0.058..0.142 rows=30 loops=1)" " Filter: (id < 100)" " Rows Removed by Filter: 207" " -> Seq Scan on players_02 players_partitions_2 (cost=0.00..7.04 rows=31 width=70) (actual time=0.017..0.088 rows=32 loops=1)" " Filter: (id < 100)" " Rows Removed by Filter: 211" " -> Seq Scan on players_03 players_partitions_3 (cost=0.00..7.20 rows=37 width=71) (actual time=0.024..0.093 rows=37 loops=1)" " Filter: (id < 100)" " Rows Removed by Filter: 219" "Planning Time: 0.756 ms" "Execution Time: 0.427 ms"
-
partitionなし統計
"Seq Scan on players (cost=0.00..19.20 rows=99 width=70) (actual time=0.070..0.325 rows=99 loops=1)" " Filter: (id < 100)" " Rows Removed by Filter: 637" "Planning Time: 0.128 ms" "Execution Time: 0.364 ms"
- 疑問
なぜか？全てのpartitionga読み込まれてる。なぜだ？
⇨Hash Partitionなので、id <100としても、先ほど説明したとおり、
⇨対象idを分割テーブル数で割ったあまりを使って、partitionテーブルに分別なので、partition1~3それぞれにid<100が存在している。
このことから、Hashで有効な検索方法は以下だと改めて理解しました
-
実行したクエリ（修正版）
explain analyze select * from practice.players_partitions where id = 100
-
partitionあり統計（修正版実行時）
ちゃんと一つのPartitionだけが読み込まれましたね！"Seq Scan on players_03 players_partitions (cost=0.00..7.20 rows=1 width=71) (actual time=0.024..0.084 rows=1 loops=1)"
" Filter: (id = 100)"
" Rows Removed by Filter: 255"
"Planning Time: 2.021 ms"
"Execution Time: 0.111 ms"
おわりに
以上です！誰かの理解の役に少しでも立つと嬉しいです（間違いがあったら指摘ください