資料ダウンロード

ククログ

株式会社クリアコード > ククログ > PostgreSQLがPGroongaのインデックスを使ってくれないときのチェックポイント

PostgreSQLがPGroongaのインデックスを使ってくれないときのチェックポイント

こんにちは、PGroongaの開発やサポートをしている堀本です。

この記事では、PGroongaのインデックスを設定しているにも関わらず、 シーケンシャルサーチで検索が実行されてしまう時に確認すべきポイントを紹介します。

PGroongaはPostgreSQLで高速に全文検索するための拡張ですが、 PGroongaのインデックスを設定しているのに、検索が速くならないことがあります。

色々な原因が考えられるのですが、よくあるケースとしては PostgreSQLがPGroongaのインデックスを使用せず、シーケンシャルサーチで検索が実行されていているケースです。

以下では、なぜシーケンシャルサーチが選択されるのか、PGroongaのインデックスを選択して 検索を実行するにはどうしたらいいかを解説します。

最初に、シーケンシャルサーチで検索が実行されているかどうかを確認します。 その後に、シーケンシャルサーチで検索が実行される原因とその解決方法を4つ記載します。

シーケンシャルサーチで検索が実行されているか確認

では、まず該当のクエリーがシーケンシャルサーチになっているかどうかを確認しましょう。 確認はEXPLAIN ANALYZEを使用して行います。

ここでは、以下のようなテーブル構造を例にします。 シーケンシャルサーチで検索している時のEXPLAIN ANALYZEの結果が欲しいので、 以下の例では、必ずシーケンシャルサーチになるように何もインデックスを設定していませんし、主キーも設定していません。

CREATE TABLE memos (
  title text,
  content text
);

INSERT INTO memos VALUES ('PostgreSQL', 'PostgreSQLはRDBMSです。');
INSERT INTO memos VALUES ('Groonga', 'Groongaは超高速な全文検索エンジンです。');
INSERT INTO memos VALUES ('PGroonga', 'PGroongaはPostgreSQLで超高速な全文検索を実現する拡張です。');

該当のクエリーは以下とします。

SELECT * FROM memos WHERE content &@~ 'PostgreSQL';

では、該当のクエリーがシーケンシャルサーチかどうかを確認しましょう。 確認には、前述の通りEXPLAIN ANALYZEを使います。

EXPLAIN ANALYZE SELECT * FROM memos WHERE content &@~ 'PostgreSQL';
--                                              QUERY PLAN                                              
-- -----------------------------------------------------------------------------------------------------
--  Seq Scan on memos  (cost=0.00..678.80 rows=1 width=64) (actual time=2.803..4.664 rows=2 loops=1)
--    Filter: (content &@~ 'PostgreSQL'::text)
--    Rows Removed by Filter: 1
--  Planning Time: 0.113 ms
--  Execution Time: 4.731 ms
-- (5 rows)

結果は上記の通りです。 シーケンシャルサーチの場合は上記のようにSeq Scanと出力されます。 ここのSeq Scanを以下のようにIndex Scan using #{PGroongaのインデックス名}とするのが今回のゴールです。

EXPLAIN ANALYZE SELECT * FROM memos WHERE content &@~ 'PostgreSQL';
--                                                           QUERY PLAN                                                          
-- ------------------------------------------------------------------------------------------------------------------------------
--  Index Scan using pgrn_content_index on memos  (cost=0.00..4.02 rows=1 width=64) (actual time=0.778..0.782 rows=2 loops=1)
--    Index Cond: (content &@~ 'PostgreSQL'::text)
--  Planning Time: 0.835 ms
--  Execution Time: 1.002 ms
-- (4 rows)

シーケンシャルサーチかどうかの確認方法がわかったので、具体的に問題のケースを解決していきましょう。

ケース１「PGroongaのインデックスの設定し忘れ」

まずは、一番簡単なケースから紹介します。

当然ですが、PGroongaのインデックスを設定していなければ PGroongaのインデックスを使った検索はできません。

そんな簡単な見落としをするかな？と思う方も多いと思いますが、非常に多くのテーブル、インデックスが存在するデータベースを考えてみてください。 数十、数百あるテーブル、カラム、インデックスがあるケースでは、うっかりインデックスを設定し忘れるということは起こり得ます。

このケースは、確認が非常に簡単ですぐ終わるので、最初に確認する点として適当かと思います。

チェックポイント：テーブルの構成情報を確認する

このケースのチェックポイントは、テーブルの構成情報を確認するということです。 具体的には、以下のように確認します。

  1. psqlで該当のDBへ接続
  2. テーブルの定義を確認
  3. 2.の中にPGroongaのインデックスが存在するか確認

1.の操作は特に難しいことは無いので詳細は省略します。 2.には、\d #{テーブル名}が使えます。#{テーブル名}の部分はPGroongaのインデックスを使って検索したいカラムを含むテーブルの名前に置き換えます。 3.で2.の内容を確認します。具体的にどこを確認するのかを以下に記載します。

まず、以下のmemosテーブルのcontentカラムにインデックスを設定しているとします。

CREATE TABLE memos (
  title text PRIMARY KEY,
  content text
);
CREATE INDEX pgrn_content_index ON memos USING pgroonga (content);

次に、memosテーブルのcontentカラムにインデックスが設定されているかどうかを\dコマンドで確認します。 結果は以下のようになります。

\d memos
--              Table "public.memos"
--  Column  | Type | Collation | Nullable | Default 
-- ---------+------+-----------+----------+---------
--  title   | text |           | not null | 
--  content | text |           |          | 
-- Indexes:
--     "memos_pkey" PRIMARY KEY, btree (title)
--     "pgrn_content_index" pgroonga (content)

Indexesの項目に注目してください。 ここには、memosテーブルに設定されている全てのIndexが#{Index名} #{インデックスの種類} (#{インデックス対象のカラム名})の形式で表示されます。 ここで、PGroongaのインデックスがcontentカラムに設定されているかを確認します。

今回の例では、pgrn_content_indexをcontentカラム設定したので、Indexesに"pgrn_content_index" pgroonga (content)が表示されていることを確認します。 前述の通り、Indexesは#{Index名} #{インデックスの種類} (#{インデックス対象のカラム名})という形式なので、 "pgrn_content_index" pgroonga (content)という表示で、PGroongaのインデックスがcontentカラムに設定されていることがわかります。

もし、pgrn_content_indexを設定し忘れてしまった場合は、以下のようにIndexesにpgrn_content_indexが表示されません。

\d memos
--              Table "public.memos"
--  Column  | Type | Collation | Nullable | Default 
-- ---------+------+-----------+----------+---------
--  title   | text |           | not null | 
--  content | text |           |          | 
-- Indexes:
--     "memos_pkey" PRIMARY KEY, btree (title)

このように\dコマンドを使ってインデックスの設定し忘れを確認します。 インデックスがちゃんと設定されていることが確認できました。

この段階で、インデックスを使用した検索ができていればチェックはここで完了です。 インデックスがちゃんと設定されていても、インデックスを使った検索にならない場合は次のケースに進んでください。

ケース2「演算子がサポートしていない型のカラムを対象に検索している」

次は検索対象のカラムの型と検索に使用している演算子がサポートしている型が一致していないケースです。

具体例を交えて説明します。 以下のようなテーブル、インデックス、クエリーを考えます。

CREATE TABLE memos (
  title text PRIMARY KEY,
  content text,
  tags text[]
);
INSERT INTO memos VALUES ('PostgreSQL', 'PostgreSQLはRDBMSです。', ARRAY['PostgreSQL']);
INSERT INTO memos VALUES ('Groonga', 'Groongaは超高速な全文検索エンジンです。', ARRAY['Groonga']);
INSERT INTO memos VALUES ('PGroonga', 'PGroongaはPostgreSQLで超高速な全文検索を実現する拡張です。', ARRAY['PGroonga', 'PostgreSQL']);

CREATE INDEX pgrn_tags_index ON memos USING pgroonga (tags);

SET enable_seqscan = off;
EXPLAIN ANALYZE VERBOSE SELECT * FROM memos WHERE tags &> 'PostgreSQL';
--                                                            QUERY PLAN                                                           
-- --------------------------------------------------------------------------------------------------------------------------------
--  Seq Scan on public.memos  (cost=10000000000.00..10000000003.28 rows=1 width=96) (actual time=23.095..23.098 rows=2 loops=1)
--    Output: title, content, tags
--    Filter: ((memos.tags)::character varying[] &> 'PostgreSQL'::character varying)
--    Rows Removed by Filter: 1
--  Planning Time: 0.084 ms
--  JIT:
--    Functions: 2
--    Options: Inlining true, Optimization true, Expressions true, Deforming true
--    Timing: Generation 0.330 ms, Inlining 9.505 ms, Optimization 8.461 ms, Emission 5.106 ms, Total 23.403 ms
--  Execution Time: 23.473 ms
-- (10 rows)

上の例を実行するとインデックスを使った検索ではなく、シーケンシャルサーチになります。 これをインデックスを使った検索をするように変更していきます。

チェックポイント：演算子がサポートしているデータ型を確認する

まず、上の例の内容を確認しましょう。 この例の検索対象のカラムはtagsでtext[]型です。 検索に使用している演算子は&>で、これは検索対象の配列型のカラム内に指定したキーワードが含まれているかどうかをチェックします。

次に、text[]型をサポートしている演算子に&>があるかを確認します。 どの演算子がどの型をサポートしているかは、PGroongaの公式ドキュメントのリファレンスマニュアルに記載があります。 「PGroongaの公式ドキュメントのリファレンスマニュアル」で&>を検索します。 すると、「varchar[]用」というセクションに&>が記載されていることがわかります。 つまり、&>はvarchar[]型のカラムをサポートしていますが、それ以外の型はサポートしていません。

再び上の例を見てみましょう。 検索対象のtagsカラムはtext[]型なので、&>はサポートしていない型です。 このように、検索対象のカラムの型を検索に使う演算子がサポートしていない場合、インデックスは使用されません。

このケースでは、インデックス作成時と検索時にtagsをvarchar[]にキャストすることでインデックスが使われるようになります。 では、実際に見てみましょう。

DROP INDEX pgrn_tags_index;
CREATE INDEX pgrn_tags_index ON memos USING pgroonga ((tags::varchar[]));

EXPLAIN ANALYZE VERBOSE SELECT * FROM memos WHERE tags::varchar[] &> 'PostgreSQL';
--                                                             QUERY PLAN                                                            
-- ----------------------------------------------------------------------------------------------------------------------------------
--  Index Scan using pgrn_tags_index on public.memos  (cost=0.00..4.01 rows=1 width=96) (actual time=0.195..0.195 rows=0 loops=1)
--    Output: title, content, tags
--    Index Cond: ((memos.tags)::character varying[] &> 'PostgreSQL'::character varying)
--  Planning Time: 0.047 ms
--  Execution Time: 0.221 ms
-- (5 rows)

無事にインデックスを使った検索ができるようになりました。

この段階で、インデックスを使用した検索ができていればチェックはここで完了です。 演算子がサポートしている型のカラムを検索をしている場合でも、インデックスを使った検索にならない場合は次のケースに進んでください。

ケース3「演算子クラスの指定が間違っている。あるいは指定していない」

次は、検索対象のカラムの型に対応した演算子クラスを指定していない場合です。 演算子クラスについて説明すると長くなってしまうので、ここでは説明を割愛します。演算子クラスが何かわからなくても確認することはできます。

ここでも具体例を交えて説明します。 以下のようなテーブル、インデックス、クエリーを考えます。

CREATE TABLE memos (
  title text PRIMARY KEY,
  content text
);
INSERT INTO memos VALUES ('PostgreSQL', 'PostgreSQLはRDBMSです。');
INSERT INTO memos VALUES ('Groonga', 'Groongaは超高速な全文検索エンジンです。');
INSERT INTO memos VALUES ('PGroonga', 'PGroongaはPostgreSQLで超高速な全文検索を実現する拡張です。');

CREATE INDEX pgrn_tags_index ON memos USING pgroonga (content);

SET enable_seqscan = off;
EXPLAIN ANALYZE VERBOSE SELECT * FROM memos WHERE content &^ 'Postgre';
--                                                            QUERY PLAN                                                           
-- --------------------------------------------------------------------------------------------------------------------------------
--  Seq Scan on public.memos  (cost=10000000000.00..10000000003.28 rows=1 width=64) (actual time=17.357..17.361 rows=1 loops=1)
--    Output: title, content
--    Filter: (memos.content &^ 'Postgre'::text)
--    Rows Removed by Filter: 2
--  Planning Time: 0.198 ms
--  JIT:
--    Functions: 2
--    Options: Inlining true, Optimization true, Expressions true, Deforming true
--    Timing: Generation 0.290 ms, Inlining 7.746 ms, Optimization 5.999 ms, Emission 3.593 ms, Total 17.628 ms
--  Execution Time: 17.705 ms
-- (10 rows)

こちらの例も実行するとインデックスを使った検索ではなく、シーケンシャルサーチになります。 これをインデックスを使った検索をするように変更していきます。

チェックポイント：演算子クラスの指定を確認する

まず、上の例の内容を確認しましょう。 この例の検索対象のカラムはcontentでtext型です。 検索に使用している演算子は&^で、これは前方一致検索を行う演算子です。

再びPGroongaの公式ドキュメントのリファレンスマニュアルを参照します。 「PGroongaの公式ドキュメントのリファレンスマニュアル」の「text用」のセクションから&^を探してください。 「pgroonga_text_term_search_ops_v2演算子クラス」の配下に見つかると思います。

これは、&^をtext型のカラムに使う場合は、pgroonga_text_term_search_ops_v2演算子クラスを指定する必要があることを示しています。 つまり、以下のようにします。

DROP INDEX pgrn_tags_index;
CREATE INDEX pgrn_tags_index ON memos USING pgroonga (content pgroonga_text_term_search_ops_v2);

EXPLAIN ANALYZE VERBOSE SELECT * FROM memos WHERE content &^ 'Postgre';
--                                                             QUERY PLAN                                                            
-- ----------------------------------------------------------------------------------------------------------------------------------
--  Index Scan using pgrn_tags_index on public.memos  (cost=0.00..4.01 rows=1 width=64) (actual time=0.633..0.636 rows=1 loops=1)
--    Output: title, content
--    Index Cond: (memos.content &^ 'Postgre'::text)
--  Planning Time: 0.798 ms
--  Execution Time: 0.830 ms
-- (5 rows)

ポイントは、CREATE INDEXのUSING pgroonga (content pgroonga_text_term_search_ops_v2)の部分です。 上の例のように、演算子クラスを指定する必要がある組み合わせの場合は、pgroonga (インデックスを設定するカラム 演算子クラス)とします。

無事にインデックスを使った検索ができるようになりました。

この段階で、インデックスを使用した検索ができていればチェックはここで完了です。 演算子クラスの指定が正しくても、インデックスを使った検索にならない場合は次のケースに進んでください。

ケース4「PGroongaのインデックスで指定しているカラムの順序と検索クエリーで記載しているカラムの順序が異なる」

このケースは、以下のように検索対象のカラムが複数あってARRAY[]を使って複数のカラムを一度にまとめて検索している場合に確認するポイントです。 具体的には以下のケースです。 以下のケースでは、titleカラムとcontentカラムにGroongaまたはPostgreSQLが含まれているレコードを検索しています。

CREATE TABLE memos (
  title text,
  content text
);

CREATE INDEX pgroonga_memos_index
    ON memos
 USING pgroonga ((ARRAY[title, content]));

INSERT INTO memos VALUES ('PostgreSQL', 'PostgreSQLはリレーショナル・データベース管理システムです。');
INSERT INTO memos VALUES ('Groonga', 'Groongaは日本語対応の高速な全文検索エンジンです。');
INSERT INTO memos VALUES ('PGroonga', 'PGroongaはインデックスとしてGroongaを使うためのPostgreSQLの拡張機能です。');
INSERT INTO memos VALUES ('コマンドライン', 'groongaコマンドがあります。');

SET enable_seqscan = off;
EXPLAIN ANALYZE VERBOSE
SELECT *
  FROM memos
 WHERE ARRAY[content, title] &@~ 'Groonga OR PostgreSQL';
--                                                           QUERY PLAN                                                          
-- -----------------------------------------------------------------------------------------------------------------------------
--  Seq Scan on public.memos  (cost=10000000000.00..10000000678.80 rows=1 width=64) (actual time=39.162..39.583 rows=4 loops=1)
--    Output: title, content
--    Filter: (ARRAY[memos.content, memos.title] &@~ 'Groonga OR PostgreSQL'::text)
--  Planning Time: 0.109 ms
--  JIT:
--    Functions: 2
--    Options: Inlining true, Optimization true, Expressions true, Deforming true
--    Timing: Generation 0.081 ms, Inlining 25.679 ms, Optimization 7.429 ms, Emission 4.475 ms, Total 37.664 ms
--  Execution Time: 58.043 ms
-- (9 rows)

上記の例もインデックスを使った検索ではなく、シーケンシャルサーチになっています。

チェックポイント：カラムの順序を確認する

確認するポイントは、以下の2点です。

  1. CREATE INDEXでインデックス対象の式として指定しているARRAY[title, content]
  2. WHERE句のARRAY[content, title]

上記のように並べるとわかるのですが、CREATE INDEXで指定しているARRAY[title, content]とWHERE句のARRAY[content, title]ではARRAY[]内のカラムの順序が異なります。 上記のように、CREATE INDEXとWHERE句でARRAY[]に指定したカラムの順序が異なるとインデックスを使用しません。

ということは、順序を揃えればインデックスを使った検索をします。 実際に見てみましょう。

EXPLAIN ANALYZE VERBOSE
SELECT *
  FROM memos
 WHERE ARRAY[title, content] &@~ 'Groonga OR PostgreSQL';
--                                                              QUERY PLAN                                                             
-- ------------------------------------------------------------------------------------------------------------------------------------
--  Index Scan using pgroonga_memos_index on public.memos  (cost=0.00..4.01 rows=1 width=64) (actual time=1.241..1.247 rows=4 loops=1)
--    Output: title, content
--    Index Cond: (ARRAY[memos.title, memos.content] &@~ 'Groonga OR PostgreSQL'::text)
--  Planning Time: 0.160 ms
--  Execution Time: 1.508 ms
-- (5 rows)

CREATE INDEXで指定しているARRAY[]とWHERE句のARRAY[]に注目してください。 こちらの例では、どちらもARRAY[title, content]でARRAY[]内のカラムの順序が同じになっています。

上記の通り、ARRAY[]内のカラムの順序が同じであればインデックスを使って検索をするようになりました。

この段階で、インデックスを使用した検索ができていればチェックはここで完了です。 カラムの順序が正しくても、インデックスを使った検索にならない場合はPGroongaの問題の可能性があるので、PGroongaのIsuuesやPGroongaのDiscussionsに報告してください。（日本語で報告いただいても大丈夫です。）

まとめ

今回は、PostgreSQLがPGroongaのインデックスを使ってくれない時に 比較的簡単に確認できるチェックポイントを紹介しました。

もちろん、ここで紹介したもの以外の原因もありますので、この記事の通りに やっても解決しない場合は、GitHubのIssuesやDiscussionsへ報告してください。 解決を急いでいる場合は、クリアコードの有償サポートもありますので、ぜひご検討ください。

関連情報

現在クリアコードではソフトウェア開発者を募集しています！ フリーソフトウェア開発での開発の仕方をベースに、成果や知識を公開し共有しながら業務に携われます。

クリアコードのYouTubeチャンネルです。メンバーの登壇動画や、開発プロダクトに関する動画を公開しています。

Groongaについて開発者に質問できる「グルカイ！」ほぼ毎週火曜日12:15-12:45　Groongaチャンネルで開催中です。 Groongaについて知りたい人、ぜひご参加ください。