【PostgreSQL】パーティションテーブルのビュー化 取得遅い

Question

実現したいこと

PostgreSQLを利用し、パーティションテーブルを利用し一定周期的に新しい断面にロードを行う。その後、パーティション管理テーブルを利用し、ビュー上から最新断面のパーティションテーブルを取得できるようにする。

前提

周期的な更新が入っても、常にテーブルにアクセスできるようにする。

発生している問題

idに0から順番に、contextに"A"を100個、partition_keyに"P01"を10万件入れた際、SELECT文での取得が遅くなってしまう。
パーティションテーブルから取得

postgres=# select * from T_TEST_P01;
  id   |                                               context                                                | partition_key 
-------+------------------------------------------------------------------------------------------------------+---------------
     0 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | P01
     1 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | P01
     2 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | P01
...
Time: 80.134 ms

パーティションテーブルをビュー化

postgres=# select * from V_TEST;
  id   |                                               context                                                
-------+------------------------------------------------------------------------------------------------------
     0 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
     1 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
     2 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
...
Time: 682.392 ms

該当のソースコード

取得したいビュー

CREATE TABLE T_TEST (
  id int,
  context varchar(100),
  partition_key varchar(3)
) PARTITION BY LIST(partition_key);

CREATE TABLE T_TEST_P01 PARTITION OF T_TEST FOR VALUES IN('P01');
CREATE TABLE T_TEST_P02 PARTITION OF T_TEST FOR VALUES IN('P02');
CREATE TABLE T_TEST_P03 PARTITION OF T_TEST FOR VALUES IN('P03');
CREATE TABLE T_TEST_P04 PARTITION OF T_TEST FOR VALUES IN('P04');
CREATE TABLE T_TEST_P05 PARTITION OF T_TEST FOR VALUES IN('P05');
CREATE TABLE T_TEST_P06 PARTITION OF T_TEST FOR VALUES IN('P06');

CREATE VIEW V_TEST AS
SELECT
  id,
  context
FROM
  T_TEST
WHERE
  partition_key = F_GET_PARTITION_KEY('T_TEST', 'A00');

CREATE OR REPLACE VIEW V_TEST AS
SELECT
  id
FROM
  T_TEST
WHERE
  partition_key = 'P01';

パーティションを管理するテーブル

CREATE TABLE T_PCTL (
  entity_name varchar(20),
  partition_key varchar(3),
  status char(3),
  PRIMARY KEY ( entity_name, partition_key )
);

CREATE INDEX T_PCTL_A01 ON T_PCTL (entity_name, status);
CREATE INDEX T_PCTL_D01 ON T_PCTL (entity_name, partition_key);

INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P01', 'A00');
INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P02', 'L02');
INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P03', 'L03');
INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P04', 'L04');
INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P05', 'L05');
INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P06', 'L06');

パーティションテーブルがどの断面なのかを取得する関数

CREATE OR REPLACE FUNCTION F_GET_PARTITION_KEY(arg_entity_name text, arg_status text) RETURNS text
AS $$
DECLARE
  ret text;
BEGIN
  SELECT partition_key INTO ret FROM T_PCTL WHERE entity_name = arg_entity_name AND status = arg_status;
  RETURN ret;
END;
$$
STABLE LANGUAGE plpgsql;

試したこと

vacuum full、vacuum analyzeを実行し統計情報の更新
=>実行時間は変化なし

実行計画の取得

postgres=# explain analyze select * from T_TEST_P01;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on t_test_p01  (cost=0.00..2725.00 rows=100000 width=109) (actual time=0.005..9.024 rows=100000 loops=1)
 Planning Time: 0.033 ms
 Execution Time: 13.368 ms
(3 rows)

postgres=# explain analyze select * from V_TEST;
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..28475.03 rows=100005 width=105) (actual time=0.023..553.599 rows=100000 loops=1)
   Subplans Removed: 5
   ->  Seq Scan on t_test_p01 t_test_1  (cost=0.00..27975.00 rows=100000 width=105) (actual time=0.022..543.104 rows=100000 loops=1)
         Filter: ((partition_key)::text = f_get_partition_key('T_TEST'::text, 'A00'::text))
 Planning Time: 0.264 ms
 Execution Time: 559.187 ms
(6 rows)

=>パーティションキーがp02~p06までは実行計画上には表示されないためスキップされている
=>t_test_p01の取得にSeq Scanが使われているため、インデックスを確認したがT_TESTに"partition_key"、T_PCTLには"entity_name, status"を張っている

補足情報（FW/ツールのバージョンなど）

Linux version 5.4.0-131-generic
PostgreSQL 15.0 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit

Answer 1

関数を利用せず、サブクエリで利用したところ早く取得することができました。

CREATE OR REPLACE VIEW V3_TEST AS
SELECT
  id,
  context
FROM
  T_TEST
WHERE
  partition_key = (SELECT partition_key FROM T_PCTL WHERE entity_name = 'T_TEST' AND status = 'A00');

postgres=# EXPLAIN ANALYZE SELECT * FROM V3_TEST;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=1.18..3544.98 rows=100010 width=105) (actual time=0.018..23.603 rows=100000 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_pctl  (cost=0.00..1.18 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)
           Filter: (((entity_name)::text = 'T_TEST'::text) AND (status = 'A00'::bpchar))
           Rows Removed by Filter: 11
   ->  Seq Scan on t_test_p01 t_test_1  (cost=0.00..2975.00 rows=100000 width=105) (actual time=0.005..17.183 rows=100000 loops=1)
         Filter: ((partition_key)::text = ($0)::text)
   ->  Seq Scan on t_test_p02 t_test_2  (cost=0.00..13.75 rows=2 width=222) (never executed)
         Filter: ((partition_key)::text = ($0)::text)
   ->  Seq Scan on t_test_p03 t_test_3  (cost=0.00..13.75 rows=2 width=222) (never executed)
         Filter: ((partition_key)::text = ($0)::text)
   ->  Seq Scan on t_test_p04 t_test_4  (cost=0.00..13.75 rows=2 width=222) (never executed)
         Filter: ((partition_key)::text = ($0)::text)
   ->  Seq Scan on t_test_p05 t_test_5  (cost=0.00..13.75 rows=2 width=222) (never executed)
         Filter: ((partition_key)::text = ($0)::text)
   ->  Seq Scan on t_test_p06 t_test_6  (cost=0.00..13.75 rows=2 width=222) (never executed)
         Filter: ((partition_key)::text = ($0)::text)
 Planning Time: 1.247 ms
 Execution Time: 26.957 ms
(19 rows)
         
Time: 29.445 ms

※追記
問題としては、上のようなサンプルだと大丈夫なのですが、個人で開発している大規模なテーブルの場合は、vacuum full,vacuum analyzeしないと最適な実行手順(SELECT時にパーティションテーブルを指定する場合は関係なし)に行かないみたいで、、大量のデータを入れるようにvacuum autoなどの設定を見直しに注意したほうがいいですね・・・