[PostgreSQL] 성능개선 사례 기록(2)

GIS(지리 정보 시스템) 분야를 모르는 분들에게는 약간 생소한 표현이 나옵니다.
용어가 헷갈리는 분들은 이 글의 맨 아래 참고: GIS 관련 용어란을 읽어주세요.

문제가 되는 Table

-- 우리나라 전국의 연속지적도 정보를 한 곳에 모은 테이블이다.
-- 원래는 Sharding 을 해야겠지만, 지금은 그런 것을 하지 않는다는 가정을 하겠다.
-- 대충 4000만~5000만 개의 데이터가 있으며, 각 Row 에 Geometry 정보도 있어서
-- 상당히 무건 테이블이다.
create table lsmd_cont_ldreg_all
(
	col_adm_sect_cd varchar(5), -- 원천시군구 코드
    pnu             varchar(19), -- 필지 고유 번호, 
    jibun           varchar(100), -- 지번
    sgg_oid         integer, -- ?
    geom            geometry(MultiPolygon, 5186), -- 도형정보
    constraint lsmd_cont_ldreg_pk
        unique (col_adm_sect_cd, pnu)
);

-- !!!! 이 index 를 사용해야 하는게 저희의 목표입니다 !!!!
create unique index lsmd_cont_ldreg_all_idx
    on lsmd_cont_ldreg_all (pnu);

아래 3가지 사항을 잘 기억하고 글을 읽어주시기 바랍니다.

• 필지고유번호(pnu) 에 index 가 잡혀있습니다.
• pnu 는 앞의 2자리가 시도 코드입니다.
  • 
  • 이미지 출처: http://www.gisdeveloper.co.kr/?p=1562

• client 에서 검색 파라미터로 pnu 를 전송하며,
  해당 pnu 는 2자리~19자리까지 랜덤하게 올 수 있는 상황입니다.

like 연산에 의한 Slow Query

---

1. SQL

SELECT *
FROM lsmd_cont_ldreg_all
WHERE
   pnu like :pnu || '%'
ORDER BY PNU
LIMIT :countPerPage OFFSET :countPerPage * (:currentPage - 1)

2. EXPLAIN 결과

Limit  (cost=0.56..470.28 rows=20 width=330)
  ->  Index Scan using on lsmd_cont_ldreg_mv_all  (cost=0.56..112279577.06 rows=4780797 width=330)
        Filter: ((pnu)::text ~~ '48%'::text)

어마어마하게 느립니다.

like 없이 범위 연산자로 조회

---

1. SQL

SELECT *
FROM lsmd_cont_ldreg_all
WHERE
    case
        when length(:pnu) < 19 then
            PNU >= :pnu and PNU < ((:pnu)::bigint + 1)::varchar(19)
        when length(:pnu) = 19 then
            pnu = :pnu
        else false
    end
ORDER BY PNU
LIMIT :countPerPage OFFSET :countPerPage * (:currentPage - 1)

2. EXPLAIN 결과

Limit  (cost=0.56..58.67 rows=20 width=330)
  ->  Index Scan using on lsmd_cont_ldreg_mv_all  (cost=0.56..12778081.18 rows=4398208 width=330)
        Index Cond: (((pnu)::text >= '48'::text) AND ((pnu)::text < '49'::text))

갑절로 빨라졌습니다!

TIP: 경상남도(pnu 앞자리 '48')의 우선순위를 높게하려면?

---

INDEX 생성

create index pnu_kyeong_nam_first_idx
on lsmd_cont_ldreg_mv_all (
      (case
          when PNU  >= '48'
          and PNU < '49' then 0
           else 1
          end) asc
      , pnu
); -- 생성하는데 4~5분 걸렸네요. 세상에.

SQL

SELECT *
FROM lsmd_cont_ldreg_mv_all
WHERE
    case
        when length(:pnu) < 19 then
            PNU >= :pnu
            and PNU < ((:pnu)::bigint + 1)::varchar(19)
        when length(:pnu) = 19 then
            pnu = :pnu
        else false
    end
ORDER BY
   (case
        when PNU  >= '48'
            and PNU < '49' then 0
        else 1
    end)
    , PNU
LIMIT :countPerPage OFFSET :countPerPage * (:curentPage - 1)

EXPLAIN 확인

Limit  (cost=0.56..124.07 rows=10 width=56)
  ->  Index Only Scan using pnu_kyeong_nam_first_idx on lsmd_cont_ldreg_mv_all  (cost=0.56..1774390.73 rows=143669 width=56)
        Index Cond: ((pnu >= '483'::text) AND (pnu < '484'::text))

• 아까 생성한 인덱스를 잘 사용하네요!

---

참고: GIS 관련 용어

• 연속지적도: 진짜 쉽게 얘기해서 전산화된 지도에 우리나라의 땅 조각 하나하나를 옮겨 넣은 것이라고 생각하면 됩니다. 이 정보들은 http://data.nsdi.go.kr/dataset/12771 에서 제공합니다.
  

• PNU : 땅 한 조각의 고유 번호라고 생각하면 됩니다. 땅에 대한 PK 라고 생각해도 좋습니다.
  • 참고로 PNU 는 총 19자리로 고정되어 있으며, 각 자릿수가 의미하는 것이 다릅니다.
  • 
  • 이미지 출처: http://www.gisdeveloper.co.kr/?p=1562

• Geometry : 도형정보를 저장할 때 사용되는 PostGIS Data Type 입니다.
• PostGIS : [ postgresql + 도형 정보 (기능) ] 을 사용할 수 있는 Postgresql Extension