PostGISでST_Intersectsした際にIndexがうまく使われていなかったのをダーティに解消した話
結論
ST_Intersectsでデータベース内にないパラメータを使うときは、先に&&演算子でBounding Box同士での比較を行っておくと、インデックスがうまいこと使われて早い。
環境
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:PostgreSQLとPostGISのバージョンの相性が悪い
PostGISはPostgreSQLのエクステンションとなります。そのため、バージョン相性がよろしくないと、プランナに正しくインデックスの優先順位が伝わらなくなるのでは?という仮説を立てました。
なお、現状本番環境で動いているプロダクトがあるので、正しくても修正が大規模になります。検証するにしても他の手段試してからの後回し。
仮説3:別インデックスがあるとそちらを優先するようになっている
3.3. 空間クエリを作成するにはどうするのですか? 他のデータベースクエリを作るのと同じで、返り値、関数、テストのSQLの組み合わせです。
空間クエリでは、クエリを作成する際に心を平静に保つための重要な二つの問題があります。 一つは、使用することができる空間インデックスがあるか、です。もう一つは、多数のジオメトリを相手に計算量の多い計算を行っているか、です。
一般的に、フィーチャーのバウンディングボックスがインタセクト (交差)しているかをテストするインタセクト演算子 (&&)を使います。&&演算子が便利な理由は、速度向上のために空間インデックスが付けられているなら、&&演算子は空間インデックスを使うからです。これによって、クエリの速度はとてもとても速くなります。
また、検索結果をより狭めるために、Distance(), ST_Intersects(), ST_Contains(), ST_Within() などといった空間関数を使うことでしょう。ほとんどの空間クエリは、インデクスのテストと空間関数のテストを含みます。インデクスのテストで返ってくるタプルを、求める条件に合致するかもしれないタプルのみとして、タプルの数を制限します。それから、空間関数で確実な条件のテストを行います
ST_Intersectsの中身見た感じその通りになってはいるんですが、実際の動作を見る限りだとそうは見えませんでした。
で、原因として思いついたのが、「別のインデックスでスキャンしたら、gistのスキャンはスキップするように実行計画がプランニングされるんじゃね……?」という仮説。
これは別に指定しているAND条件を外すと、そもそもの要件を満たさなくなるので、検証してもそれをプロダクトに反映させるためのSQL再設計が必要になります。というわけで同じく手段としてはあとまわし。
仮説4: そもそもクエリが全部クソ
うるせ~~~~~~~~~!!!
知らね~~~~~~~~~~~!!!
PostgreS
QL
結局どうしたか
方法考えつつ空間インデックス周りの資料読んでて目に留まったのが以下の文です。
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のバージョン依存しないようにするなら、やっておいて損はないかなと思います。