道化的プログラミング

PostgreSQLのパーティションをネストした時のパフォーマンス

仕事でPostgreSQL 10の宣言的パーティショニングをネストさせて使っているのだが、どうもクエリが遅いので検証してみた。

PostgreSQL インストール

-p の左側はお好みで。

docker run --name postgres10 -p 5432:5432 -d postgres:10

パーティション作りまくる

構成は以下の通り

  • single_partitioned
    • フラットに single_partitioned_0 ~ single_partitioned_9999 の10000パーティション
  • nested_partitioned
    • nested_partitioned_0 ~ nested_partitioned_99 が1段階目
    • さらにそれぞれに nested_partitioned_${n}_0 ~ nested_partitioned_${n}_99 が存在し100 * 100 = 10000パーティション

groovyでこんな感じで作った。

import groovy.sql.Sql

@GrabConfig(systemClassLoader=true)
@Grab(group='org.postgresql', module='postgresql', version='42.2.5')

def main(){
  def sql = Sql.newInstance(
    'jdbc:postgresql://localhost/postgres',
    'postgres',
    'postgres',
    'org.postgresql.Driver')

  sql.execute('DROP TABLE IF EXISTS single_partitioned;DROP TABLE IF EXISTS nested_partitioned;')

  // フラットに10000パーティション
  sql.execute('CREATE TABLE single_partitioned(group_id int, seq int) PARTITION BY LIST (group_id)')
  (0 .. 9999).each { partitionNum ->
    sql.execute("""CREATE TABLE single_partitioned_${partitionNum}
      PARTITION OF single_partitioned FOR VALUES IN (${partitionNum})""".toString())
  }

  // 100 x 100で10000パーティション
  sql.execute('CREATE TABLE nested_partitioned(group_id int, seq int) PARTITION BY LIST (group_id)')
  (0 .. 99).each { partitionNum ->
    sql.execute("""CREATE TABLE nested_partitioned_${partitionNum}
      PARTITION OF nested_partitioned FOR VALUES IN (${partitionNum}) PARTITION BY RANGE (seq)""".toString())
    (0 .. 99).each { n ->
      sql.execute("""CREATE TABLE nested_partitioned_${partitionNum}_${n}
            PARTITION OF nested_partitioned_${partitionNum} FOR VALUES FROM (${n * 100}) TO (${n * 100 + 99})""".toString())
    }
  }

}
main()

性能測定

psqlで接続し、パーティショニングされたテーブルに対するSELECTの実行計画生成の時間を見る。

$ psql -hlocalhost -Upostgres
postgres=# \timing
Timing is on.

1. フラットに10000パーティション

explain select count(*) from single_partitioned where group_id = 10 and seq = 100;

2. 100 x 100で10000パーティション

explain select count(*) from nested_partitioned where group_id = 10 and seq = 100;

3. 100 x 100で10000パーティションの1段階目のパーティションテーブル名を直接指定

explain select count(*) from nested_partitioned_10 where group_id = 10 and seq = 100;

4. 100 x 100で10000パーティションの1段階目の全パーティションテーブル名を直接指定

explain select count(*) from (
  select * from nested_partitioned_0 union all
  select * from nested_partitioned_1 union all
  -- 中略
  select * from nested_partitioned_99) x where group_id = 10 and seq = 100;

結果

1 2 3 4
1回目 452.145 ms 501.850 ms 8.116 ms 685.204 ms
2回目 196.438 ms 234.407 ms 1.710 ms 213.472 ms
3回目 188.596 ms 224.175 ms 8.287 ms 226.871 ms

2, 3, 4 は出力される実行計画が全て同じになる。つまり、実行計画の生成そのものに時間がかかる。

                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Aggregate  (cost=43.90..43.91 rows=1 width=8)
   ->  Append  (cost=0.00..43.90 rows=1 width=0)
         ->  Seq Scan on nested_partitioned_10_1  (cost=0.00..43.90 rows=1 width=0)
               Filter: ((group_id = 10) AND (seq = 100))
(4 rows)

まとめ

  • わずかながらフラットな構成のほうが速いが、ネストした分も含めたパーティションの総数によるところが大きい。
  • パーティションの子テーブル名を直接指定するほうが断然速い。遅いケースが0.5秒前後となるとさすがに無視できないケースも多そう。
  • 3. があまりにも速いのでいっそ全指定したところ、親テーブルのみを参照した場合と同等の性能になってしまった。
  • パーティションをネストさせるような場合、1段階目のパーティションは値からテーブル名の特定が容易な構成にしておくと速度を稼ぎやすそう。

なんだかこれではパーティショニングに意味がないような感じだが、それでも以下のメリットはある。

  • 子テーブルのカラム構成が親テーブルと同じであることが保証される
  • パーティショニング対象のカラムの値が子テーブルに対して保証される
  • 速度にシビアな場面でなければ、親テーブルに対するシンプルなSQLでクエリできる