ST_DWithinは指数関数的に遅い。私が間違っていることを見つけることができません

Aug 20 2020
  • PostGISバージョン:3.1
  • PostgreSQLバージョン:12.3
  • 私が使用しているマシンには、126G RAM、48CPUコアがあります。

情報:

私はPostGISを始めています。

私の目標は、2つのポイント間で一致するすべてのデータを取得することです。

lv.geopointとsub.geopointはどちらもGEOGRAPHYポイント(SRID:4326)であり、GISTインデックスがあります。

私のサブSELECTは約3k行を返しますが、私の 'valeurs_foncieres'テーブルには14000 000行があります。

valeurs_foncieres.id、caracteristiques_2018.id、caracteristiques_2018.num_acc、usagers_2018.id、usagers_2018.num_acc、vehicules_2018.id、vehicules_2018.num_accにBTREEインデックスがあります。

問題:

ST_DWithinの距離を大きくすると、クエリが指数関数的に遅くなります。

  • 精度100:2秒
  • 精度1000:10秒
  • 精度10000:6分

クエリは次のとおりです。

SELECT
    DISTINCT(sub.num_acc),
    sub.geopoint,
    sub.id
FROM
    (
    SELECT
        DISTINCT(u.num_acc) AS unumacc, c.*
    FROM
        usagers_2018 u
    INNER JOIN vehicules_2018 v ON
        u.num_acc = v.num_acc
    INNER JOIN caracteristiques_2018 c ON
        u.num_acc = c.num_acc
    WHERE
        u.grav = '2'
    ORDER BY
        c.id
) AS sub
INNER JOIN valeurs_foncieres vf ON
    ST_DWithin(vf.geopoint,
    sub.geog,
    1000,
    FALSE);

説明は次のとおりです。

HashAggregate  (cost=265577998.10..265578004.81 rows=671 width=49)
  Group Key: c.num_acc, c.geopoint, c.id
  ->  Nested Loop  (cost=9948.38..264845621.97 rows=97650150 width=49)
        ->  Unique  (cost=9947.84..10316.67 rows=6706 width=170)
              ->  Sort  (cost=9947.84..9964.60 rows=6706 width=170)
                    Sort Key: c.id, u.num_acc, c.an, c.mois, c.jour, c.hrmn, c.lum, c.agg, c."int", c.atm, c.col, c.com, c.adr, c.gps, c.lat, c.long, c.dep, c.lat_gps, c.long_gps, c.geopoint, c.geog
                    ->  Gather  (cost=3200.48..9521.63 rows=6706 width=170)
                          Workers Planned: 1
                          ->  Nested Loop  (cost=2200.48..7851.03 rows=3945 width=170)
                                Join Filter: ((u.num_acc)::text = (v.num_acc)::text)
                                ->  Parallel Hash Join  (cost=2200.06..6686.70 rows=2075 width=170)
                                      Hash Cond: ((c.num_acc)::text = (u.num_acc)::text)
                                      ->  Parallel Seq Scan on caracteristiques_2018 c  (cost=0.00..2859.90 rows=33990 width=157)
                                      ->  Parallel Hash  (cost=2174.12..2174.12 rows=2075 width=13)
                                            ->  Parallel Seq Scan on usagers_2018 u  (cost=0.00..2174.12 rows=2075 width=13)
                                                  Filter: ((grav)::text = '2'::text)
                                ->  Index Only Scan using vehicules_2018_num_acc_idx on vehicules_2018 v  (cost=0.42..0.54 rows=2 width=13)
                                      Index Cond: (num_acc = (c.num_acc)::text)
        ->  Index Scan using valeurs_foncieres_geopoint_idx on valeurs_foncieres vf  (cost=0.54..39477.72 rows=1456 width=32)
              Index Cond: (geopoint && _st_expand(c.geog, '1000'::double precision))
              Filter: st_dwithin(geopoint, c.geog, '1000'::double precision, false)
JIT:
  Functions: 30
  Options: Inlining true, Optimization true, Expressions true, Deforming true

質問:

これは正常ですか?どうすれば実行時間を短縮できますか?

回答

3 robinloche Aug 21 2020 at 09:46

14 000000行は小さくありません。また、お持ちのgeogが均一に分布している場合、半径x10を掛けると関係するポイントの数は約x100になります(円の面積はr²に依存します)。したがって、時間の増加は2乗に見えるのが普通です。ここではそれ以上のように見えますが、操作するデータが多いほど、すべてのキャッシュジェスチャとディスク呼び出しのために必要になる可能性のある操作が多くなります(小さなデータや大きなキャッシュには当てはまりません)。

ここで説明は問題ないようです。インデックスを使用しているので問題ありません。必ずテーブルをVACUUMANALYZEする必要がありますが、あまり変わらないはずです。

そうしなかった場合にできる主なことは、postgresqlを微調整することです。デフォルトでは、パラメーターは非常に保守的です。大きなサーバーがある場合は、パラメーターを変更して適切に使用する必要があります。これらのパラメータは、Linuxの次のファイルで処理できます:/etc/postgresql/12/main/postgresql.conf次に、postgresを再起動する必要があります(質問がある場合は、インターネットでドキュメントを簡単に見つけることができます)。通常、私が変更するのは次のとおりです(約120Goおよび48 CPUのRAMに適合):

  • shared_buffers = 30GB
  • Effective_cache_size = 80GB
  • work_mem = 256MB
  • Maintenance_work_mem = 5GB
  • autovacuum_work_mem = 5GB
  • effective_io_concurrency = 200(SSDの場合、またはディスクの場合は2)
  • max_worker_processes = 48
  • max_parallel_workers = 48
  • max_parallel_workers_per_gather = 12
  • wal_buffers = 16MB
  • min_wal_size = 1GB
  • max_wal_size = 2GB

それらはおそらく完璧ではなく、一部は私が見つけたドキュメントのために、一部は大きな要求に応じて試行錯誤したために定義されています。しかし、postgresqlをまったく構成しなかった場合(開始したと言った場合)、大きなリクエストのパフォーマンスに大きな違いが生じるはずです(それほど大きくはありませんが、影響があるはずです)。ジオメトリデータは通常大きいため、postgresqlの通常の使用よりも多くのスペースが必要になります。また、可能であれば、必ずデータをSSDに配置してください。これも、大きな影響を与える可能性があります。

編集

リクエストを読み直したところ、numaccで1行だけ保持した後、Xメートルのすべてのポイントが必要な理由がよくわかりません。クエリ全体を入力しなかったか、実際には1つのポイントしか必要ありません。だから私はあなたが本当に望んでいたのが最も近いポイントを取得することだった場合に備えてそれを書き直します。各ステップの一時テーブルを作成するMATERIALIZEDCTEを使用しましたが、パフォーマンスが大幅に向上する場合があるため、最も近い隣人だけでなくすべてのポイントを取得したい場合は、ORDERを削除してそのまま実行してみてください。 BYと最後のINNERJOINLATERALのLIMIT。そしてもちろん、ここではST_DWithinで検索を制限しますが、真に最も近い隣人が必要な場合は、このWHEREを削除できます。

WITH usg AS MATERIALIZED
(
    SELECT
            DISTINCT(u.num_acc) AS unumacc
            , c.*
        FROM
            usagers_2018 u
        WHERE
            u.grav = '2'
        INNER JOIN caracteristiques_2018 c ON
            u.num_acc = c.num_acc
        ORDER BY
            c.id
), sub AS MATERIALIZED
(
    SELECT
            DISTINCT(usg.unumacc)
            , usg.*
            , v.*
        FROM
            usg
        INNER JOIN vehicules_2018 v ON
            usg.num_acc = v.num_acc
)
SELECT
        sub.*
        , vf.*
    FROM sub
    INNER JOIN LATERAL 
        (
            SELECT
                    vf.*
                FROM
                    valeurs_foncieres vf
                WHERE
                    ST_DWithin(
                        vf.geopoint
                        ,sub.geog
                        , 1000
                        ,FALSE
                    )
                ORDER BY vf.geopoint <-> sub.geog
                LIMIT 1
        )   
    ON TRUE;