[PostgreSQL] 공간 인덱스 활용

공간 인덱스 활용

 

업무를 하는데 공간 연산을 하는 일이 많다. 특정 주소와 비교하여 가까운 거리에 있는 주소를 추출한다거나 500m 또는 1km 이내의 주소들을 추출하는 경우이다. 이럴 때에는 거리 계산이 필요하다. 

거리계산하는 함수로 st_distance 를 사용하였는데, 이 공감함수는 공간 인덱스를 타지 못한다는 걸 깨달았다. 500m 이내 데이터를 추출하는데 34초 가량 걸려 개선해야될 필요성을 느꼈다. 

 

PostgreSQL 인덱스 생성

CREATE INDEX [인덱스명] ON [테이블명] USING btree (컬럼1, 컬럼2..);

 

인덱스를 생성할 때에는 보통 위와 같이 사용한다. geometry 데이터 타입인 경우에도 위와 같이 생성하면 인덱스를 탈 줄 알았는데, 잘못 알고 있었다. 공간 인덱스를 생성하는 방법은 아래와 같다. 

CREATE INDEX [공간인덱스명] ON [테이블명] USING gist ((컬럼::geography));
-- or
CREATE INDEX [공간인덱스명] ON [테이블명] USING gist ((컬럼::geometry));

 

 

내가 만든 공간컬럼의 데이터타입은 geometry이지만 인덱스를 생성할 때에는 geography 로 생성하였다.

geometry는 유클리드 방식을 통해 도형을 다루는 것이고, geography는 구면좌표계를 이용하여 지구 타원체 상에서 도형을 다루는 것이다. 두 지점과의 길이와 직선의 의미가 다르고, 삼각함수의 계산 방법도 다르다. 이것을 잘 활용해야 한다. 

 

공간인덱스를 알기 이전에 st_distance를 활용하였을 때에는 아래와 같이 썼다.

st_distance(
    ('SRID=4326;POINT(' || st_x(coords) || ' ' || st_y(coords) || ')')::geometry,
    ('SRID=4326;POINT (' || '127.12' || ' ' || '37.12' || ')')::geometry
) * 100000 <= 500

 

나의 쿼리 중 일부만 발췌한 것인데, 127.12, 37.12 위경도 상에서 500미터 이내의 데이터를 추출하는 것이다. 레이어가 담긴 테이블의 용량은 20GB였다. 쿼리 실행 시 34초가 걸렸고, 인덱스를 타지 않았다. 

 

공간 인덱스를 생성하고, st_distance 대신에 ST_DWithin 를 사용하였다.

ST_DWithin(
    coords::geography, 
    ST_GeographyFromText('SRID=4326;POINT (' || '127.12' || ' ' || '37.12' || ')'
), 500)

coords 컬럼을 공간 인덱스로 생성하고, st_DWithin을 사용하니 쿼리를 실행하는데 2초 가량 걸렸다. 공간 인덱스를 잘 사용하여 실행되었다. 

2초도 아직 느리다고 생각되어 지속적인 SQL 튜닝이 필요할 것으로 보인다.