postGIS 쿼리 튜닝

자체 서버에서 돌고 있던 postgreSQL을 aws의 RDS로 이관하는 작업을 하였다. RDS로 이관 후 CPU가 99%에서 내려오질 않는 현상을 발견했다. 해당 테이블은 데이터도 적을 뿐더러, 기존 자체 서버의 성능이 워낙 좋았던 탓에 누구도 쿼리를 손보지 않고 돌리고 있었던 것으로 판단하였다. (기존 서버에서는 cpu성능이 좋아서 30%을 넘지 않았다)

쿼리 변경전 RDS CPU 모니터링 상황

해당 쿼리는 GIS 관련 쿼리로 하나의 포인트 지점에서 가장 가까운 5곳에 해당하는 데이터를 가져오는 쿼리였다.

테이블 : wstn

테이블내 데이터 갯수 : 1567

공간칼럼이름 : loc

기존 쿼리는 ST_Distance_Sphere를 이용해서 작성되었었다.

1. ST_Distance_Sphere

두 개의 경도 / 위도 점 사이의 선형 거리를 미터 단위로 반환합니다. 구형 지구와 6370986 미터의 반경을 사용합니다. ST_Distance_Spheroid 보다 빠르지만 정확도는 떨어집니다. 포인트에 대해서만 구현됩니다.

explain (analyze, buffers)
select id, ST_Distance_Sphere(loc, ST_GeomFromText('POINT(127.791196702555 37.8630914693919)', 4326)) as distance 
from wstn 
order by distance 
LIMIT 5

→ 실행 결과

해당 테이블을 모두 scan 후 order by에 의해 st_distance_sphere()를 통해 모든 컬럼을 sort했다. 그리고 limit 5를 통해 총 5개의 데이터를 리턴했다.

QUERY PLAN
Limit  (cost=1237.95..1237.96 rows=5 width=12) (actual time=120.083..120.087 rows=5 loops=1)
  Buffers: shared hit=4722
  ->  Sort  (cost=1237.95..1241.86 rows=1567 width=12) (actual time=120.081..120.083 rows=5 loops=1)
"        Sort Key: (st_distance_sphere(loc, '0101000020E6100000508B7EF7A2F25F40553F01C879EE4240'::geometry))"
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=4722
        ->  Seq Scan on wstn  (cost=0.00..1211.92 rows=1567 width=12) (actual time=0.396..118.129 rows=1567 loops=1)
              Buffers: shared hit=4722
Planning time: 0.121 ms
Execution time: 120.109 ms

데이터 결과 값 비교

지도상의 직선거리 (오차있음)	쿼리 결과 (거리)	오차
6560	6559.11371168	0.88
10210	10260.00617244	-50
15020	15000.61022516	19.38
16110	16140.97029263	-30.97
18940	18909.59381729	30.4

postGIS에서 두 지점의 거리를 계산하는 함수는 여러개가 있다. 이를 통해 쿼리 최적화를 하려 한다.

2. ST_Distance_Spheroid

특정 SPHEROID가 주어진 경우 두 개의 경도 / 위도 점 사이의 선형 거리를 반환합니다.

explain (analyze, buffers)
select
id, ST_Distance_Spheroid(loc, ST_GeomFromText('POINT(127.791196702555 37.8630914693919)', 4326), 'SPHEROID["WGS_1984",6378137.0,298.257223563]') as distance
from wstn
order by
distance
LIMIT 5

SPHEROID 란?

타원체에있는 형상의 길이 또는 둘레를 계산합니다. 이것은 지오메트리의 좌표가 경도 / 위도이고 재투영없이 길이가 
필요한 경우 사용된다. 앞서 point 좌표시 저장하는 4326의 경우 "WGS_1984",6378137.0,298.257223563 로 
표기 된다. 

해당 shpheroid의 목록은 여기에서 확인이 가능하다. 
https://developers.arcgis.com/javascript/3/jshelp/gcs.html

→ 실행 결과

속도가 10배가 빨라졌다!!!

설명에서는 ST_Distance_Sphere()가 ST_Distance_Spheroid()보다 빠르다고 했지만, 실행결과는 10배 차이가 났다.

QUERY PLAN
Limit  (cost=454.45..454.46 rows=5 width=12) (actual time=10.821..10.825 rows=5 loops=1)
  Buffers: shared hit=27
  ->  Sort  (cost=454.45..458.36 rows=1567 width=12) (actual time=10.819..10.821 rows=5 loops=1)
"        Sort Key: (st_distance_spheroid(loc, '0101000020E6100000508B7EF7A2F25F40553F01C879EE4240'::geometry, 'SPHEROID(""WGS_1984"",6378137,298.257223562997)'::spheroid))"
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=27
        ->  Seq Scan on wstn  (cost=0.00..428.42 rows=1567 width=12) (actual time=0.246..10.092 rows=1567 loops=1)
              Buffers: shared hit=27
Planning time: 0.141 ms
Execution time: 10.846 ms

ST_Distance_Spheroid 데이터 결과 값 비교

지도상의 직선거리 (오차있음)	쿼리 결과 (거리)	오차
6560	6562.438718428975	-2.4387
10210	10242.2299191738	-32.22
15020	15018.779714434888	1.22
16110	16129.820177967542	-19.82
18940	18938.804332324562	1.19

3. ST_Distance

두 기하학 사이의 2 차원 데카르트 최소 거리를 투영 된 단위로 반환합니다.

explain (analyze, buffers)
select
id, ST_Distance(loc, ST_GeomFromText('POINT(127.791196702555 37.8630914693919)', 4326)) as distance
from wstn
order by
distance
LIMIT 5

→ 실행 결과

ST_Distance_Spheroid 보다 10배

ST_Distance_Sphere 보다 100배가 빨라졌다!!!

QUERY PLAN
Limit  (cost=160.63..160.65 rows=5 width=12) (actual time=1.540..1.544 rows=5 loops=1)
  Buffers: shared hit=21
  ->  Sort  (cost=160.63..164.55 rows=1567 width=12) (actual time=1.539..1.540 rows=5 loops=1)
"        Sort Key: (st_distance(loc, '0101000020E6100000508B7EF7A2F25F40553F01C879EE4240'::geometry))"
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=21
        ->  Seq Scan on wstn  (cost=0.00..134.61 rows=1567 width=12) (actual time=0.017..1.093 rows=1567 loops=1)
              Buffers: shared hit=21
Planning time: 0.116 ms
Execution time: 1.575 ms

결과값

→ 거리에 대한 정확도가 떨어졌다.

ST_Distance 데이터 결과 값 비교

지도상의 직선거리 (오차있음)	쿼리 결과 (거리)	오차
6560	6812.34760217798	-252.34
10210	9272.92416747653	937.07
15020	15637.1858343044	-617.185
16110	16157.5113677447	-47.511
18940	18954.0548848764	14.054

4. ST_DWithin

도형이 주어진 거리 내에 있으면 true를 반환합니다.

단위는 미터 및 거리 측정 기본값에 use_spheroid=True입니다.. 더 빠른 평가를 위해 use_spheroid= false를 사용 하여 구에서 측정합니다. "라고 했었지만, false 옵션을 추가해도 속도는 빨라지지 않았다"

explain (analyze, buffers)
select
id, ST_Distance(loc, ST_GeomFromText('POINT(127.791196702555 37.8630914693919)', 4326)) as distance
from wstn
where
ST_DWithin(ST_GeographyFromText('POINT(127.791196702555 37.8630914693919)'),  loc, 50000, false)
order by distance
limit 5

→ 실행 결과

살짝 느려짐!

시작시 테이블에서 거리를 통해 1491개의 데이터를 걸러낼수 있었으나, 해당 과정에서 모든 데이터를 비교해야 하므로 cost 발생!! 만일 데이터가 많으면 많을 수록 더 빨라질 거라 생각된다.

QUERY PLAN
Limit  (cost=645.54..645.56 rows=5 width=12) (actual time=1.631..1.636 rows=5 loops=1)
  Buffers: shared hit=24
  ->  Sort  (cost=645.54..645.60 rows=21 width=12) (actual time=1.630..1.631 rows=5 loops=1)
"        Sort Key: (st_distance(loc, '0101000020E6100000508B7EF7A2F25F40553F01C879EE4240'::geometry))"
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=24
        ->  Seq Scan on wstn  (cost=0.00..645.20 rows=21 width=12) (actual time=0.196..1.593 rows=76 loops=1)
"              Filter: (((loc)::geography && '0101000020E6100000508B7EF7A2F25F40553F01C879EE4240'::geography) AND ('0101000020E6100000508B7EF7A2F25F40553F01C879EE4240'::geography && _st_expand((loc)::geography, '50000'::double precision)) AND _st_dwithin('0101000020E6100000508B7EF7A2F25F40553F01C879EE4240'::geography, (loc)::geography, '50000'::double precision, false))"
              Rows Removed by Filter: 1491
              Buffers: shared hit=24
Planning time: 0.309 ms
Execution time: 1.675 ms

쿼리 변경 후 RDS 모니터링 상황

기존 70% → 50% 으로 내려간것을 확인할수 있다.

상황에 맞는 postGIS 함수를 사용하면 속도를 높힐 수 있다!!

번외

위의 쿼리에 들어간 위경도를 지오매트리로 변경하는 함수에는 ST_GeographyFromText()를 썼지만, 여러 함수가 있다.

ST_GeomFromText() : PostGIS ST_Geometry 객체를 생성

ST_GeographyFromText() : 텍스트 표현에서 지리 객체를 반환합니다. 지정되지 않은 경우 SRID 4326이 사용됩니다.

ST_MakePoint() : 2D, 3D Z 또는 4D ZM 점 지오메트리를 만듭니다. SRID를 사용시 ST_SetSRID를 사용해야 한다.

select ST_GeomFromText('POINT(127.791196702555 37.8630914693919)', 4326);
select ST_GeographyFromText('POINT(127.791196702555 37.8630914693919)');
select ST_SetSRID (ST_MakePoint (127.791196702555, 37.8630914693919), 4326);

# 0101000020E6100000508B7EF7A2F25F40553F01C879EE4240
# 0101000020E6100000508B7EF7A2F25F40553F01C879EE4240
# 0101000020E6100000508B7EF7A2F25F40553F01C879EE4240

SRID는 Spatial Reference Identifier를 나타내며  투영, 좌표계, 데이텀 및 타원체를 정의합니다. 
Spatial_ref_sys (또는 유사한 이름의 테이블 또는 뷰)라는 조회 테이블의 기본 키인 OGC 호환 데이터베이스의 
정수로 일반 GIS 데이터베이스에서 사용됩니다. 공간 참조 시스템의 공간 참조 세부 정보를 조회하는 데 사용된다. 

더 많은 SRID는 여기에서 확인 가능 하다.
https://developers.arcgis.com/javascript/3/jshelp/gcs.html

세 함수 모두 결과 값은 같으며, 속도 또한 거의 동일하다.

보통의 경우 SRID을 4326으로 사용하므로, ST_GeographyFromText를 사용한다.

참고

https://www.coletiv.com/blog/improve-geographical-search-database/

저작자표시

'Database > DB' 카테고리의 다른 글

객체 지향 데이터베이스 (OODB / object-oriented Database) (2)	2021.06.18
LeetCode sql 문제, 유저별 월별 집계 (0)	2021.05.20
postgreSQL core (2)	2020.06.15
DBMS core architecture 2 (0)	2020.06.14
DBMS core architecture 1 (0)	2020.06.12

조아하는모든것

postGIS 쿼리 튜닝

쿼리 변경전 RDS CPU 모니터링 상황

1. ST_Distance_Sphere

2. ST_Distance_Spheroid

3. ST_Distance

4. ST_DWithin

쿼리 변경 후 RDS 모니터링 상황

상황에 맞는 postGIS 함수를 사용하면 속도를 높힐 수 있다!!

번외

'Database > DB' 카테고리의 다른 글

티스토리툴바

postGIS 쿼리 튜닝

쿼리 변경전 RDS CPU 모니터링 상황

1. ST_Distance_Sphere

2. ST_Distance_Spheroid

3. ST_Distance

4. ST_DWithin

쿼리 변경 후 RDS 모니터링 상황

상황에 맞는 postGIS 함수를 사용하면 속도를 높힐 수 있다!!

번외

'Database > DB' 카테고리의 다른 글

'Database/DB' Related Articles

티스토리툴바