[PostgreSQL] 최적화 힌트

요즈음의 DBMS들은 아주 똑똑하다. PostgreSQL은 그 중에서도 가장 똑똑한 편이다.

그래서 테이블 설정들 잘 주고, 쿼리만 정석대로 잘 짜면 아주 빠르고 효율적으로 처리를 한다.

하지만 종종 극한의 튜닝을 할 경우엔 최적화 동작에 직접 손을 대야 할 필요가 생기곤 하는데, 이럴 때 사용되는 것이 힌트다. 쿼리 주석에 특정한 명령들을 입력해 어떻게 조인을 할지, 어떻게 스캔을 할지 등을 선택할 수 있게 해주는 것이다.
특수한 상황에서 잘 사용하면 성능을 극한으로 끌어올릴 수 있지만, 모든걸 망쳐버릴 수도 있는 그런 양날의 검이다.

postgres는 최적화 명령어셋을 지원하진 않는다. 앞으로도 힌트를 포함하지 않을 계획이라고 하니.. 좀 부정적으로 보는 것 같다.

그래서 힌트를 쓰려면 직접 확장을 설치를 해줘야 한다.

나는 일단 WSL Ubuntu 18으로 테스트를 했다.

설치 (ubuntu)

깃 레포지토리를 clone 하고,

git clone https://github.com/ossc-db/pg_hint_plan

버전에 맞춰서 브랜치를 바꾼다. 내가 쓰는 postgres는 10 버전이기 때문에 PG10로 바꿨다.

그리고 make를 시도하면

종속성 문제가 생길 수 있다.

그럴 때는 libpq-dev, python-dev,

postgresql-server-dev-all postgresql-common 등을 설치해준다,

다시 make하고

make install

그리고 나서 postgres에 접속해 설치한 플러그인을 로드한다.

이건 세션 단위로만 동작하는 명령이다.

영구적으로 플러그인을 적용되게 하려면 postgresql.conf에서

shared_preload_libraries = "pg_hint_plan";

를 넣어주면 된다.

테스트

설치를 했으니 이제 테스트를 좀 해보자.
테스트용 테이블과 데이터를 삽입한다.

그리고 이런식으로 select를 하면 원래 인덱스 스캔이 뜬다.
id에 PK로 인덱스가 걸려있기 때문이다.

웬만하면 인덱스가 되는 상황에서는 인덱스가 빠르나, 풀스캔이 필요할 수도 있다.
물론 그런 상황은 상당히 드물지만..

옵티마이저의 말을 따르기 싫다면 이런식으로 주석을 써주면 된다.
힌트명령어는 항상 /+.../ 의 형태로 들어간다.
그리고 SeqScan은 시퀀스 스캔. 풀스캔을 강제하는 명령이다.

매우 잘못된 예시지만, 일단은 됐다.
대충 이런식으로 사용하면 된다.

상세한 힌트 목록은 여기서 볼 수 있다.
스캔 방법, 조인 방법, 조인 순서 정도에 대한 변경만을 지원한다. INSERT 등에 대한 힌트는 없더라.
https://github.com/ossc-db/pg_hint_plan/blob/PG10/doc/hint_list.html

참조
https://www.slideshare.net/SiyeonAcademy/postgresql-20178