기하 급수적으로 느리다. 내가 뭘 잘못하고 있는지 찾을 수 없다

Aug 20 2020
  • PostGIS 버전 : 3.1
  • PostgreSQL 버전 : 12.3
  • 내가 작업하는 컴퓨터에는 126G RAM, 48 CPU 코어가 있습니다.

정보 :

PostGIS를 시작하고 있습니다.

내 목표는 두 지점 간의 모든 일치 데이터를 얻는 것입니다.

lv.geopoint 및 sub.geopoint는 모두 GEOGRAPHY 포인트 (SRID : 4326)이며 GIST 인덱스가 있습니다.

내 하위 SELECT는 약 3k 줄을 반환하지만 'valeurs_foncieres'테이블에는 14,000,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 초
  • 정밀도 10,000 : 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,000,000 줄은 작지 않습니다. 또한 가지고있는 지그가 균일하게 분포되어있는 경우 반경 x10 (원의 면적은 r²에 따라 다름)을 곱할 때 관련 포인트 수가 약 x100이므로 시간 증가가 제곱 된 것처럼 보이는 것이 정상입니다. 여기에서는 그 이상인 것 같지만 더 많은 데이터를 조작할수록 모든 캐시 동작 및 디스크 호출로 인해 잠재적으로 더 많은 작업이 필요할 수 있습니다 (작은 데이터 또는 큰 캐시에는 해당되지 않음).

여기서 설명은 괜찮아 보이며 색인을 사용하므로 문제가 아닙니다. 테이블을 VACUUM ANALYZE해야하지만 많이 변경되지 않아야합니다.

그렇지 않은 경우 할 수있는 가장 중요한 일은 postgresql을 조정하는 것입니다. 기본적으로 매개 변수는 매우 보수적입니다. 큰 서버가있는 경우 매개 변수를 수정하여 적절하게 사용해야합니다. 이러한 매개 변수는 Linux의이 파일에서 처리 할 수 ​​있습니다. /etc/postgresql/12/main/postgresql.conf 그런 다음 postgres를 다시 시작해야합니다 (질문이 있으면 인터넷에서 doc를 쉽게 찾을 수 있음). 일반적으로 내가 수정하는 것은 다음과 같습니다 (약 120Go 및 48 CPU 램에 적합).

  • shared_buffers = 30GB
  • 효과적인 _ 캐시 _ 크기 = 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로 한 줄만 유지 한 후 X 미터에 대한 모든 포인트가 필요한 이유를 정말로 이해하지 못합니다. 전체 쿼리를 입력하지 않았거나 실제로 한 점만 필요합니다. 그래서 나는 당신이 정말로 원했던 것이 가장 가까운 지점을 얻는 것이었을 경우를 대비하여 그것을 다시 작성합니다. 각 단계에 대한 임시 테이블을 생성하는 MATERIALIZED CTE를 사용했는데 때로는 성능을 실제로 향상시킬 수 있으므로 가장 가까운 이웃뿐만 아니라 모든 포인트를 얻으려면 ORDER를 제거하여 그대로 실행할 수 있습니다. INNER JOIN LATERAL의 BY 및 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;