エムスリーテックブログ

エムスリー(m3)のエンジニア・開発メンバーによる技術ブログです

PostgreSQL を使ったユーザー検索機能のパフォーマンス改善の話

こんにちは、デジスマチームでソフトウェアエンジニアをしている伊藤です。 この記事はデジスマチームブログリレーの8日目の記事です。 今回は我々が開発しているデジスマ診療 (以降、デジスマ) で、医療機関向けに提供しているユーザー検索機能のパフォーマンスを PostgreSQL の機能を活用して改善した話について紹介します。

ユーザー検索の課題感

検索機能は、医療機関のスタッフが患者や予約情報を迅速に見つけるために不可欠です。 しかし、ユーザー検索は、

  • 特に大規模な医療機関ではスキャンするデータ量が増える
  • 検索対象のカラムが多岐にわたり適切なインデックスを効きかせづらい
  • そもそも部分一致の場合、B-Tree インデックスでは前方一致しか効かない

などの理由から、パフォーマンスの問題が発生することがあります。 嬉しい悲鳴ではありますが、デジスマも事業成長に伴って、ユーザー数が増加し、これらの課題に直面しようとしています。 特に一部の患者数の多い医療機関では、ユーザー検索のレイテンシーが 99パーセンタイル (p99) で劣化する可能性があるなど、事前に対策が必要なユースケースが見えてきたため、先立って改善検討を進めました。

ハッシュテーブルや検索エンジンを使うことも一般的には検討されますが、今回は先立って PostgreSQL の機能を活用して改善して備えました。

データ構造

デジスマでは、ユーザーはデジスマ診察券というアプリに登録し、デジスマを利用している複数の医療機関において、予約や決済を行えます。 それを実現するために、データ構造は、ユーザーの会員情報は医療機関から独立して users テーブルに格納しています。 一方で、ユーザーが医療機関を利用したときにはじめて、来院情報 (clinic_users) やユーザー番号 (user_numbers)、ユーザーメモ (user_notes) などの医療機関との関連情報が格納されます。 下記は、実際のアプリケーションとは異なりますが、本記事に関連する部分を簡略化したテーブル構造を示しています。

-- ユーザー基本情報
CREATE TABLE IF NOT EXISTS users
(
    id           UUID                     DEFAULT uuid_generate_v4() PRIMARY KEY,
    phone_number VARCHAR(255) UNIQUE                    NOT NULL,
    name         VARCHAR(255)                           NULL,
    name_kana    VARCHAR(255)                           NULL,
    birthday     DATE                                   NULL,
    created_at   TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at   TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

-- 医療機関
CREATE TABLE IF NOT EXISTS clinics
(
    id         UUID                     DEFAULT uuid_generate_v4() PRIMARY KEY
);

-- 医療機関を利用したユーザーと医療機関の関連
CREATE TABLE IF NOT EXISTS clinic_users
(
    clinic_id UUID                                   NOT NULL REFERENCES clinics (id),
    user_id                UUID                                   NOT NULL REFERENCES users (id),
    created_at             TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,

    PRIMARY KEY (clinic_id, user_id)
);

-- 医療機関内のユーザー番号
CREATE TABLE IF NOT EXISTS user_numbers
(
    clinic_id UUID                                   NOT NULL REFERENCES clinics (id),
    user_id                UUID                                   NOT NULL REFERENCES users (id),
    number                 VARCHAR(20)                            NOT NULL,
    created_at             TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,

    PRIMARY KEY (clinic_id, user_id)
);

-- 医療機関のユーザーに対するメモ
CREATE TABLE IF NOT EXISTS user_notes
(
    clinic_id UUID                                   NOT NULL REFERENCES clinics (id),
    user_id                UUID                                   NOT NULL REFERENCES users (id),
    note                   TEXT                                   NOT NULL,
    created_at             TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,

    PRIMARY KEY (clinic_id, user_id)
);

検索クエリ

検索クエリの仕様は次のようになっています。

検索対象カラム (すべて部分一致 LIKE '%{query}%') ：

  • users.name (姓名の間のスペース除去後)
  • users.name_kana (姓名の間のスペース除去後)
  • users.phone_number
  • users.birthday (DATE型なので文字列にキャスト後)
  • user_numbers.number
  • user_notes.note

ソート：

  • clinic_users.updated_at DESC
  • user_numbers.number DESC NULLS LAST
  • users.created_at DESC
SELECT *
FROM users
INNER JOIN clinic_users
ON users.id = clinic_users.user_id
    AND (clinic_users.clinic_id = '{clinic_id}')
LEFT JOIN user_numbers
ON users.id = user_numbers.user_id
    AND (user_numbers.clinic_id = '{clinic_id}')
LEFT JOIN user_notes
ON users.id = user_notes.user_id
    AND (user_notes.clinic_id = '{clinic_id}')
WHERE (user_numbers.number LIKE '%{query}%')
   OR (replace(users.name, ' ', '') LIKE '%{query}%')
   OR (replace(users.name_kana, ' ', '') LIKE '%{query}%')
   OR (users.phone_number LIKE '%{query}%')
   OR (CAST(users.birthday AS VARCHAR)) LIKE '%{query}%'
   OR (user_notes.note LIKE '%{query}%')
ORDER BY clinic_users.created_at DESC,
         user_numbers.number DESC NULLS LAST,
         users.created_at DESC
LIMIT 100;

Step 1: 仕様の変更で対応できることはないか?

課題としては、検索条件の各カラムに対して部分一致検索しているため、B-Treeインデックスが効かず、シーケンシャルスキャンでフィルタがされることでした。 (割愛していますが、Explain Analyze で確認しています。) 最初に検討したのは、仕様変更で対応できることはないかという点でした。 既に作成されていた B-Tree インデックスは前方一致検索や完全一致には有効なので、LIKE '{query}%' や = '{query}' のように、前方一致検索や完全一致に変更できないかを検討しました。 また、インデックスを新しく追加するにしても Writer インスタンスのメモリ使用量の制約や書き込みのオーバーヘッドがあるため、実装が実際のユースケースに対して過剰なものになっているなら削りたいと考えました。

ここからは技術でも何でもないですが、プロダクトマネージャーと相談して、次の仕様の変更しました：

  • users.birthday の部分一致検索をやめ、完全一致検索
  • users.phone_number の部分一致検索をやめ、前方一致検索

これにより、両カラムの検索条件は B-Tree インデックスが効くようになり、シーケンシャルスキャンを回避できるようになりました。

Step 2: 部分一致検索のままで高速化できないか? (pg_bigm の導入)

次に、部分一致検索のままで高速化できないかを検討しました。 今回は pg_bigm という PostgreSQL の拡張モジュールを使って、部分一致検索のパフォーマンスを改善することにしました。 pg_bigm は、文字列を 2-gram (バイグラム) に分割する n-gram の一種で、特に日本語の部分一致検索に適しています。 当初は pg_trgm を使うことも検討しましたが、日本人の姓名の長さが 3-gram (トライグラム) との相性が悪いことや、pg_trgm が日本語で性能を出すのが難しそうだったため、pg_bigm を選択しました。

まずは簡単なクエリで実験してみました。

CREATE EXTENSION IF NOT EXISTS pg_bigm;

CREATE INDEX CONCURRENTLY IF NOT EXISTS users_name_idx ON users USING GIN (name gin_bigm_ops);

CREATE INDEX CONCURRENTLY IF NOT EXISTS users_name_kana_idx ON users USING GIN (name_kana gin_bigm_ops);

EXPLAIN ANALYZE
SELECT *
FROM users
INNER JOIN clinic_users ON users.id = clinic_users.user_id
    AND clinic_users.clinic_id = '{clinic_id}'
WHERE users.name LIKE '%{query}%'
   OR users.name_kana LIKE '%{query}%';

結果が下記になります。

Gather  (cost=1141.65..15468.91 rows=52 width=437) (actual time=7.159..9.185 rows=0 loops=1)
  Workers Planned: 1
  Workers Launched: 1
  ->  Nested Loop  (cost=141.65..14463.71 rows=31 width=437) (actual time=1.128..1.128 rows=0 loops=2)
        ->  Parallel Bitmap Heap Scan on users  (cost=141.22..6358.50 rows=972 width=381) (actual time=0.242..0.440 rows=122 loops=2)
              Recheck Cond: (((name)::text ~~ '%{query}%'::text) OR ((name_kana)::text ~~ '%{query}%'::text))
              Heap Blocks: exact=241
              ->  BitmapOr  (cost=141.22..141.22 rows=1652 width=0) (actual time=0.427..0.428 rows=0 loops=1)
                    ->  Bitmap Index Scan on users_name_idx  (cost=0.00..66.20 rows=827 width=0) (actual time=0.383..0.383 rows=243 loops=1)
                          Index Cond: ((name)::text ~~ '%{query}%'::text)
                    ->  Bitmap Index Scan on users_name_kana_idx  (cost=0.00..74.19 rows=826 width=0) (actual time=0.044..0.044 rows=0 loops=1)
                          Index Cond: ((name_kana)::text ~~ '%{query}%'::text)
        ->  Index Scan using clinic_users_user_id_idx on clinic_users  (cost=0.43..8.34 rows=1 width=56) (actual time=0.005..0.005 rows=0 loops=243)
              Index Cond: (user_id = users.id)
              Filter: (clinic_id = '{clinic_id}'::uuid)
              Rows Removed by Filter: 1
Planning Time: 0.554 ms
Execution Time: 9.288 ms

GINインデックスを使うことで、LIKE '%token%' のような曖昧検索でもインデックスが効くようになりました。 9.288ms という実行時間は、完全に同じクエリではないですが、元のクエリが数十秒かかっていたことを考えると大幅な改善でした。 これまでシーケンシャルスキャンしていた部分がインデックススキャンで解決できるようになり、パフォーマンスが大幅に向上しました。

Step 3: インデックスを効率的に活用できないか? (2段階検索への分解)

一難去ってまた一難で、GIN インデックスを使うことで、部分一致検索のパフォーマンスは大幅に改善されそうなことは手応えとしてありました。 しかし、複雑なOR条件が絡むと、プランナーがインデックスを選びづらい問題が残りました。また、実際のクエリでは、replace 関数を使っており、統計情報に反映されないため、複雑なクエリだとなおさらプランナーが適切なインデックスを選択されないという課題がありました。

そこで、最終的には2段階に分解して、各検索条件ごとに個別に user_id のリストを取得し、それらを結合して最終的な検索を行う方法を採用しました。

Step 3.1: 各検索条件で user_id のリストを個別に取得

-- 名前検索
SELECT users.id,
       clinic_users.updated_at,
       users.created_at
FROM users
INNER JOIN clinic_users
ON users.id = clinic_users.user_id
    AND (clinic_users.clinic_id = 'inst_id')
WHERE (replace(users.name, ' ', '') LIKE '%query%')
   OR (replace(users.name_kana, ' ', '') LIKE '%query%')
ORDER BY clinic_users.created_at DESC,
         user_numbers.number DESC NULLS LAST,
         users.created_at DESC
LIMIT 100;

Step 2: 取得した user_id リストでIN句を使った最終検索

SELECT *
FROM users
INNER JOIN clinic_users
ON users.id = clinic_users.user_id
    AND (clinic_users.clinic_id = '{clinic_id}')
LEFT JOIN user_numbers
ON users.id = user_numbers.user_id
    AND (user_numbers.clinic_id = '{clinic_id}')
LEFT JOIN user_notes
ON users.id = user_notes.user_id
    AND (user_notes.clinic_id = '{clinic_id}')
WHERE users.id IN ('{uuid1}', '{uuid2}', '{uuid3}') -- Step1で取得したユーザーIDのリスト
ORDER BY clinic_users.updated_at DESC,
         user_numbers.number DESC NULLS LAST,
         users.created_at DESC
LIMIT 100;

2段階検索にすると、問い合わせ回数が増えるためオーバーヘッドが生まれるというトレードオフはあります。ただし、次のようなメリットを取る選択をしました：

  • 個別の検索条件ごとにシンプルなクエリを実行することで、プランナーがインデックスを選びやすくなり、クエリのパフォーマンスが安定すること
  • 最終的なクエリは id に対する絞り込みになるため、インデックスの効きが良い

また、実装パートでは、さらに次のような細かい問題にもぶつかりましたが、ここでは、割愛します：

  • パラメータによってクエリビルディングしている関係で、テストケースが増加し、クエリ分割の影響範囲を出すのが大変だった。
  • UNION を使う場合は ORDER BY ができないが、サブクエリで対応するか、個別のクエリで問い合わせるか？
  • 分割して取得するので LIMIT / OFFSET を個別のクエリではどうするか？

パフォーマンス改善の結果

改善の結果、以下のようなパフォーマンス向上を達成しました：

  • 一部のユーザー数の多い医療機関におけるレイテンシーのスパイクにも備えることができました。
  • また、オーバーヘッドを懸念していましたが、平常時のクエリのレイテンシーは、従来と同程度の応答時間を維持できていました。

一方で、それでも一部クエリがまだ劣化してしまうパターンが課題としては残っています。（もちろんアプリケーション上エラーにならない範囲です） これは、医療機関横断の users をインデックススキャンで取得し、それを clinic_users で Nested Loop で結合するパターンでした。 統計情報がうまく取れている場合は、プランナーが先に clinic_users をスキャンして、そこから検索条件で絞ってくれるのですが、うまく行かないケースがあるみたいでした。 ここは引き続き改善を検討していきたいと思います。

終わりに

本記事では、デジスマチームで日々開発しているリアルな課題や取り組み方、またユーザー検索というどのサービスでもありえる課題の解決の仕方の一例を紹介しました。

We are Hiring!

エムスリーでは一緒にプロダクト開発をするエンジニアを絶賛募集中です。 デジスマの仕事に興味をお持ちいただけたならぜひ詳細をご確認ください。

エンジニア採用ページはこちら

jobs.m3.com

エンジニア新卒採用サイト！ ！

fresh.m3recruit.com

カジュアル面談！ ！

jobs.m3.com