Like 조회의 한계

문자열을 에서 해당 단어가 포함되어있는지 여부를 찾을 때 like 를 사용하는 경우가 있습니다. 간편하지만 % 를 사용하면 해당 필드에 인덱스가 있음에도 이를 사용할 수 없어 성능이 떨어지는 단점이 있습니다.

select * from posts where title like '%검색어%'; -- 인덱스를 사용할 수 없음

데이터가 적은 테이블에선 큰 문제가 되지 않지만, 주소검색과 같이 100만건이 넘는 테이블에서도 이렇게 사용할 수 있을까요? 이번시간엔 Postgresql 에서 문자열을 빠르게 검색할 수 있는 기법 중 하나인 tsquery, tsvector 를 사용하는 방법을 알아봅니다.

tsvector? tsquery?

tsvector 는 텍스트 검색을 위해 Postgresql 에서 제공하는 데이터 타입입니다. tsvector 는 텍스트를 토큰화하여 저장하는 데이터 타입으로, tsquery 는 tsvector 에서 토큰화된 데이터를 검색하는데 사용됩니다.

tsvector 를 생성하는 방법은 아래와 같습니다.

to_tsvector('simple', '검색할 문자열')

tsquery 를 생성하는 방법은 아래와 같습니다.

to_tsquery('simple', '검색할 문자열')

tsvector 형식의 인덱스 생성도 가능합니다. 아래 쿼리는 인덱스 생성 예제입니다.

CREATE INDEX idx_rnaddrkor_city_district_road ON rnaddrkor USING GIN (to_tsvector('simple', rnaddrkor.city_name || ' ' || rnaddrkor.district_name || ' ' || rnaddrkor.road_name));

tsquery 연산자 종류

문자열에 단순 단어 검색 이외에도 다양한 연산자를 사용할 수 있습니다. 아래는 tsquery 에서 사용할 수 있는 연산자 종류입니다.

• AND(&) : & 사이에 속한 조건들이 모두 부합한 대상을 필터링 합니다. 예를 들어 세종특별시 & 호려울로 로 조회하면 세종특별시 와 호려울로가 모두 포함되어 있는 tsvector 값으로 필터링합니다.
• OR(|) : | 사이에 속한 조건들 중 1개라도 부합하면 됩니다. 예를 들어 호려울로 | 대평로 로 조회하면, 호려울로 와 대평로가ㅣ 모두 포함되어 있는 tsvector 값으로 필터링합니다.
• NOT(!) : 단어 앞에 붙이며, 해당 조건에 부합하지 않는 항목을 대상으로 조회합니다. 예를 들어 !대평시장 의 결과 값은 대평시장을 포함하지 않는다.
• FOLLOWED BY(<->) : 앞 뒤 단어의 순서 일치여부를 따집니다. 예를 들어 세종특별시 <-> 호려울로 로 조회하면 세종특별시 다음에 호려울로 순으로 정의된 tsvector 값을 필터링합니다.

AND(&) 와 FOLLOWED BY(<->) 차이점

세종특별시 & 호려울로 와 호려울로 & 세종특별시 는 같은 결과가 조회됩니다. 세종특별시 <-> 호려울로 와 호려울로 <-> 세종특별시 는 조회 결과가 다릅니다.

단순 설명만으론 이해하기 어려울 수 있으니, 예제 데이터를 구축하여 실제로 조회해보겠습니다.

데이터 구축

먼저 테스트용 데이터를 구축해봅니다. postgresql 에 아래와 같은 테이블을 생성합니다. 구축 예제는 https://github.com/ddochea0314/example-postgres-text-search-types를 참조합니다.

데이터 조회

구축이 완료되었다면 조회를 해봅니다.

SELECT rnaddrkor."road_address_id", 
rnaddrkor."city_name", 
rnaddrkor."district_name", 
rnaddrkor."road_name"
FROM rnaddrkor
WHERE to_tsvector('simple', rnaddrkor."city_name" || ' ' 
||rnaddrkor."district_name" || ' ' 
|| rnaddrkor."road_name") @@ to_tsquery('세종:* & (호려울로 | 대평:*) & !대평시장:*');

결과는 아래와 같이 나올 것 입니다.

해당 예제는 주소기반산업지원서비스 에서 제공하는 도로명주소 한글 2023년 6월 제공데이터를 사용했습니다. 실제 실습한 시기에 따라 쿼리 결과에 차이가 있을 수 있습니다.

마치며

이번 글에서는 postgresql 에서 제공하는 텍스트 검색 기능을 사용해보았습니다. 텍스트 검색 기능은 검색 엔진을 구축할 때 많이 사용되는 기능이기 때문에 검색 엔진을 구축할 때 postgresql 을 사용한다면 텍스트 검색 기능을 사용해보는 것을 추천합니다.

참고자료

• PostgreSQL: Documentation: 15: 8.11. Text Search Types
• https://github.com/ddochea0314/example-postgres-text-search-types
• 주소기반산업지원서비스