スクリプト類とメニューボタン

トップ ソフト 雑記 日記 リンク

2011年12月23日金曜日

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

PostgreSQL Advent Calendar」23日目です。

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

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

Twitterでぼやいてみたら、9.1からは「パーティション全体のソートの最適化」が行われていることを教えてもらいました。そういえば、9.1を使っていたんだった。ということで、追試してみました。
試してみた環境は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 の中で未実装のもの」です。

0 件のコメント:

コメントを投稿

広告