以下のようなPostGISでの空間結合のチューニングの記事を見かけたので試行した際のメモ。
5x Faster Spatial Join with this One Weird Trick
例えばPointとPolygonでの空間結合のような処理で、サイズの大きなポリゴンが多数存在するような場合に、簡単な手順でパフォーマンス改善ができる余地がある模様。
環境
試行の際に使用した環境は以下。
- PostgreSQL 11.1
- PostGIS 2.5.1
試行の手順
基本的には 5x Faster Spatial Join with this One Weird Trick の通りなので、流れだけ記述。
サンプルデータの入手とロード
Natural Earthからサンプルのデータとしてne_10m_admin_0_countries.zipとne_10m_populated_places.zipを入手。
zipファイルを解凍したのち、PostGISに付随する shp2pgsql のツールを用いてPostGISにロードする。
ここではテスト用に作成していた geomdb というDBに対してインポートするため、以下のようなコマンドで実行した。
shp2pgsql -s 4326 -D -I ne_10m_admin_0_countries.shp countries | psql -U postgres geomdb
shp2pgsql -s 4326 -D -I ne_10m_populated_places.shp places | psql -U postgres geomdb
それぞれcountries, placesというテーブルにロードされる。
なおshp2pgsqlコマンドのオプションは以下の通り。
4.4.2. Using the Loader
通常のパフォーマンス
何もしない状態で 5x Faster Spatial Join with this One Weird Trick に上げられているST_Intersectsを用いたクエリを実行したところ、手元のPCでは約10秒ほどの時間がかかった。
これはcountriesテーブルのうちサイズの大きな約150件ほどのポリゴンがTOASTのタプルに圧縮状態で格納されており、それらのpglz_decompress関数での解凍で多くの時間が使われているためとのこと。
チューニングとその結果
以下のようにEXTERNALのオプションを指定して、countriesテーブルのポリゴンをTOASTのタプルに非圧縮で格納する。
-- Change the storage type
ALTER TABLE countries
ALTER COLUMN geom
SET STORAGE EXTERNAL;
-- Force the column to rewrite
UPDATE countries
SET geom = ST_SetSRID(geom, 4326);
（5x Faster Spatial Join with this One Weird Trickより引用）
その上でチューニング前と同じクエリを実行したところ、処理時間が約1.3秒程度に改善された。
まとめ
-
PostGISでは、一定以上サイズの大きなジオメトリはTOASTのタプルに格納する。その際に"MAIN"のストレージオプションが使われ、大きなジオメトリは圧縮して格納される。
- 圧縮されたジオメトリが多い場合は、pglz_decompress関数での解凍に時間がかかる場合がある。
-
以下のように"EXTERNAL"のストレージオプションを使用して、非圧縮でジオメトリをTOASTのタプルに保持することで、高速化することが可能。
- この使用するストレージの変更は、特別なプログラムなど無しで対応可能。
- 但し、（当然ながら）使用するストレージの容量は増加する。
ALTER TABLE [table] ALTER COLUMN [geometry_column] SET STORAGE EXTERNAL;
UPDATE [table] SET [geometry_column] = ST_SetSRID([geometry_column], [SRID]);
- また、圧縮が必要となるような大きなサイズのジオメトリが少ない場合も、この調整による効果は小さいと考えられる。