PostgreSQLのtext型に対するINDEX
PostgreSQLのtext型のカラムに対してB-treeインデックスを作成した場合、そのカラムに大きな文字列がINSERTされるとエラーとなります。
(本記事はPostgreSQL 12.5 での確認結果を元に記載しています)
たとえば下記のようなテーブルとインデックスを用意して
CREATE TABLE table1 (
value text
);
CREATE INDEX ON table1(value);
大きなテキストをINSERTするとエラーになることが確認できます。
INSERT INTO table1 VALUES(repeat('a', 300000));
testdb=# INSERT INTO table1 VALUES(repeat('a', 300000));
ERROR: index row size 3456 exceeds btree version 4 maximum 2704 for index "table1_value_idx"
DETAIL: Index row references tuple (0,1) in relation "table1".
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
なお、テキストのサイズによってエラーメッセージが異なります。
INSERT INTO table1 VALUES(repeat('a', 1000000));
testdb=# INSERT INTO table1 VALUES(repeat('a', 1000000));
ERROR: index row requires 11464 bytes, maximum size is 8191
大きなテキストに対してインデックスを作成する方法
text型にインデックスを張りたいようなことは、ほとんど無いのではと思います。あるとすれば、異常値となるようなものも全て格納しておいたうえで、何かと突合したい、、みたいなシチュエーションでしょうか。
そういった場合のために、この問題を回避して大きなテキストに対してインデックスを作成する方法をいくつか試してみます。
インデックスが効いているかを含めて確認するために、まずはテストデータを用意します。
CREATE TABLE table1 (
id integer,
value text
);
-- 0文字から999文字のデータを10,000,000件
INSERT INTO table1
SELECT
seq,
repeat(seq::text, seq % 1000)
FROM
generate_series(1, 10000000) AS seq;
-- B-treeインデックスだとエラーになるデータ
INSERT INTO table1 VALUES(-1, repeat('a', 300000));
INSERT INTO table1 VALUES(-2, repeat('a', 1000000));
いったんインデックス無しの状態で実行計画を見てみます。
SELECT id FROM table1 WHERE value = '1';
testdb=# EXPLAIN ANALYZE SELECT id FROM table1 WHERE value = '1';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..515777.44 rows=1 width=4) (actual time=3106.957..3110.776 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on table1 (cost=0.00..514777.34 rows=1 width=4) (actual time=2072.206..3104.169 rows=0 loops=3)
Filter: (value = '1'::text)
Rows Removed by Filter: 3333334
Planning Time: 0.058 ms
Execution Time: 3110.792 ms
(8 rows)
Time: 3111.104 ms (00:03.111)
Seq Scanでcostが1000.00..515777.44、実行時間が約3秒でした。
(大まかな数字が知りたいだけなので、キャッシュクリア＋測定を繰り返し行った結果で平均取る、、といったようなことは今回しません)
md5関数インデックス
エラーメッセージにもConsider a function index of an MD5 hash of the value, or use full text indexing.とあった通り、md5関数を利用した関数インデックスを利用することで、MD5によるハッシュ値の32文字に制限されるので、大きな文字列でもエラーになることなくインデックスが作成できます。
CREATE INDEX ON table1(md5(value));
testdb=# CREATE INDEX ON table1(md5(value));
CREATE INDEX
Time: 94736.893 ms (01:34.737)
このインデックスを利用するためには、明示的にWHERE句としてmd5関数を指定する必要があります。
また、コリジョンの可能性があるため、md5関数を利用しない条件も一緒に指定する必要があります。ちょっと面倒です。
SELECT id FROM table1 WHERE value = '1' AND md5(value) = md5('1');
testdb=# EXPLAIN ANALYZE SELECT id FROM table1 WHERE value = '1' AND md5(value) = md5('1');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on table1 (cost=1819.56..146875.59 rows=1 width=4) (actual time=1.648..1.650 rows=1 loops=1)
Recheck Cond: (md5(value) = 'c4ca4238a0b923820dcc509a6f75849b'::text)
Filter: (value = '1'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on table1_md5_idx (cost=0.00..1819.56 rows=50000 width=0) (actual time=0.844..0.845 rows=1 loops=1)
Index Cond: (md5(value) = 'c4ca4238a0b923820dcc509a6f75849b'::text)
Planning Time: 0.096 ms
Execution Time: 1.670 ms
(8 rows)
Time: 12.226 ms
関数インデックスが利用されBitmap Index Scanとなり、costが1819.56..146875.59、実行時間が約0.012秒と大幅に早くなりました。
B-treeの際にエラーになった大きなテキストも問題なく検索できます。
testdb=# SELECT id FROM table1 WHERE value = repeat('a', 1000000) AND md5(value) = md5(repeat('a', 1000000));
id
----
-2
(1 row)
Time: 54.118 ms
インデックスのサイズは590,536,704バイトでした。
testdb=# SELECT *, pg_relation_size(indexname::regclass) FROM pg_indexes WHERE tablename = 'table1';
schemaname | tablename | indexname | tablespace | indexdef | pg_relation_size
------------+-----------+----------------+------------+-----------------------------------------------------------------------+------------------
public | table1 | table1_md5_idx | | CREATE INDEX table1_md5_idx ON public.table1 USING btree (md5(value)) | 590536704
(1 row)
Time: 13.024 ms
substring関数インデックス
substring関数で文字数を制限することでも、md5関数と同じような効果が得られます。
md5と同じように32文字で作ってみます。
CREATE INDEX ON table1(substring(value from 1 for 32));
testdb=# CREATE INDEX ON table1(substring(value from 1 for 32));
CREATE INDEX
Time: 78851.593 ms (01:18.852)
md5関数を利用した時と同じように、関数インデックスと同じ条件をWHERE句に追加してあげます。
SELECT id FROM table1 WHERE value = '1' AND substring(value from 1 for 32) = substring('1' from 1 for 32);
testdb=# EXPLAIN ANALYZE SELECT id FROM table1 WHERE value = '1' AND substring(value from 1 for 32) = substring('1' from 1 for 32);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on table1 (cost=1815.56..146871.59 rows=1 width=4) (actual time=6.278..6.280 rows=1 loops=1)
Recheck Cond: ("substring"(value, 1, 32) = '1'::text)
Filter: (value = '1'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on table1_substring_idx (cost=0.00..1815.56 rows=50000 width=0) (actual time=6.267..6.267 rows=1 loops=1)
Index Cond: ("substring"(value, 1, 32) = '1'::text)
Planning Time: 0.092 ms
Execution Time: 6.302 ms
(8 rows)
Time: 6.855 ms
関数インデックスが利用されBitmap Index Scanとなり、costが1815.56..146871.59、実行時間が約0.006秒と大幅に早くなりました。
md5の時と同じ効果が得られたことがわかります。
B-treeの際にエラーになった大きなテキストも問題なく検索できます。
testdb=# SELECT id FROM table1 WHERE value = repeat('a', 1000000) AND substring(value from 1 for 32) = substring(repeat('a', 1000000) from 1 for 32);
id
----
-2
(1 row)
Time: 54.850 ms
インデックスのサイズは590,536,704バイトでした。md5と文字数をあわせたのもあってか、md5で作った時とサイズはほとんど変わりません。
testdb=# SELECT *, pg_relation_size(indexname::regclass) FROM pg_indexes WHERE tablename = 'table1';
schemaname | tablename | indexname | tablespace | indexdef | pg_relation_size
------------+-----------+----------------------+------------+--------------------------------------------------------------------------------------------+------------------
public | table1 | table1_substring_idx | | CREATE INDEX table1_substring_idx ON public.table1 USING btree ("substring"(value, 1, 32)) | 588972032
(1 row)
Time: 2.016 ms
当然、substringで切り出す文字数を減らすとインデックスサイズも減らせます。
試しに10文字でやってみます。
CREATE INDEX ON table1(substring(value from 1 for 10));
testdb=# CREATE INDEX ON table1(substring(value from 1 for 10));
CREATE INDEX
Time: 66769.009 ms (01:06.769)
実行計画としては10文字でもあまり変わらないように見えます。
testdb=# EXPLAIN ANALYZE SELECT id FROM table1 WHERE value = '1' AND substring(value from 1 for 10) = substring('1' from 1 for 10);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on table1 (cost=1147.44..146203.47 rows=1 width=4) (actual time=0.039..0.040 rows=1 loops=1)
Recheck Cond: ("substring"(value, 1, 10) = '1'::text)
Filter: (value = '1'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on table1_substring_idx (cost=0.00..1147.43 rows=50000 width=0) (actual time=0.033..0.033 rows=1 loops=1)
Index Cond: ("substring"(value, 1, 10) = '1'::text)
Planning Time: 0.091 ms
Execution Time: 0.060 ms
(8 rows)
Time: 0.456 ms
インデックスサイズは590,536,704バイトから315,318,272バイトに減りました。
testdb=# SELECT *, pg_relation_size(indexname::regclass) FROM pg_indexes WHERE tablename = 'table1';
schemaname | tablename | indexname | tablespace | indexdef | pg_relation_size
------------+-----------+----------------------+------------+--------------------------------------------------------------------------------------------+------------------
public | table1 | table1_substring_idx | | CREATE INDEX table1_substring_idx ON public.table1 USING btree ("substring"(value, 1, 10)) | 315318272
(1 row)
Time: 1.354 ms
まとめ
md5やsubstringを使って文字数を制限したインデックスを作成することで、大きなテキストがインデックス作成でエラーになることを回避し、インデックスを使った検索で性能を上げられることがわかりました。
md5とsubstringだと、md5の方が実行に時間がかかります。これはmd5は文字列全体を見てハッシュ計算するのに対し、substringは一部分の文字しかみないためです。
そのため、インデックス作成にかかる時間もmd5の方が長いことになります。また、substringでは文字数を少なくした方が、その分時間も短縮となります。
以下は、CREATE INDEXを繰り返し実行した際の計測時間(ミリ秒)です。
| md5(value) | substring(value from 1 for 32) | substring(value from 1 for 10) | |
|---|---|---|---|
| 1 | 82,491.53 | 72,999.52 | 65,689.24 |
| 2 | 81,820.62 | 71,914.60 | 65,977.31 |
| 3 | 82,934.11 | 71,851.98 | 65,046.34 |
| 平均値 | 82,415.42 | 72,255.37 | 65,570.96 |
substringの場合、先頭の文字しか見ないので、substringで切り出した部分が同じような値となってしまう、すなわちカーディナリティが低くなる可能性があります。そうなると、インデックスの効果も低くなります。
逆にmd5だと同じような値にはならず、カーディナリティが高くなるので、このような問題は起きません。
上記を踏まえて考えると、substringを使って十分なカーディナリティが保てる文字数でインデックスを作るのが良いと思っています。substringで保てない場合にはmd5を使う形で。
また、今回試してはいませんが、全文検索的なもの(pg_bigmなど)を使うといった方法もあります。ただ、これはどちらかというと、部分一致のような場合に利用するもので、今回のような完全一致の場合には、関数インデックスで十分かなと考えています。
参考: MySQLでの対応
MySQLでは、文字列のカラム型(CHAR、VARCHAR、TEXTなど)に対してプレフィックス長を指定することができます。(TEXT型はプレフィックス長が必須)
CREATE INDEX ON table1(value(32));
これのとても便利なところは、検索時にプレフィックス長を意識しなくて良いことです。
PostgreSQLの時のように、WHERE句でsubstring関数を指定する必要がありませんので、関数インデックス用意していたけど、WHERE句で指定してなかったのでインデックス使われなかった、、みたいなことになりません。
PostgreSQLでも同じようなことが出来たら便利ですね。
Discussion