日記: 12月 2011

「PostgreSQL Advent Calendar」23日目です。

PostgreSQLにはパーティショニングテーブルというものがあります。今日はそのお話。
「カラム定義が同じ複数のテーブルに、データを分割して格納できる」機能といいますか、「テーブルに親子関係を持たせられる」といいますか、詳しくは、Let's PostgreSQLを参照。（丸投げ）

拙作「ふぁぼるっく」では、バックエンドのデータベースとしてPostgreSQLを採用しています。Twitterのつぶやきデータが大量に貯まり、SSDに載りきらないため、新しいデータはSSD上のテーブルスペース、古いデータはHDD上のテーブルスペースと分けて保存しています。
そこで検索は横断的にやりたいということで、パーティショニングテーブルを使っています。
しかし、どうにも重たい。要求は新しいもの順で表示するので、SSDだけにアクセスし、データが無ければHDDを見に行くような動きをしてほしいのです。

Twitterでぼやいてみたら、9.1からは「パーティション全体のソートの最適化」が行われていることを教えてもらいました。そういえば、9.1を使っていたんだった。ということで、追試してみました。

PostgreSQLで期間ごとに区切ってパーティショニングテーブルを使っているんだけど、その期間をORDER BYに入れた場合、小さい部分の受け持ちテーブルから順に見に行ってくれたりするんだろうか？いやー、この遅さなら、見てくれてないんだろうな。
— おさ (@osapon) 12月 15, 2011

たとえば、Twitterのstatus_idの大きい順に指定件数だけ取ってきたいんだけど、古いテーブルも見てるんならやっぱり遅いよな。アプリ側で各テーブルを順に見て、指定件数分取得できるまで古いテーブルを掘り返す方が速いんだろうか。
— おさ (@osapon) 12月 15, 2011

それってアプリ側でやることじゃないよなぁ。せっかく制約も掛けて、そのテーブルに含まれるstatus_idが分かってるんだからさ。
— おさ (@osapon) 12月 15, 2011

EXPLAINの結果はどうでしょうか？ 9.1ではパーティションのORDER BYが強化されています→ bit.ly/q5jnOV RT @osapon: 期間ごとに区切ってパーティショニングテーブルを使っているんだけど、その期間をORDER BYに入れた場合…
— 日本PostgreSQLユーザ会 (@PostgreSQL_JP) 12月 15, 2011

@PostgreSQL_JP 9.1を使っているんですが、他のテーブルとJOINしたりしてるので、そのせいか全テーブルを見に行っちゃってるんですよね。条件減らして、JOINしなければうまくソートしてくれました。もう少しSQLを調整してみます。
— おさ (@osapon) 12月 15, 2011

@osapon なるほどなるほど。他のテーブルも含めた絞り方がキーなんですね。
— 日本PostgreSQLユーザ会 (@PostgreSQL_JP) 12月 15, 2011

PostgreSQL.1の「パーティション全体のソートの最適化」が無効になる条件判明。SELECT a.*,b.* FROM A JOIN B ON ～ WHERE B.hoge ORDER BY A.idでB.hogeにインデックスが無い時。うー、その項2値しか入らないんよ。
— おさ (@osapon) 12月 15, 2011

試してみた環境はPostgreSQL9.1.2、テーブル構成はこんな感じ。

CREATE TABLE t_parent
(
id smallint NOT NULL, -- 社員番号
kind smallint NOT NULL, -- 役職
fullname text NOT NULL, -- 名前
CONSTRAINT t_parent_pkey PRIMARY KEY (id )
)
WITH (
OIDS=FALSE
);
ALTER TABLE t_parent
OWNER TO postgres;
COMMENT ON COLUMN t_parent.id IS '社員番号';
COMMENT ON COLUMN t_parent.kind IS '役職';
COMMENT ON COLUMN t_parent.fullname IS '名前';

CREATE TABLE t_child10
(
-- 継承 from table t_parent:  id smallint NOT NULL,
-- 継承 from table t_parent:  kind smallint NOT NULL,
-- 継承 from table t_parent:  fullname text NOT NULL,
CONSTRAINT t_child10_pkey PRIMARY KEY (id ),
CONSTRAINT id_chk_1 CHECK (id >= 10 AND id < 20)
)
INHERITS (t_parent)
WITH (
OIDS=FALSE
);
ALTER TABLE t_child10
OWNER TO postgres;

CREATE INDEX t_child10_kind_idx
ON t_child10
USING btree
(kind );

CREATE TABLE t_child20
(
-- 継承 from table t_parent:  id smallint NOT NULL,
-- 継承 from table t_parent:  kind smallint NOT NULL,
-- 継承 from table t_parent:  fullname text NOT NULL,
CONSTRAINT t_child20_pkey PRIMARY KEY (id ),
CONSTRAINT id_chk_2 CHECK (id >= 20 AND id < 30)
)
INHERITS (t_parent)
WITH (
OIDS=FALSE
);
ALTER TABLE t_child20
OWNER TO postgres;

CREATE INDEX t_child20_kind_idx
ON t_child20
USING btree
(kind );

INSERT INTO t_child10 VALUES (10, 1, '社長');
INSERT INTO t_child10 VALUES (11, 2, '部長A');
INSERT INTO t_child10 VALUES (12, 2, '部長B');
INSERT INTO t_child10 VALUES (16, 3, '課長A');
INSERT INTO t_child10 VALUES (19, 4, '社員A');
INSERT INTO t_child20 VALUES (25, 2, '部長C');
INSERT INTO t_child20 VALUES (23, 3, '課長B');
INSERT INTO t_child20 VALUES (27, 3, '課長C');
INSERT INTO t_child20 VALUES (24, 4, '社員B');
INSERT INTO t_child20 VALUES (26, 4, '社員C');
INSERT INTO t_child20 VALUES (28, 4, '社員D');

親テーブルt_parent、子テーブルt_child10、t_child20。
主キーはそれぞれidの社員番号。t_child10はidが10代、t_child20は20代という制約が付いている。
そのほか、kindの役職にインデックスを張っている。

このとき

EXPLAIN (COSTS OFF) SELECT *
FROM t_parent
WHERE id >= 15
ORDER BY id
LIMIT 2

とidが15以上の者、id順で上位2件を取得すると
Limit
-> Result
        -> Merge Append
              Sort Key: public.t_parent.id
              -> Index Scan using t_parent_pkey on t_parent
                    Index Cond: (id >= 15)
              -> Index Scan using t_child10_pkey on t_child10 t_parent
                    Index Cond: (id >= 15)
              -> Index Scan using t_child20_pkey on t_child20 t_parent
                    Index Cond: (id >= 15)
となる。このMerge Appendが最適化が行われた状態。

つぎに

EXPLAIN (COSTS OFF) SELECT *
FROM t_parent
WHERE kind = 2
ORDER BY id
LIMIT 2

とkindが2の者、id順で上位2件を取得すると
Limit
-> Sort
        Sort Key: public.t_parent.id
        -> Result
              -> Append
                    -> Index Scan using t_parent_pkey on t_parent
                          Filter: (kind = 2)
                    -> Index Scan using t_child10_kind_idx on t_child10 t_parent
                          Index Cond: (kind = 2)
                    -> Index Scan using t_child20_kind_idx on t_child20 t_parent
                          Index Cond: (kind = 2)
となり、全テーブルを見に行くようになってしまいました。

（追記9:53 ANALYZEかけたらMerge Appendになりました。）

いろいろ試してみたのですが、テーブルの設計がかなり物を言うようです。あとは頻繁にANALYZEをかける必要がありそう。
実際に使用している環境では、一つのテーブルが数千万行、また正規化しまくっていてJOINでつないでいるテーブルも数百万行あり、「パーティション全体のソートの最適化」が行われませんでした。

プランナが賢くなるまで、もう少し辛抱のようです。

24日目はt_motookさんの「SQLFeatureNotSupportedException - JDBC4.0 API の中で未実装のもの」です。

12月 23, 2011

PostgreSQL 9.1でのパーティション全体のソートの最適化

12月 03, 2011

zabbixで外部チェックアイテムを作る