기하 급수적으로 느리다. 내가 뭘 잘못하고 있는지 찾을 수 없다
- 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
질문 :
이것은 정상입니까? 실행 시간을 줄이려면 어떻게해야합니까?
답변
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;