親愛なるジョンからの手紙

雑記です。ゲームとか漫画とかプログラミングとか。

PostGISでST_Intersectsした際にIndexがうまく使われていなかったのをダーティに解消した話

結論

ST_Intersectsでデータベース内にないパラメータを使うときは、先に&&演算子でBounding Box同士での比較を行っておくと、インデックスがうまいこと使われて早い。

postgresql - PostGIS doesn't use spatial index with ST_Intersects - Geographic Information Systems Stack Exchange

環境

PostgreSQL 11.1系 PostGIS 2.4系 DBサーバ用の仮想環境:CentOS 7系

詳細な内容

PostGIS使って、データベース内にある地理情報から、ある二次元円内のGeometry型地理情報をぶっこぬいてくるとき、以下のような感じで行ってます。

SELECT
  geometry
FROM
  geometry_table
WHERE
  ST_Intersects(geometry, ST_Buffer(ST_SetSRID( ST_POINT( 139.76719 , 35.6811 ) , 4326 )::geometry, {radius}));

ST_Bufferで中心点と半径を指定した円を作り、作成した円とデータベース内にあるジオメトリの接触判定をし、これを以て範囲とする、というやり方です。

ところがどっこい、プロダクトコードで検証してみたところこのSQL(実際に試したクエリは業務上明かせないためぼかしてあります。上記のSQLをサブクエリ内・別のWhere条件付き、Inner Join利用で行ったものとして考えてください)が遅く、EXPLAINしてみると「これgeometryに張ったgistインデックス使われてなくね……?」という結果に。

これを解消するためにいろいろと試してみてEXPLAINしてみたのですが、内部的にインデックス利用していなさそうというか、そもそも何しているかまったくわからん、という感じな実行計画しか出てきませんでした。

Merge Join  (cost=21135.61..30887.07 rows=917 width=87) (actual time=374.015..1207.340 rows=11 loops=3)
                 Merge Cond: ((geometry_table.primary_key)::text = (other_table.foreign_key)::text)
                 ->  Parallel Index Scan using geometry_table_pkey on   (cost=0.42..71274.59 rows=12823 width=2441) (actual time=335.955..1160.354 rows=11 loops=3)
                       Filter: (((primary_key)::text = ($1)::text) AND st_intersects(geometry, $2))
                       Rows Removed by Filter: 61382
~~~~~~
~~~~~~
 Planning Time: 1.213 ms
 Execution Time: 1273.504 ms

というわけで、この記事は上記現象を解消するためにやったこととか考えてみた仮説とかを並べていく記事となります。

結局どういう結果になったかは一番上を見てください。

仮説1: サブクエリ内で関数使うと実行計画がいい感じに読んでくれない

最初はコレを考えていたんですが、SQLを変えてゴリゴリと実行計画読んでみた感じ、特に表示や速度に代わりはありませんでした。

プランナがキャッシュをもとに計画を立てている等々も考え、VACUUMしたりしながら試してみたのですが、ほぼほぼ誤差な感じ。

仮説2:PostgreSQLPostGISのバージョンの相性が悪い

PostGISPostgreSQLのエクステンションとなります。そのため、バージョン相性がよろしくないと、プランナに正しくインデックスの優先順位が伝わらなくなるのでは?という仮説を立てました。

なお、現状本番環境で動いているプロダクトがあるので、正しくても修正が大規模になります。検証するにしても他の手段試してからの後回し。

仮説3:別インデックスがあるとそちらを優先するようになっている

www.finds.jp

3.3. 空間クエリを作成するにはどうするのですか? 他のデータベースクエリを作るのと同じで、返り値、関数、テストのSQLの組み合わせです。

空間クエリでは、クエリを作成する際に心を平静に保つための重要な二つの問題があります。 一つは、使用することができる空間インデックスがあるか、です。もう一つは、多数のジオメトリを相手に計算量の多い計算を行っているか、です。

一般的に、フィーチャーのバウンディングボックスがインタセクト (交差)しているかをテストするインタセクト演算子 (&&)を使います。&&演算子が便利な理由は、速度向上のために空間インデックスが付けられているなら、&&演算子は空間インデックスを使うからです。これによって、クエリの速度はとてもとても速くなります。

また、検索結果をより狭めるために、Distance(), ST_Intersects(), ST_Contains(), ST_Within() などといった空間関数を使うことでしょう。ほとんどの空間クエリは、インデクスのテストと空間関数のテストを含みます。インデクスのテストで返ってくるタプルを、求める条件に合致するかもしれないタプルのみとして、タプルの数を制限します。それから、空間関数で確実な条件のテストを行います

ST_Intersectsの中身見た感じその通りになってはいるんですが、実際の動作を見る限りだとそうは見えませんでした。

で、原因として思いついたのが、「別のインデックスでスキャンしたら、gistのスキャンはスキップするように実行計画がプランニングされるんじゃね……?」という仮説。

これは別に指定しているAND条件を外すと、そもそもの要件を満たさなくなるので、検証してもそれをプロダクトに反映させるためのSQL再設計が必要になります。というわけで同じく手段としてはあとまわし。

仮説4: そもそもクエリが全部クソ

うるせ~~~~~~~~~!!!

知らね~~~~~~~~~~~!!!

PostgreS

QL

結局どうしたか

方法考えつつ空間インデックス周りの資料読んでて目に留まったのが以下の文です。

postgis.net

15.2. Index-Only Queries Most of the commonly used functions in PostGIS (ST_Contains, ST_Intersects, ST_DWithin, etc) include an index filter automatically. But some functions (e.g., ST_Relate) do not include and index filter. To do a bounding-box search using the index (and no filtering), make use of the && operator. For geometries, the && operator means “bounding boxes overlap or touch” in the same way that for number the = operator means “values are the same”.

Bounding Boxはざっくりいうと対象地理上図形に外接する四角形なのですが、&&演算子を使うとこのBounding Boxで地理上図形の重なり判定を行った上、インデックスも使ってくれる模様。 なので、脳死で対応するため、&&で重なり判定をしてざっくり削ってみた後、ST_Intersectsを利用してもっと正確な重なり判定をする、という手法をとってみました。

これは仮説3でも同様のことを行っているのですが、どうも関数内部に指定された&&では、プランナがうまいこととってくれないパターンもあるのかもしれません。 ですが、今回はもうできればいいやくらいの対応でおまじない的にやってしまう方がよいと判断しました。

というわけで、実際にやったものとは違いますが、以下のような形に変更し、明示的に&&で絞った後にST_Intersectsする形に変更しました。

SELECT
  geometry
FROM
  geometry_table
WHERE
  geometry && ST_Buffer(ST_SetSRID( ST_POINT( 139.76719 , 35.6811 ) , {srid} ), {radius}) AND
  ST_Intersects(geometry, ST_Buffer(ST_SetSRID( ST_POINT( 139.76719 , 35.6811 ) , {srid} )::geometry, {radius}));

結果として以下のように、うまいことgist使ってくれている形になりました。

   ->  Hash  (cost=109.16..109.16 rows=8 width=2377) (actual time=14.110..14.110 rows=36 loops=1) 
         Buckets: 1024  Batches: 1  Memory Usage: 42kB
         ->  Bitmap Index Scan on idx_geometry_table_geometry  (cost=0.00..4.47 rows=25 width=0) (actual time=0.089..0.089 rows=46 loops=1) 
               Index Cond: (geometry && '{buffer}'::geometry) 

~~~~~~
~~~~~~
 Planning Time: 1.732 ms
 Execution Time: 33.898 ms

まとめ

仮説検証せずにとりあえず採用するのはよろしくないが、時間がない場合は&&利用した絞り込み安定なので脳死でやっておけ。

なお調べなおした結果、もう語りつくされたテクニックらしい。車輪の再開発~。 blog.chizuburari.jp

安定です。10年近く?変わらないこの安定感。いぶし銀です。

まあ、その10年近く前にST_指定があったかどうかは覚えてないですが…。

簡単に説明すると、前半の&&は、thegeomカラム内の各ジオメトリの外接矩形で、インデクスによる篩をかけています。

その後のST_Intersectsで、正確な交差判定を行ってます。

この辺はもう定石なので、いちいち&&しなくてもST_Intersectsをする際には自動で&&処理をするようにするとかしないとかいう話があった気がしますが、実際に対応したかは未確認です。

もし最新版でしていたとしても、PostGISのバージョン依存しないようにするなら、やっておいて損はないかなと思います。