postgresql18.3 로컬 주소DB 구축 (8) - pg_trgm, 인덱스 설정

한글 검색 성능 향상을 위한 확장 설정 (pg_trgm)

주소 검색 시 사용자가 "강남"만 입력해도 "강남대로", "강남아파트"가 모두 검색되게 하려면 pg_trgm 확장이 필수입니다. 이는 docker-compose 설정은 아니지만, DB 기동 후 최초 1회 실행해주어야 합니다.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

 

이 확장을 설치하면 나중에 통합 검색 테이블을 만들 때 아래와 같이 한글 부분 일치 검색 전용 인덱스를 만들 수 있어 검색 속도를 향상시킬 수 있습니다.

---

한글 주소 검색 인덱스 (GIN + pg_trgm)

-- 인덱스 생성 (full_address는 시도+시군구+도로명+건물번호가 합쳐진 컬럼명)
CREATE INDEX idx_juso_search_total_full_address_trgm 
ON juso.juso_search_total
USING GIN (full_address gin_trgm_ops);

---

공간 검색 인덱스 (GiST)

"내 위치 주변 1km 이내 상점/주소 찾기"와 같은 위치 기반 기능을 구현할 때 필수입니다.

• 인덱스 타입: GIST (Generalized Search Tree)
• 용도: ST_DWithin, ST_Distance 등 PostGIS 공간 함수 성능 향상

-- geom_wgs84 컬럼(GEOMETRY 타입)에 생성
CREATE INDEX idx_juso_search_total_geom_wgs84_gist 
ON juso.juso_search_total
USING GIST (geom_wgs84);

---

관리번호 및 위경도 인덱스 (B-Tree)

데이터 수정, 특정 건물 타겟팅, 혹은 단순 정렬을 위해 필요한 표준 인덱스입니다.

• 인덱스 타입: B-Tree (기본값)
• 용도: 고유 번호 조회 및 단순 범위 검색

-- 1. 관리번호 (업데이트 및 상세 조회용)
CREATE UNIQUE INDEX idx_juso_search_total_wgs84_manage_no ON juso.juso_search_total (manage_no);

-- 2. 위경도 숫자 컬럼 (좌표 기반 단순 필터링용)
CREATE INDEX idx_juso_juso_search_total_latitude_longitude ON juso.juso_search_total (latitude, longitude);

---

정리: 통합 테이블 생성 및 인덱스 적용 순서

1. juso_search_total 통합 테이블 생성 (컬럼만 정의)
2. juso_master + juso_location_summary 데이터를 조인해서 INSERT (적재)
3. 위의 3가지 인덱스 생성
4. ANALYZE juso.juso_search_total; 실행 (DB 통계 갱신)