Q&A
実現したいこと
PostgreSQLを利用し、パーティションテーブルを利用し一定周期的に新しい断面にロードを行う。その後、パーティション管理テーブルを利用し、ビュー上から最新断面のパーティションテーブルを取得できるようにする。
前提
周期的な更新が入っても、常にテーブルにアクセスできるようにする。
発生している問題
idに0から順番に、contextに"A"を100個、partition_keyに"P01"を10万件入れた際、SELECT文での取得が遅くなってしまう。
パーティションテーブルから取得
sql
1postgres=# select * from T_TEST_P01; 2 id | context | partition_key 3-------+------------------------------------------------------------------------------------------------------+--------------- 4 0 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | P01 5 1 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | P01 6 2 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | P01 7... 8Time: 80.134 ms
パーティションテーブルをビュー化
sql
1postgres=# select * from V_TEST; 2 id | context 3-------+------------------------------------------------------------------------------------------------------ 4 0 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 5 1 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 6 2 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 7... 8Time: 682.392 ms
該当のソースコード
取得したいビュー
sql
1CREATE TABLE T_TEST ( 2 id int, 3 context varchar(100), 4 partition_key varchar(3) 5) PARTITION BY LIST(partition_key); 6 7CREATE TABLE T_TEST_P01 PARTITION OF T_TEST FOR VALUES IN('P01'); 8CREATE TABLE T_TEST_P02 PARTITION OF T_TEST FOR VALUES IN('P02'); 9CREATE TABLE T_TEST_P03 PARTITION OF T_TEST FOR VALUES IN('P03'); 10CREATE TABLE T_TEST_P04 PARTITION OF T_TEST FOR VALUES IN('P04'); 11CREATE TABLE T_TEST_P05 PARTITION OF T_TEST FOR VALUES IN('P05'); 12CREATE TABLE T_TEST_P06 PARTITION OF T_TEST FOR VALUES IN('P06'); 13 14CREATE VIEW V_TEST AS 15SELECT 16 id, 17 context 18FROM 19 T_TEST 20WHERE 21 partition_key = F_GET_PARTITION_KEY('T_TEST', 'A00'); 22 23CREATE OR REPLACE VIEW V_TEST AS 24SELECT 25 id 26FROM 27 T_TEST 28WHERE 29 partition_key = 'P01';
パーティションを管理するテーブル
sql
1CREATE TABLE T_PCTL ( 2 entity_name varchar(20), 3 partition_key varchar(3), 4 status char(3), 5 PRIMARY KEY ( entity_name, partition_key ) 6); 7 8CREATE INDEX T_PCTL_A01 ON T_PCTL (entity_name, status); 9CREATE INDEX T_PCTL_D01 ON T_PCTL (entity_name, partition_key); 10 11INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P01', 'A00'); 12INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P02', 'L02'); 13INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P03', 'L03'); 14INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P04', 'L04'); 15INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P05', 'L05'); 16INSERT INTO T_PCTL(entity_name, partition_key, status) VALUES('T_TEST', 'P06', 'L06');
パーティションテーブルがどの断面なのかを取得する関数
sql
1CREATE OR REPLACE FUNCTION F_GET_PARTITION_KEY(arg_entity_name text, arg_status text) RETURNS text 2AS $$ 3DECLARE 4 ret text; 5BEGIN 6 SELECT partition_key INTO ret FROM T_PCTL WHERE entity_name = arg_entity_name AND status = arg_status; 7 RETURN ret; 8END; 9$$ 10STABLE LANGUAGE plpgsql;
試したこと
vacuum full、vacuum analyzeを実行し統計情報の更新
=>実行時間は変化なし
実行計画の取得
sql
1postgres=# explain analyze select * from T_TEST_P01; 2 QUERY PLAN 3------------------------------------------------------------------------------------------------------------------- 4 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) 5 Planning Time: 0.033 ms 6 Execution Time: 13.368 ms 7(3 rows)
sql
1postgres=# explain analyze select * from V_TEST; 2 QUERY PLAN 3------------------------------------------------------------------------------------------------------------------------------------- 4 Append (cost=0.00..28475.03 rows=100005 width=105) (actual time=0.023..553.599 rows=100000 loops=1) 5 Subplans Removed: 5 6 -> 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) 7 Filter: ((partition_key)::text = f_get_partition_key('T_TEST'::text, 'A00'::text)) 8 Planning Time: 0.264 ms 9 Execution Time: 559.187 ms 10(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
回答1件
あなたの回答
tips
プレビュー
下記のような回答は推奨されていません。
このような回答には修正を依頼しましょう。