82735


数百万件のポリゴンデータがあってその重なり関係にあるペアを計算したいです

* * *

PostgreSQL に

[code]

    tmp {
      id: int             --- 1からはじまる連続ID
      way: geometry       --- ポリゴンカラム
      way_area: float     --- ポリゴンの面積
    }
    
[/code]

テーブルをつくって geometry 型カラムにインデックスを貼りました

[code]

    select * from tmp t1, tmp t2
    where t1.way && t2.way and st_overlaps(t1.way, t2.way)
    and t1.id < 100
    
[/code]

で動かしてみたところ 100 件でも数秒かかるので数百万となるとかなり長時間かかりそうな感じなので  
100行 ずつで区切って並列動作させるよう以下のようなシェルを作りました

[code]

    # 開始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'
    
[/code]

task.sh

[code]

    #!/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"
    
[/code]

最初の数万件は数秒〜数十秒ペースで動いていたんですが  
数万件ほどたったところで処理がとまってしまいます

書き込み先の relations はこんな感じです

[code]

    CREATE TABLE IF NOT EXISTS relations (
      id bigserial PRIMARY KEY,
      parent bigint,
      child bigint
    );
    CREATE INDEX ON relations (parent);
    CREATE INDEX ON relations (child);
    
[/code]

重なり計算する時に巨大で複雑なポリゴンが含まれてるのとかなと  
停止したクエリの select 部分だけ実行しても数秒で終わります

プロセスを見ると

[code]

    postgres  8004  2055 99 Sep24 ?        2-20:36:33 postgres: postgres postgres [local] INSERT
    
[/code]

こういうプロセスが残ってしまうようで重なり計算する SELECT 部分ではなく  
INSERT 部分でデッドロック？がおこってるみたいですが  
relations に対しては読み込みを行ってないので循環参照が起こるはずないのですが

原因や原因の調べ方がわかる方いたら  
助けていただけると助かります


CC BY-SA 4.0
2021-09-27T03:48:00.780
82704
2021-09-28T14:05:39.453
2021-09-27T04:09:08.360
3060
question
postgresql
PostgreSQL で INSERT が止まってしまう


PostgreSQLはデッドロックの検知機構を持っているので、デッドロックの可能性は低いと思います。(検知した場合、一方のトランザクションは失敗する)

ですので時間はかかっているが処理は進んでいると予想します。時間がかかる原因の調査ポイントは次のものが考えられます。

  1. I/Oパワー不足 → `sar`や`iostat`でI/O量を確認する。I/O量が多い場合、`shared_buffer`を増やすことで改善するかも知れない。
  2. CPUパワー不足 → `sar`や`mpstat`でCPU使用率を確認する。CPU使用率が高い場合、並列度が性能限界を超えているので、並列度を下げる。
  3. 実行計画が(短時間に多量のINSERTを行うことにより)最適でなくなる → 「処理が終わる分量で」`EXPLAIN ANALYZE`してシーケンススキャン等、実行が遅くなる実行計画になってないか確認する。そういう場合は、途中で`VACUUM`を実行してみると実行計画が改善されるかも知れない。


CC BY-SA 4.0
2021-09-28T14:05:39.453
82735
2021-09-28T14:05:39.453
20098
82704
answer