【PostgreSQL】PostGISによる空間検索のパフォーマンス改善

ある地点から100m圏内のデータで、かつ、１番近いデータを取得するという検索をしたい

前の記事で有効そうなSQLを考えたが、１リクエストで25秒もかかってしまったのでこれを改善したい

uga-box.hatenablog.com

結果はもうわかっていて、以下のように考え方を変えたらうまくいった

AS-IS

1. WHERE句で100m圏内で絞り込む
2. 絞り込んだ結果を距離近い順にソート
3. このうちの最初の１件を取得

TO-BE

1. WITH句で距離近い順にソートしたうちの最初の１件を取得（距離を取得しておく）
2. 1.のテーブルを結合し、WHERE句でその１件が100m圏内であるかを判別する

AS-ISの①は以下の条件式になるが、どうやらこの絞り込みにはインデックスが使われないようで、ここがボトルネックになっていた

where ST_Distance(#{coordinate}, C.COORDINATE_GEOGRAPHY) <= #{distance}

一方で、近い順にソートする以下の式ではインデックスが使われるようなので高速だった（ドキュメントより）

order by C.COORDINATE_GEOGRAPHY <-> #{coordinate} asc

PostgreSQL solves the nearest neighbor problem by introducing an “order by distance” (<->) operator that induces the database to use an index to speed up a sorted return set.
29. Nearest-Neighbour Searching — Introduction to PostGIS

なので、WHERE句を使わないようにSQLを修正したところ１リクエストで5ミリ秒になり劇的に改善した