PostgreSQLでXML(2): インデックスによる効率化
さて、前回からの続きで、PostgreSQLでXMLを使ってみる…の件です。 今回は次のようなテーブルを前提とします。
CREATE TABLE testxml ( id SERIAL PRIMARY KEY, xmldoc XML );
コンテンツとしては、本サイトのXMLデータ(約1500件)を、 xmldocに突っ込んであります。idは連番です。
インデックスの必要性
PostgreSQLはなかなかよくできたデータベースで、 普通のSQLのデータベースサーバとしてみたときは、 結構無茶なSQLを書いても、結構な速度で検索してくれます。 まあ、それでも無茶なことをやり過ぎるとどんどん遅くなっていって、 データベースのチューニングが必須のものとなります。
で、XMLの話なんですが、XMLサポート機能は、 まだPostgreSQLに登場してそれほど時間が経っていないためか、 あるいはXMLデータ自体が重いデータが多いということもあってか(多分こちら)、 残念ながらこの「どんどん遅くなる」具合が通常のSQLに比べて早いように思います。 特に、どんどん重いXMLデータをデータベースに突っ込んでいったときに、 加速的に性能は悪化します (特に、メガバイト単位のXML文書などを大量に突っ込んだ場合)。
たとえば次のようなSQLを考えます。
SELECT xpath('//a/@href', xmldoc) FROM testxml
これは、documentsテーブルのxml_content列にあるXMLデータから、 <a href="....">のhref部分を全て検索するというSQLなわけですが、 このXPathの部分というのは結構なコストなわけです。
EXPLAIN ANALYZEしてみるとわかりますが、 Seq Scanで、XML全体を舐めて検索していることがわかります。 このサイトのようなそれほど大規模でないコンテンツでも、 約0.8秒の検索時間がかかっています。
testxml=# EXPLAIN ANALYZE xpath('//a/@href', xmldoc) FROM testxml;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on testxml (cost=0.00..459.47 rows=1558 width=32) (actual time=0.463..761.270 rows=1558 loops=1)
Total runtime: 766.578 ms
(2 行)
もしdocumentsが膨大な行数を持つ表だった場合、 もしくはXML文書自体が巨大だった場合、 単純に次のようなXMLを実行する感覚で上記のようなSELECTを投げてしまうと、 えらい目に合うことになります。
SELECT id FROM testxml
ちなみにこの場合だと、特にインデックスを貼っていなくても、 約8msで検索が完了しています。PostgreSQL、優秀ですね。
testxml=# EXPLAIN ANALYZE SELECT id FROM testxml;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on testxml (cost=0.00..455.58 rows=1558 width=4) (actual time=0.135..4.659 rows=1558 loops=1)
Total runtime: 7.885 ms
(2 行)
こういう性能上の問題になったときに何をするかといえばインデックスなわけです。 インデックスはPostgreSQLにおいてXMLでの検索を行う際に必須のものと言っていいでしょう。 というか、インデックスがなければ実用的な検索は難しいというのが実際に近い感覚かもしれません。
XMLデータに対するGINインデックス
PostgreSQLでは、XML中のXPathで指定される位置に対してインデックスを張ることが可能です。前回述べたとおり、XMLの検索結果は配列として扱われますので、配列に適したインデックスの形式であるGINインデックスを用いることが可能です。
例えば先程の例での documents テーブルの xpath('//title/text()', xmldoc) に対してGINインデックスを張る場合、次のようにすることができます。
CREATE INDEX testxml_title_idx ON testxml USING GIN ((xpath(E'//title/text()', xmldoc)::text[]));
インデックス利用の制約
さて、このインデックスを実際に利用するにはいくつかの制限があります。
インデックスが機能しない例(1): インデックスと異なるWHERE条件
まずは、XMLに限らずインデックスに関する一般的な注意点ですが、 インデックスはWHEREなどの条件に、 張ったインデックスそのままの状態で登場しない限り利用されません。
たとえば上のようなインデックスを張ったとしても、 次のようなSELECTに対してはインデックスは働きません。 次の例では、XHTML文書中に最初に現れる <title>の内容が'かーさむーちー'となっている文書のidという列を持ってこようとしています。
SELECT id FROM testxml WHERE (xpath(E'//title/text()', xmldoc)::text[])[1] = 'かーさむーちー'
実際に検索するとこのようになります。一件、こちらのページのデータが検索されて出てきました。
testxml=# SELECT id FROM testxml testxml-# WHERE (xpath(E'//title/text()', xmldoc)::text[])[1] = 'かーさむーちー'; id ------ 1549 (1 行)
このような検索をしようとしても、このWHEREにインデックスdocument_title_idxは利用されません。インデックスとして作成されているのはあくまで「xpath(E'/html/head/title/text()'::text, xml_document)::text[]」に対してであって、 そこに余計な「[1]」が付いたWHEREには、インデックスが利用されません。
EXPLAIN ANALYZEで調べてみると、次のようにSeq Scanが行われていることがわかります。結果も0.7秒弱かかっています。
testxml=# EXPLAIN ANALYZE SELECT id FROM testxml
testxml-# WHERE (xpath(E'//title/text()', xmldoc)::text[])[1] = 'かーさむーちー';
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on testxml (cost=0.00..463.37 rows=8 width=4) (actual time=680.177..683.947 rows=2 loops=1)
Filter: (((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[])[1] = 'かーさむーちー'::text)
Total runtime: 684.023 ms
(3 行)
インデックスが機能しない例(2): GINインデックスはany()に利用できない
さらに、「xpath(E'//title/text()'::text, xmldoc)::text[]」がそのままWHEREに出てくるようにした場合であっても、インデックスが利用されるとは限りません。次の例はインデックスを利用しません。
SELECT id FROM testxml WHERE 'かーさむーちー' = any(xpath(E'//title/text()', xmldoc)::text[])
EXPLAIN ANALYZEでも状況を確認できます。
testxml=# EXPLAIN ANALYZE SELECT id FROM testxml
testxml-# WHERE 'かーさむーちー' = any(xpath(E'//title/text()', xmldoc)::text[]);
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on testxml (cost=0.00..478.95 rows=76 width=4) (actual time=679.270..683.043 rows=2 loops=1)
Filter: ('かーさむーちー'::text = ANY ((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[]))
Total runtime: 683.116 ms
(3 行)
これはGINインデックスの問題です。GINインデックスが対応している演算子は、「=, @>, @<, &&」の4種類のみです。anyの処理はサポートされていません。従って上記の例は、次のように包含関係を示す演算子「@>」を使って書きなおせばインデックスを利用するようになります。
(特に、比較対象がスカラー 'かーさむーちー' から配列 '{かーさむーちー}' に変わっていることに注意してください！)
SELECT id FROM testxml
WHERE (xpath(E'//title/text()', xmldoc)::text[]) @> '{かーさむーちー}'
この場合は、EXPLAIN ANALYZEで "Bitmap Index Scan on testxml_title_idx" のように、インデックスが利用されているのがわかります。そして検索速度も約0.1msと、圧倒的に高速化されていることがわかるでしょう。
testxml=# EXPLAIN ANALYZE SELECT id FROM testxml
testxml-# WHERE (xpath(E'//title/text()', xmldoc)::text[]) @> '{かーさむーちー}';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on testxml (cost=4.27..11.89 rows=2 width=4) (actual time=0.053..0.059 rows=2 loops=1)
Recheck Cond: ((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[] @> '{かーさむーちー}'::text[])
-> Bitmap Index Scan on testxml_title_idx (cost=0.00..4.27 rows=2 width=0) (actual time=0.038..0.038 rows=2 loops=1)
Index Cond: ((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[] @> '{かーさむーちー}'::text[])
Total runtime: 0.141 ms
(5 行)
インデックスが機能しない例(3): 否定条件
その他にも、インデックスを利用するにはインデックス自体のもつ制限があります。 もっとも重要なことは、インデックスは「否定形の検索には利用できない」ことです。 つまり、先程の例を少し変更して、次のように<title>の内容が 'かーさむーちー' となっていない文書のidという列を持ってこようとした場合は、インデックスは利用されません。
SELECT id FROM testxml
WHERE NOT (xpath(E'//title/text()', xmldoc)::text[]) @> '{かーさむーちー}'
EXPLAIN ANALYZEで、Seq Scanとなっていることを確認できます。 当然ながら、速度も非常に遅くなっています。
testxml=# EXPLAIN ANALYZE SELECT id FROM testxml
testxml-# WHERE NOT (xpath(E'//title/text()', xmldoc)::text[]) @> '{かーさむーちー}';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on testxml (cost=0.00..463.37 rows=1556 width=4) (actual time=0.712..697.688 rows=1556 loops=1)
Filter: (NOT ((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[] @> '{かーさむーちー}'::text[]))
Total runtime: 702.464 ms
(3 行)
インデックスが機能しない例(4): LIKEによるパターンマッチング
GINインデックスはLIKEをサポートしません (通常のインデックスに利用されるB-treeは、ロケールの制限があるものの、 前方一致の場合にのみLIKEが利用できます)。 SQLの記述のしようがありませんので、こちらの例はありません。
もっとも、タイトルを示す<title>のように、一度しか出現しないと分かっているエレメントについては、次のように array_to_string() を用いて、通常のB-treeのインデックスを張ることも可能です(もちろん、本当に一度しか存在しないかどうかを、XML Schemaなどを用いて保証することが望ましい)。
CREATE INDEX testxml_title_btree_idx ON testxml USING BTREE (array_to_string(xpath(E'//title/text()'::text, xmldoc)::text[], ' '));
(追記) この例は、PostgreSQL 9.0ではエラーが出てインデックスを作成できません。 回避方法については第4回で検討しています。
こうすることで、次のようにして前方一致のLIKEを検索することができます (「か」で始まる項目を検索している)。 B-treeのインデックスが使われていることが、 "Bitmap Index Scan on testxml_title_btree_idx" から確認できますね。 検索も非常に高速です。
testxml=# SELECT id,
testxml=# array_to_string(xpath(E'//title/text()'::text, xmldoc)::text[], ' ')
testxml=# FROM testxml
testxml-# WHERE array_to_string(xpath(E'//title/text()'::text, xmldoc)::text[], ' ')
testxml-# LIKE 'か%'
id | array_to_string
------+----------------------
1199 | かつやのラー油カツ丼
1549 | かーさむーちー
(2 行)
testxml=# EXPLAIN ANALYZE SELECT id,
testxml=# array_to_string(xpath(E'//title/text()'::text, xmldoc)::text[], ' ')
testxml=# FROM testxml
testxml-# WHERE array_to_string(xpath(E'//title/text()'::text, xmldoc)::text[], ' ')
testxml-# LIKE 'か%'
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on testxml (cost=4.34..33.29 rows=8 width=36) (actual time=0.645..1.879 rows=3 loops=1)
Filter: (array_to_string((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[], ' '::text) ~~ 'か%'::text)
-> Bitmap Index Scan on testxml_title_btree_idx (cost=0.00..4.34 rows=8 width=0) (actual time=0.043..0.043 rows=3 loops=1)
Index Cond: ((array_to_string((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[], ' '::text) >= 'か'::text) AND (array_to_string((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[], ' '::text) > 'が'::text))
Total runtime: 1.986 ms
(5 行)
しかし、後方一致や中間一致のLIKE検索は、B-treeゆえにサポートされません。 通常のSeq Scanが行われることになります。
testxml=# EXPLAIN ANALYZE SELECT id,
testxml-# array_to_string(xpath(E'//title/text()'::text, xmldoc)::text[], ' ')
testxml-# FROM testxml
testxml-# WHERE array_to_string(xpath(E'//title/text()'::text, xmldoc)::text[], ' ')
testxml-# LIKE '%ー';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on testxml (cost=0.00..467.32 rows=12 width=36) (actual time=44.547..705.224 rows=51 loops=1)
Filter: (array_to_string((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[], ' '::text) ~~ '%ー'::text)
Total runtime: 705.544 ms
(3 行)
インデックスまわりのこれらの制限については、 後日また回避法などについて書こうと思います。