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 行)
では、次回からもさらにインデックスに関する話を続ける予定です。