설치·접속: PostgreSQL 설치와 접속
부제: "내 위치 기준 반경 3km 매장"을 PostGIS 도입 없이 인덱스 스캔으로 조회할 때
-- cube + earthdistance 조합. lat/lon 저장 테이블
CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;
CREATE TABLE store (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
lat double precision NOT NULL,
lon double precision NOT NULL
);
-- 위경도를 3D 큐브 좌표로 바꾸는 ll_to_earth() 표현식에 GiST 인덱스
CREATE INDEX store_earth_gix ON store USING gist (ll_to_earth(lat, lon));
-- 서울시청(37.5666, 126.9784) 반경 3km:
-- earth_box()로 1차(인덱스) 필터 → earth_distance()로 정밀 반경 컷
SELECT name,
round(earth_distance(ll_to_earth(37.5666,126.9784),
ll_to_earth(lat,lon))::numeric) AS meters
FROM store
WHERE earth_box(ll_to_earth(37.5666,126.9784), 3000) @> ll_to_earth(lat,lon)
AND earth_distance(ll_to_earth(37.5666,126.9784), ll_to_earth(lat,lon)) < 3000
ORDER BY meters; name | meters
-----------------+--------
City Hall Cafe | 0
Myeongdong Shop | 671
Gwanghwamun | 1044
Seoul Station | 1489
Namsan Tower | 1920
(...외 임의 매장, 반경 3km 안 12건)50,006건(임의 좌표 5만 + 서울 매장 6개)이 든 테이블에서 반경 3km 매장을 뽑았다. 핵심은 두 단계다. earth_box(중심, 반경)은 반경을 감싸는 3차원 bounding box를 만들고, 이게 ll_to_earth(lat,lon) 표현식 GiST 인덱스를 타서 후보를 수십 건으로 좁힌다(1차, 인덱스). 그다음 earth_distance()가 남은 후보만 대권거리로 재서 반경 밖을 걷어낸다(2차, 정밀). box는 사각이라 반경을 살짝 넘는 후보가 딸려 오는데, 2차 컷이 그걸 정리한다.
인덱스를 만든 이유는 EXPLAIN에서 드러난다. ll_to_earth()는 immutable 함수라 그 결과에 표현식 인덱스를 걸 수 있고, earth_box() @> ll_to_earth(...)가 곧 cube의 포함 연산이라 GiST가 그대로 받는다. 함정은 인덱스가 반드시 earth_box(...) @> ll_to_earth(...) 형태여야 탄다는 것. earth_distance(...) < 3000만 WHERE에 쓰면 매 행 거리를 계산해 seq scan이 된다. box 1차 필터를 꼭 같이 걸어야 한다.
이렇게도 쓴다
인덱스가 실제로 걸리는지 EXPLAIN으로 확인한다. Bitmap Index Scan이 나와야 정상. (조합: EXPLAIN)
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT name FROM store
WHERE earth_box(ll_to_earth(37.5666,126.9784), 3000) @> ll_to_earth(lat,lon)
AND earth_distance(ll_to_earth(37.5666,126.9784), ll_to_earth(lat,lon)) < 3000; Bitmap Heap Scan on store (actual rows=12 loops=1)
Recheck Cond: ('...'::cube @> (ll_to_earth(lat, lon))::cube)
Filter: (sec_to_gc(cube_distance(...)) < '3000'::double precision)
Rows Removed by Filter: 8
-> Bitmap Index Scan on store_earth_gix (actual rows=20 loops=1)
Execution Time: 0.270 ms
인덱스가 없을 때(또는 box 필터를 빼먹었을 때) 어떤 대가를 치르는지 비교한다. 5만 행 전수 거리 계산.
SET enable_bitmapscan=off; SET enable_indexscan=off; -- 강제 seq scan
-- 같은 쿼리 → Seq Scan, Rows Removed by Filter: 49994, Execution Time: 107.659 ms
-- 인덱스 0.27ms vs seq 107ms. 약 400배.
정렬까지 인덱스로 하려면 KNN 최근접 이웃으로 "가까운 N개"를 뽑는다. cube의 <->(유클리드 거리) 연산자. (조합: cube KNN)
SELECT name FROM store
ORDER BY ll_to_earth(lat,lon) <-> ll_to_earth(37.5666,126.9784)
LIMIT 5; -- 반경이 아니라 "제일 가까운 5개"가 필요할 때
거리 단위를 바꾼다. earth_box/earth_distance의 반경은 미터. km면 그대로 ×1000, 마일이면 ×1609.
-- 반경 5km
WHERE earth_box(ll_to_earth(:lat,:lon), 5000) @> ll_to_earth(lat,lon)
AND earth_distance(ll_to_earth(:lat,:lon), ll_to_earth(lat,lon)) < 5000;언제 PostGIS로 갈아타나 (경계선). 이 조합은 "점 하나 기준 반경/최근접"까지가 깔끔한 한계다. 폴리곤(행정구역 안 포함 여부), 선(도로 경로), 좌표계 변환(SRID), 면적·교차·버퍼 연산이 필요해지면 earthdistance로는 표현이 안 된다. 그 순간 geometry/geography 타입과 ST_DWithin·ST_Contains를 갖춘 PostGIS로 옮기는 게 맞다. 반대로 "반경 안 매장/스터디 목록"만 필요하면 확장 두 개로 충분하고, 인덱스 스캔도 된다.