SQL Academy · レッスン

空間インデックス（GiST）

Q: 「空間インデックス（GiST）」で何を学びますか？

位置情報クエリを高速化します ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

位置情報クエリを高速化します

「空間インデックス（GiST）」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン4/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

位置情報クエリが遅くなる理由

何百万件ものレストランの位置情報を含むテーブルを想像してください。「現在地から5 km以内にあるすべてのレストランを検索する」と、データベースは距離を計算するためにすべての行を1件ずつ確認しなければなりません。これはシーケンシャルスキャンと呼ばれ、テーブルが大きくなるにつれて非常に遅くなります。

空間インデックスは、ジオメトリデータをツリー構造に整理することでこの問題を解決します。データベースはテーブルの大部分を即座に読み飛ばせるようになります。

GiSTインデックスとは

GiSTはGeneralized Search Treeの略です。これはPostgreSQLに組み込まれた柔軟なインデックスフレームワークで、幾何学的な形状やPostGISのジオメトリなど、多くのデータ型をサポートします。

整数や文字列のように並べ替え可能な値を扱うB-treeインデックスとは異なり、GiSTはポイント、ポリゴン、ラインなどの多次元データにインデックスを作成できます。PostGISは内部でGiSTを使用して空間インデックスを構築します。

空間インデックスの作成

ジオメトリ列にGiSTインデックスを作成するのは簡単です。USING gist句を付けたCREATE INDEXを使用します。この1つの文だけで、クエリの実行時間を数分から数ミリ秒に短縮できる場合があります。

CREATE INDEX idx_restaurants_geom
  ON restaurants
  USING gist (geom);

GiSTの仕組み：バウンディングボックス

GiSTの空間インデックスは、正確なジオメトリを保存しません。代わりに、各ジオメトリを囲む最小の長方形であるバウンディングボックスを保存します。ツリーは、各レベルで近くにあるバウンディングボックスをまとめるように構築されます。

クエリが実行されると、PostgreSQLはツリーを下りながら、検索範囲と重ならないバウンディングボックスを持つ枝を除外します。その後、残った候補行だけが正確に検査されます。この2段階の方式（インデックス検索と再検査）は非常に効率的です。

サンプルテーブルの設定

インデックスの動作を確認する前に、都市のポイントを格納するサンプルテーブルを作成し、いくつかの行を追加しましょう。geom列には、WGS 84（SRID 4326）における各都市をPointとして格納します。

CREATE TABLE cities (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  geom GEOMETRY(Point, 4326)
);

INSERT INTO cities (name, geom) VALUES
  ('Paris',    ST_SetSRID(ST_MakePoint(2.3522,  48.8566), 4326)),
  ('Berlin',   ST_SetSRID(ST_MakePoint(13.4050, 52.5200), 4326)),
  ('Madrid',   ST_SetSRID(ST_MakePoint(-3.7038, 40.4168), 4326)),
  ('Rome',     ST_SetSRID(ST_MakePoint(12.4964, 41.9028), 4326)),
  ('Warsaw',   ST_SetSRID(ST_MakePoint(21.0122, 52.2297), 4326));

GiSTインデックスの追加

テーブルへのデータ投入が完了したら、geom列にGiSTインデックスを追加します。数百万行を持つ本番テーブルでは、この文の実行に数分かかる場合がありますが、実行が必要なのは一度だけです。その後は、この列に対するすべての空間クエリで自動的にメリットを得られます。

CREATE INDEX idx_cities_geom
  ON cities
  USING gist (geom);

-- Verify the index exists
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'cities';

バウンディングボックス演算子 &&

PostGISには、2つのバウンディングボックスが重なるかどうかを判定する&&演算子があります。この演算子はインデックス対応であり、プランナーはGiSTインデックスを自動的に使用します。正確なジオメトリの交差を計算するよりもはるかに高速で、簡易的な事前フィルターとしてよく使われます。

-- Find cities whose bounding box overlaps a search rectangle
SELECT name
FROM   cities
WHERE  geom && ST_MakeEnvelope(-5, 40, 15, 50, 4326);

<->による最近傍検索

<->演算子は2つのジオメトリ間の距離を返し、GiSTによる高速化にも対応しています。これをORDER BY ... LIMITと組み合わせると、完全なテーブルスキャンを必要としない非常に高速なk最近傍（KNN）クエリを実行できます。

-- Find the 3 cities closest to a reference point (Brussels)
SELECT name,
       ST_Distance(
         geom::geography,
         ST_SetSRID(ST_MakePoint(4.3517, 50.8503), 4326)::geography
       ) / 1000 AS distance_km
FROM   cities
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(4.3517, 50.8503), 4326)
LIMIT  3;

EXPLAINによるインデックス使用状況の確認

プランナーが実際にインデックスを使用していることを確認するため、必ずEXPLAINまたはEXPLAIN ANALYZEを使用してください。出力にBitmap Index ScanまたはIndex Scan using idx_cities_geomが含まれているか確認します。代わりにSeq Scanが表示される場合は、テーブルが小さすぎるため、プランナーがインデックスを優先していない可能性があります。

EXPLAIN
SELECT name
FROM   cities
WHERE  geom && ST_MakeEnvelope(-5, 40, 15, 50, 4326);

並行インデックス作成

標準のCREATE INDEXコマンドで大規模な空間インデックスを構築すると、書き込みのためにテーブルがロックされます。本番環境では、挿入や更新をブロックせずにインデックスを構築できるCREATE INDEX CONCURRENTLYを使用してください。その代わり、実行時間が長くなり、トランザクションブロック内では実行できません。

-- Safe for production tables (no write lock)
CREATE INDEX CONCURRENTLY idx_restaurants_geom
  ON restaurants
  USING gist (geom);

空間インデックスのメンテナンス

時間の経過とともに、頻繁な挿入、更新、削除によってインデックスの肥大化が起こることがあります。インデックスが断片化して、効率が低下する状態です。REINDEXでインデックスを再構築するか、定期的にVACUUM ANALYZEを実行して統計情報を更新すると、クエリプランナーがより適切な判断を行えるようになります。

-- Rebuild the index to remove bloat
REINDEX INDEX idx_cities_geom;

-- Update planner statistics for the table
ANALYZE cities;

理解度チェック：GiSTインデックス

PostGISにおけるGiSTを使った空間インデックスについての理解度を確認しましょう。

まとめ：GiSTによる空間インデックス

このレッスンでは、位置情報クエリで高いパフォーマンスを得るために空間インデックスが不可欠な理由と、PostgreSQLおよびPostGISでGiSTがそれを実現する仕組みを学びました。

重要なポイント：

GiST（Generalized Search Tree）は、多次元のジオメトリデータをサポートする柔軟なインデックス型です。
CREATE INDEX ... USING gist (geom)で空間インデックスを作成します。
GiSTはバウンディングボックスを保存し、検索ツリーを枝刈りすることで、完全なテーブルスキャンを回避します。
&&演算子（バウンディングボックスの重なり）と<->演算子（距離／KNN）は、どちらもGiSTによる高速化に対応しています。
EXPLAINでインデックスの使用状況を確認し、本番環境ではCREATE INDEX CONCURRENTLYを使用して書き込みロックを回避します。
REINDEXとANALYZEでインデックスをメンテナンスし、時間が経過してもクエリを高速に保ちます。

よくある質問

「空間インデックス（GiST）」レッスンは無料ですか？

はい。「空間インデックス（GiST）」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「空間インデックス（GiST）」で何を学びますか？

位置情報クエリを高速化しますブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン4/4です。

「空間インデックス（GiST）」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Academyに戻る