PostgreSQLでXML(4): [補足] 9.0と8.4のインデックスに関する振る舞いの差

さて、PostgreSQLでXMLを使うシリーズです。

第2回でインデックスがないとPostgreSQLでのXML検索は性能悲惨、という話を書いたのですが、実はこのへんにはさらに深い沼が広がっているようです。

今回は小ネタで、私もまだきちんと把握出来ていない場所なのですが、 PostgreSQL 8.4とPostgreSQL 9.0で振る舞いの違う場所があったので、 第2回の補足、という位置づけです。

なお、今回も引き続き次のようなテーブルを前提とします。

CREATE TABLE testxml (
  id SERIAL PRIMARY KEY,
  xmldoc XML
);

コンテンツも引き続き、本サイトのXMLデータ(約1500件)を、 xmldocに突っ込んであります。idは連番です。

PostgreSQL 9.0でのXMLへのB-treeインデックス

第2回では、XML要素へのインデックスは通常配列を対象とするGINインデックスを用いるが、次のようなテクニックを使うことで、B-treeのインデックスを貼ることができ、前方一致検索が可能となる、ということを紹介しました。

CREATE INDEX testxml_title_btree_idx ON testxml
USING BTREE 
(array_to_string(xpath(E'//title/text()'::text, xmldoc)::text[], ' '));

ところが、第2回はPostgreSQL 8.4でテストしていたのですが、このインデックスはPostgreSQL 9.0ではエラーが出て作成できません。

testxml=# CREATE INDEX testxml_title_btree_idx ON testxml
testxml-# USING BTREE 
testxml-# (array_to_string(xpath(E'//title/text()'::text, xmldoc)::text[], ' '));
ERROR:  functions in index expression must be marked IMMUTABLE
testxml=#

私にはなぜarray_to_string()がIMMUTABLEでないかが理解出来ないのですが (ご存じの方、 タイトルのところの「ツイートする」ボタンでつぶやいて教えてくださいませ…)、 エラーが出るのは仕方がありません。

回避するには、次のようなIMUUTABLE指定した関数array_to_string2()を作り…。

CREATE FUNCTION array_to_string2(text[]) RETURNS text
    LANGUAGE sql 
    IMMUTABLE STRICT 
    AS $$
        SELECT array_to_string($1, ' ') 
    $$

これを使ってインデックスを作ればいいんですけどね。

CREATE INDEX testxml_title_btree_idx ON testxml
USING BTREE 
(array_to_string2(xpath(E'//title/text()'::text, xmldoc)::text[]));

まあ、関数作ってまでやるくらいなら、array_to_string()を使わずに、配列要素への参照を直接用いて、配列の最初の要素を返す次のような関数array_first()を使ったほうがシンプルそうですが。

CREATE FUNCTION array_first(text[]) RETURNS text
    LANGUAGE sql 
    IMMUTABLE STRICT
    AS $$ 
        SELECT $1[1] 
    $$;

なんと言っても、いくら私が解せなくても、array_to_string()がIMMUTABLEでないとエラーを返すPostgreSQLの判断にも何らかの理由があるのかも知れないし…。

ちなみに、そもそもなぜ、今までこの例と同じ(xpath('...', ...)::text[])[1]にインデックスを貼らなかったのかというと、実際には次のようにCREATE INDEXがエラーを返してきて、インデックスが貼れないためです。 何故でしょうね? 関数を作る以外に何かスマートな回避の方法をご存じの方、教えてください。

testxml=# CREATE INDEX testxml_title_btree2_idx ON testxml
testxml-# USING BTREE 
testxml-# ((xpath(E'//title/text()'::text, xmldoc)::text[])[1]);
ERROR:  syntax error at or near "["
行 3: ((xpath(E'//title/text()'::text, xmldoc)::text[])[1]);
                                                       ^

まあとりあえず、このarray_first()を使えば普通にインデックスを作ることができます。めでたしめでたし。

testxml=# CREATE INDEX testxml_title_btree2_idx ON testxml
testxml-# USING BTREE 
testxml-# (array_first(xpath(E'//title/text()'::text, xmldoc)::text[]));
CREATE INDEX

もちろん、検索の際もarray_first()を使ってあげないとインデックスは適用されませんよ。こんな感じに。B-treeなので、LIKEの前方一致検索もできます。

SELECT id,
array_first(xpath(E'//title/text()'::text, xmldoc)::text[])
FROM testxml
WHERE array_first(xpath(E'//title/text()'::text, xmldoc)::text[]) 
LIKE '謎%'

念のためEXPLAIN ANALYZEしてみましょうか。きちんと、testxml_title_btree2_idxというインデックスにBitmap Index Scanを行っていることが確認できますね。

testxml=# SELECT id,
testxml-# array_first(xpath(E'//title/text()'::text, xmldoc)::text[])
testxml-# FROM testxml
testxml-# WHERE array_first(xpath(E'//title/text()'::text, xmldoc)::text[]) 
testxml-# LIKE '謎%';
  id  |            array_first             
------+------------------------------------
  309 | 謎の赤い虫(?)
  559 | 謎肉ヌードル確保
 1221 | 謎肉ヌードル「ミートキング」登場!
(3 行)
testxml=# EXPLAIN ANALYZE SELECT id,
testxml-# array_first(xpath(E'//title/text()'::text, xmldoc)::text[])
testxml-# FROM testxml
testxml-# WHERE array_first(xpath(E'//title/text()'::text, xmldoc)::text[]) 
testxml-# LIKE '謎%';
                                                                                           QUERY PLAN                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on testxml  (cost=4.34..31.90 rows=8 width=36) (actual time=0.784..1.835 rows=3 loops=1)
   Filter: (((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[])[1] ~~ '謎%'::text)
   ->  Bitmap Index Scan on testxml_title_btree2_idx  (cost=0.00..4.34 rows=8 width=0) (actual time=0.040..0.040 rows=3 loops=1)
         Index Cond: ((((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[])[1] >= '謎'::text) AND (((xpath('//title/text()'::text, xmldoc, '{}'::text[]))::text[])[1] < '謏'::text))
 Total runtime: 1.954 ms
(5 行)

では、次回からもさらにインデックスに関する話を続ける予定です。

関連記事