[Postgresql] tsquery, tsvector를 이용한 텍스트 검색

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월 제공데이터를 사용했습니다. 실제 실습한 시기에 따라 쿼리 결과에 차이가 있을 수 있습니다.

	road_address_id	city_name	district_name	road_name
1	36110103335079400000900000	세종특별자치시		호려울로
2	36110103335079400001900000	세종특별자치시		호려울로
3	36110103335079400002900000	세종특별자치시		호려울로
4	36110103335079400004200000	세종특별자치시		호려울로
5	36110103335079400004500000	세종특별자치시		호려울로
6	36110103335079400005100000	세종특별자치시		호려울로
7	36110104335079500000300000	세종특별자치시		대평로
8	36110104335079500002700000	세종특별자치시		대평로
9	36110104335079500003400000	세종특별자치시		대평로
10	36110104335079500005600000	세종특별자치시		대평로
11	36110104335079500007100000	세종특별자치시		대평로
12	36110104335079500007500000	세종특별자치시		대평로
13	36110104335079500008000000	세종특별자치시		대평로
14	36110104335079500008300000	세종특별자치시		대평로
15	36110104335079500008600000	세종특별자치시		대평로
16	36110104335079500008700000	세종특별자치시		대평로
17	36110104485319100001000000	세종특별자치시		대평1길
18	36110104485319100001700000	세종특별자치시		대평1길
19	36110104485319100001900000	세종특별자치시		대평1길
20	36110104485319100002200000	세종특별자치시		대평1길
21	36110104485319100002700000	세종특별자치시		대평1길
22	36110104485319100003700000	세종특별자치시		대평1길
23	36110104485319100003800000	세종특별자치시		대평1길
24	36110104485319300001000000	세종특별자치시		대평3길
25	36110104485319300001700000	세종특별자치시		대평3길
26	36110104485319300001800000	세종특별자치시		대평3길
27	36110104485510800001700000	세종특별자치시		대평4길
28	36110104485510800003300000	세종특별자치시		대평4길

마치며

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

참고자료

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

출처

• Postgresql 문자열 검색 타입(text search types) 사용 방법 | 또치의 손수 만든 삽질 보관함