質問: 数百万件のポリゴンデータがあってその重なり関係にあるペアを計算したいです --- PostgreSQL に ``` tmp { id: int --- 1からはじまる連続ID way: geometry --- ポリゴンカラム way_area: float --- ポリゴンの面積 } ``` テーブルをつくって geometry 型カラムにインデックスを貼りました ``` select * from tmp t1, tmp t2 where t1.way && t2.way and st_overlaps(t1.way, t2.way) and t1.id < 100 ``` で動かしてみたところ 100 件でも数秒かかるので数百万となるとかなり長時間かかりそうな感じなので 100行 ずつで区切って並列動作させるよう以下のようなシェルを作りました ``` # 開始ID start_id=$1 end_id=$(psql -c 'SELECT MAX(id) FROM tmp' -A | sed -n 2P) # 0 100 200 300 ... という数字を生成して parallel に渡す seq $start_id 100 $end_id | parallel -j 100% './task.sh {} 100' ``` task.sh ``` #!/bin/bash start=$1 # 0 100 200 300 ... という可変数がわたる pagesize=$2 # 100固定 end=$((start+pagesize)) query=$(cat << EOS INSERT INTO relations(parent, child) SELECT -- 面積の大きい方を親とする CASE WHEN g1.way_area >= g2.way_area THEN g1.id ELSE g2.id END, CASE WHEN g1.way_area >= g2.way_area THEN g2.id ELSE g1.id END FROM tmp g1, tmp g2 WHERE g1.id >= $start AND g1.id < $end -- 100 行ずつ AND g1.id > g2.id -- 組み合わせは片方だけ計算 AND g1.way && g2.way AND ST_overlaps(g1.way::geometry, g2.way::geometry); -- 重なり判定 EOS ) psql -c "$query" ``` 最初の数万件は数秒〜数十秒ペースで動いていたんですが 数万件ほどたったところで処理がとまってしまいます 書き込み先の relations はこんな感じです ``` CREATE TABLE IF NOT EXISTS relations ( id bigserial PRIMARY KEY, parent bigint, child bigint ); CREATE INDEX ON relations (parent); CREATE INDEX ON relations (child); ``` 重なり計算する時に巨大で複雑なポリゴンが含まれてるのとかなと 停止したクエリの select 部分だけ実行しても数秒で終わります プロセスを見ると ``` postgres 8004 2055 99 Sep24 ? 2-20:36:33 postgres: postgres postgres [local] INSERT ``` こういうプロセスが残ってしまうようで重なり計算する SELECT 部分ではなく INSERT 部分でデッドロック?がおこってるみたいですが relations に対しては読み込みを行ってないので循環参照が起こるはずないのですが 原因や原因の調べ方がわかる方いたら 助けていただけると助かります
回答: PostgreSQLはデッドロックの検知機構を持っているので、デッドロックの可能性は低いと思います。(検知した場合、一方のトランザクションは失敗する) ですので時間はかかっているが処理は進んでいると予想します。時間がかかる原因の調査ポイントは次のものが考えられます。 1. I/Oパワー不足 → `sar`や`iostat`でI/O量を確認する。I/O量が多い場合、`shared_buffer`を増やすことで改善するかも知れない。 2. CPUパワー不足 → `sar`や`mpstat`でCPU使用率を確認する。CPU使用率が高い場合、並列度が性能限界を超えているので、並列度を下げる。 3. 実行計画が(短時間に多量のINSERTを行うことにより)最適でなくなる → 「処理が終わる分量で」`EXPLAIN ANALYZE`してシーケンススキャン等、実行が遅くなる実行計画になってないか確認する。そういう場合は、途中で`VACUUM`を実行してみると実行計画が改善されるかも知れない。