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

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

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 -hlocalhost -Upostgres
postgres=# \timing
Timing is on.

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

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

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

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;

                                     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)