VS Code の PostgreSQL 拡張機能を使って SQL のパフォーマンスを計測すると便利だった

はじめに

前回の記事 では AWS Bedrock と pgvector を使い、記事の要約文章の自動生成やタグのサジェスト機能を実装しました。

その流れでベクトルデータを活用した関連記事レコメンド機能も追加したのですが、ベクトルデータの扱いに不慣れだったこともあり SQL がやや複雑になってしまいました。

リリース後にパフォーマンスを検証したいと思っていたため、今回は VS Code の PostgreSQL 拡張機能で EXPLAIN 分析し、改善した話を書きます。

---

VS Code PostgreSQL 拡張機能とは

Microsoft が提供する VS Code 向けの PostgreSQL 拡張機能です。

DB への接続・クエリ実行に加え、EXPLAIN の結果をビジュアルなノードツリーで表示でき、どのノードがボトルネックか一目でわかるのが便利だなと感じました。

• PostgreSQL (ms-ossdata.vscode-pgsql) - VS Code Marketplace

他にも様々な機能が提供されていますが、設定後のイメージを簡単にお見せすると以下のようになっています。

テーブルのER図もビジュアル化できます。

---

関連記事レコメンド機能の概要

簡単ではありますが、以下のように機能させています。

記事投稿時

• 記事を投稿すると、記事のテキストをベクトルデータに変換してテーブルに保存します。
• ベクトルデータ:
  • テキストの意味を数値の配列 (例: [0.12, -0.43, 0.87, ...]) で表現したものです。
  • 意味が近い文章ほど、数値の並びが似た形になります。

記事参照時

• 記事詳細ページを表示するタイミングで、pgvector のコサイン類似度を使い、閲覧中の記事に意味的に近い記事を最大 5 件取得して返します。
• コサイン類似度:
  • 2 つのベクトルがどれだけ同じ方向を向いているかを -1〜1 の値で表す指標です。
  • 1 に近いほど意味が似ており、pgvector では <=> 演算子で計算できます。

修正前のクエリ

WITH target AS (
    SELECT embedding FROM article_embeddings WHERE article_id = '...'
)
SELECT
    a.id, a.title, a.slug, a.summary, a.published_at, a.category_id,
    c.name AS category_name, c.slug AS category_slug, c.color AS category_color,
    COALESCE(
        json_agg(
            json_build_object('id', tg.id, 'name', tg.name, 'slug', tg.slug, 'description', tg.description)
        ) FILTER (WHERE tg.id IS NOT NULL),
        '[]'
    ) AS tags,
    ae.embedding <=> t.embedding AS distance
FROM article_embeddings ae
CROSS JOIN target t
JOIN articles a ON a.id = ae.article_id
LEFT JOIN categories AS c ON a.category_id = c.id
LEFT JOIN article_tags AS at ON a.id = at.article_id
LEFT JOIN tags AS tg ON at.tag_id = tg.id
WHERE ae.article_id != '...'
    AND a.status = 'published'
    AND a.deleted_at IS NULL
GROUP BY a.id, c.id, c.name, c.slug, c.color, ae.embedding <=> t.embedding
ORDER BY distance
LIMIT 5;

LIMIT 5 が最後にあるため、全記事に対して JOIN + GROUP BY を実行してから絞り込む構造になっています。

拡張機能で EXPLAIN を実行してみる

まずはターミナルなどから EXPLAIN を実行してみます。

                                                                                        QUERY PLAN                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=21.90..21.93 rows=1 width=1892) (actual time=0.649..0.662 rows=5 loops=1)
   Buffers: shared hit=684
   ->  GroupAggregate  (cost=21.90..21.93 rows=1 width=1892) (actual time=0.649..0.661 rows=5 loops=1)
         Group Key: ((ae.embedding <=> article_embeddings.embedding)), a.id, c.id
         Buffers: shared hit=684
         ->  Sort  (cost=21.90..21.90 rows=1 width=1933) (actual time=0.636..0.637 rows=11 loops=1)
               Sort Key: ((ae.embedding <=> article_embeddings.embedding)), a.id, c.id
               Sort Method: quicksort  Memory: 47kB
               Buffers: shared hit=684
               ->  Nested Loop  (cost=8.46..21.89 rows=1 width=1933) (actual time=0.097..0.600 rows=63 loops=1)
                     Buffers: shared hit=675
                     ->  Nested Loop Left Join  (cost=8.46..20.49 rows=1 width=1943) (actual time=0.061..0.254 rows=63 loops=1)
                           Buffers: shared hit=319
                           ->  Nested Loop Left Join  (cost=8.31..20.09 rows=1 width=1874) (actual time=0.054..0.208 rows=63 loops=1)
                                 Buffers: shared hit=197
                                 ->  Nested Loop  (cost=8.17..11.81 rows=1 width=1402) (actual time=0.044..0.164 rows=63 loops=1)
                                       Join Filter: (a.id = ae.article_id)
                                       Rows Removed by Join Filter: 971
                                       Buffers: shared hit=71
                                       ->  Hash Right Join  (cost=8.17..10.05 rows=1 width=1384) (actual time=0.034..0.050 rows=65 loops=1)
                                             Hash Cond: (at.article_id = a.id)
                                             Buffers: shared hit=6
                                             ->  Seq Scan on article_tags at  (cost=0.00..1.69 rows=69 width=20) (actual time=0.002..0.004 rows=69 loops=1)
                                                   Buffers: shared hit=1
                                             ->  Hash  (cost=8.16..8.16 rows=1 width=1380) (actual time=0.026..0.026 rows=31 loops=1)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 16kB
                                                   Buffers: shared hit=5
                                                   ->  Index Scan using idx_articles_status on articles a  (cost=0.14..8.16 rows=1 width=1380) (actual time=0.009..0.015 rows=31 loops=1)
                                                         Index Cond: ((status)::text = 'published'::text)
                                                         Filter: (deleted_at IS NULL)
                                                         Buffers: shared hit=5
                                       ->  Seq Scan on article_embeddings ae  (cost=0.00..1.39 rows=30 width=34) (actual time=0.000..0.001 rows=16 loops=65)
                                             Filter: (article_id <> '5f7abce7-d1cb-424c-b4b2-6bde9276207d'::uuid)
                                             Rows Removed by Filter: 1
                                             Buffers: shared hit=65
                                 ->  Index Scan using categories_pkey on categories c  (cost=0.14..8.16 rows=1 width=472) (actual time=0.000..0.000 rows=1 loops=63)
                                       Index Cond: (id = a.category_id)
                                       Buffers: shared hit=126
                           ->  Index Scan using tags_pkey on tags tg  (cost=0.14..0.39 rows=1 width=73) (actual time=0.001..0.001 rows=1 loops=63)
                                 Index Cond: (id = at.tag_id)
                                 Buffers: shared hit=122
                     ->  Seq Scan on article_embeddings  (cost=0.00..1.39 rows=1 width=18) (actual time=0.000..0.001 rows=1 loops=63)
                           Filter: (article_id = '5f7abce7-d1cb-424c-b4b2-6bde9276207d'::uuid)
                           Rows Removed by Filter: 30
                           Buffers: shared hit=63
 Planning:
   Buffers: shared hit=467
 Planning Time: 3.205 ms
 Execution Time: 0.750 ms
(49 rows)

こちらと同じクエリを拡張機能から実行すると、実行計画がビジュアルで表示されます。

ビジュアルで確認できるボトルネックの箇所は以下の 3 点です。

• Nested Loop #7 → Seq Scan #12 の間の「1.04K」: article_embeddings テーブルを合計約 1,040 行スキャンしており、全記事分の距離計算が JOIN の中で繰り返されている。
• Index Scan #13 の「クリティカル」ラベル: categories テーブルへの Index Scan が 63 回繰り返されている。
• Limit #1 がツリーの最上部にある: 絞り込みが処理の最後に行われているため、それまでの全 JOIN・ソートが全記事を対象に実行されている。

ポイントとなる数値を抜き出すと次のとおりです。

article_embeddings が 65 ループしているようです。おそらく全記事分の JOIN を完了してからソートと LIMIT を適用しているからと考えられます。

この構造では、記事数が増えるほど article_embeddings のループ回数が増加し、タグの紐づきが多い記事が増えるほど JOIN 後の行数も膨らんでいくことが想像できます。

最終的に返すのは 5 件であるにもかかわらず、全記事を対象にした JOIN・ソート・GROUP BY が毎回実行される状態になっているため、運用が続いていくほどパフォーマンスに影響を与えかねないクエリになってしまっていることがわかります。

修正後のクエリ

top_articles CTE で距離計算と LIMIT 5 を先に完結させ、その後に JOIN する設計に変更しました。

WITH target AS (
    SELECT embedding FROM article_embeddings WHERE article_id = '...'
),
top_articles AS (
    SELECT ae.article_id, ae.embedding <=> t.embedding AS distance
    FROM article_embeddings ae
    CROSS JOIN target t
    JOIN articles a ON a.id = ae.article_id
    WHERE ae.article_id != '...'
        AND a.status = 'published'
        AND a.deleted_at IS NULL
    ORDER BY ae.embedding <=> t.embedding
    LIMIT 5
)
SELECT
    a.id, a.title, a.slug, a.summary, a.category_id,
    c.name AS category_name, c.slug AS category_slug, c.color AS category_color,
    COALESCE(
        json_agg(
            json_build_object('id', tg.id, 'name', tg.name, 'slug', tg.slug, 'description', tg.description)
        ) FILTER (WHERE tg.id IS NOT NULL),
        '[]'
    ) AS tags,
    ta.distance
FROM top_articles ta
JOIN articles a ON a.id = ta.article_id
LEFT JOIN categories c ON a.category_id = c.id
LEFT JOIN article_tags at ON a.id = at.article_id
LEFT JOIN tags tg ON at.tag_id = tg.id
GROUP BY a.id, a.title, a.slug, a.summary, a.category_id,
    c.id, c.name, c.slug, c.color, ta.distance
ORDER BY ta.distance;

---

改修後の EXPLAIN 結果

こちらもまずはターミナルから EXPLAIN を実行してみます。

                                                                                              QUERY PLAN                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=21.53..21.56 rows=1 width=1884) (actual time=0.206..0.216 rows=5 loops=1)
   Group Key: ((ae.embedding <=> article_embeddings.embedding)), a.id, c.id
   Buffers: shared hit=248
   ->  Sort  (cost=21.53..21.53 rows=1 width=1925) (actual time=0.196..0.197 rows=10 loops=1)
         Sort Key: ((ae.embedding <=> article_embeddings.embedding)), a.id, c.id
         Sort Method: quicksort  Memory: 28kB
         Buffers: shared hit=248
         ->  Nested Loop Left Join  (cost=11.63..21.52 rows=1 width=1925) (actual time=0.171..0.185 rows=10 loops=1)
               Buffers: shared hit=239
               ->  Nested Loop Left Join  (cost=11.48..21.12 rows=1 width=1856) (actual time=0.169..0.178 rows=10 loops=1)
                     Buffers: shared hit=219
                     ->  Nested Loop Left Join  (cost=11.34..20.68 rows=1 width=1852) (actual time=0.166..0.172 rows=5 loops=1)
                           Buffers: shared hit=209
                           ->  Nested Loop  (cost=11.20..19.54 rows=1 width=1380) (actual time=0.165..0.168 rows=5 loops=1)
                                 Buffers: shared hit=199
                                 ->  Limit  (cost=11.06..11.06 rows=1 width=24) (actual time=0.162..0.163 rows=5 loops=1)
                                       Buffers: shared hit=189
                                       ->  Sort  (cost=11.06..11.06 rows=1 width=24) (actual time=0.161..0.162 rows=5 loops=1)
                                             Sort Key: ((ae.embedding <=> article_embeddings.embedding))
                                             Sort Method: top-N heapsort  Memory: 25kB
                                             Buffers: shared hit=189
                                             ->  Nested Loop  (cost=8.17..11.05 rows=1 width=24) (actual time=0.041..0.158 rows=30 loops=1)
                                                   Buffers: shared hit=189
                                                   ->  Hash Join  (cost=8.17..9.65 rows=1 width=34) (actual time=0.020..0.024 rows=30 loops=1)
                                                         Hash Cond: (ae.article_id = a_1.id)
                                                         Buffers: shared hit=6
                                                         ->  Seq Scan on article_embeddings ae  (cost=0.00..1.39 rows=30 width=34) (actual time=0.002..0.003 rows=30 loops=1)
                                                               Filter: (article_id <> '5f7abce7-d1cb-424c-b4b2-6bde9276207d'::uuid)
                                                               Rows Removed by Filter: 1
                                                               Buffers: shared hit=1
                                                         ->  Hash  (cost=8.16..8.16 rows=1 width=16) (actual time=0.014..0.014 rows=31 loops=1)
                                                               Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                                               Buffers: shared hit=5
                                                               ->  Index Scan using idx_articles_status on articles a_1  (cost=0.14..8.16 rows=1 width=16) (actual time=0.006..0.011 rows=31 loops=1)
                                                                     Index Cond: ((status)::text = 'published'::text)
                                                                     Filter: (deleted_at IS NULL)
                                                                     Buffers: shared hit=5
                                                   ->  Seq Scan on article_embeddings  (cost=0.00..1.39 rows=1 width=18) (actual time=0.000..0.001 rows=1 loops=30)
                                                         Filter: (article_id = '5f7abce7-d1cb-424c-b4b2-6bde9276207d'::uuid)
                                                         Rows Removed by Filter: 30
                                                         Buffers: shared hit=30
                                 ->  Index Scan using articles_pkey on articles a  (cost=0.14..8.16 rows=1 width=1372) (actual time=0.001..0.001 rows=1 loops=5)
                                       Index Cond: (id = ae.article_id)
                                       Buffers: shared hit=10
                           ->  Index Scan using categories_pkey on categories c  (cost=0.14..1.12 rows=1 width=472) (actual time=0.000..0.000 rows=1 loops=5)
                                 Index Cond: (id = a.category_id)
                                 Buffers: shared hit=10
                     ->  Index Scan using idx_article_tags_article_id on article_tags at  (cost=0.14..0.42 rows=2 width=20) (actual time=0.001..0.001 rows=2 loops=5)
                           Index Cond: (article_id = a.id)
                           Buffers: shared hit=10
               ->  Index Scan using tags_pkey on tags tg  (cost=0.14..0.39 rows=1 width=73) (actual time=0.001..0.001 rows=1 loops=10)
                     Index Cond: (id = at.tag_id)
                     Buffers: shared hit=20
 Planning:
   Buffers: shared hit=470
 Planning Time: 1.304 ms
 Execution Time: 0.263 ms
(57 rows)

ビジュアルで表示するとこのようになります。

修正前と比較して、以下の 4 点が改善されていることがわかります。

• Limit #7 がツリーの中間に現れている: Before では最上部にあった Limit が中段に降りており、top_articles CTE の中で上位 5 件への絞り込みが早い段階で行われている。
• Limit #7 より上のエッジがすべて「5」: Before では 63 や 1.04K だった行数が、5 件に絞られた状態で JOIN が実行されている。
• Index Scan のラベルが「クリティカル → 低」に変化: JOIN の対象が 5 件になったことで繰り返し回数が大幅に減少。
• Seq Scan の行数が 1.04K → 30 に激減: Before の 1.04K と比べて約 97% 削減されている。

実行時間が約 65%、ブロック読み込みが約 64% 削減されました。※ループ回数が 65 → 1 になっているのもわかるかと思います

top_articles CTE で距離計算と絞り込みを先に完結させたことで、 categories, tags への JOIN が全記事ではなく 5 件分のみに限定されました。

これにより、記事数やタグ数が増えても JOIN・ソートの対象が膨らまない構造にできたかと思います。

---

まとめ

VS Code の PostgreSQL 拡張機能でビジュアル表示すると、コンソールで出力された文字列のデータを読むよりも速く問題のノードに気づけます。

最近は AI エージェントなどを用いてこの辺りのパフォーマンス解消を行うケースが多いかと思うのですが、特にリレーショナルデータベースでアプリケーションを運用している場合、サービスの規模が大きくなってくるに連れて関連するテーブルも増え、このような計測結果も出してもらったはいいが、即座に理解することが難しいケースも多々あるかと思います。

ほか、影響範囲が大きいテーブルの場合はチームを跨ぎ複数人で認識を共有するケースなども運用中に発生するケースも多いかと思います。

こういった状況下で、さっと手軽にビジュアライズして出せるようになるのはとても便利な機能だなと感じました。

ほかにも良さそうな機能があればまた紹介できたらと思います。