[Postgresql] Full text 관련 내용 정리

다음은 Mastering PostgreSQL 13 의 내용 중 index 부분의 일부를정리한 글입니다. 개인 복습용

Achieving better answers with fuzzy searching

• pg_trgm 사용. tri 알고리즘을 사용하여 단어간에 유사도 계산
  - 인덱스는 GiST 사용
  - 데이터셋이 클 경우 GIN 사용

Speeding up LIKE queries

• trigram index 가 동일하게 사용될 수 있다

Handling regular expressions

• trigram 은 정규식에도 사용 가능

Understanding full-text search

• 동등 일치검색보다는 contains opreation 에 가깝다.
• GIN index 를 통해 이뤄짐
• 텍스트를 분리 / 추출
  ex)

 SELECT to_tsvector('english', 'A car, I want a car. I would not even mind having many cars'); 
                          to_tsvector                          
---------------------------------------------------------------
 'car':2,6,14 'even':10 'mani':13 'mind':11 'want':4 'would':8

more supported languages

select cfgname from pg_ts_config;

Comparing strings

• 특정 키워드를 비교할 경우 transformed 된 값을 비교한다
• 따라서 실제 input 이 달라도 stemmed 된 단어가 같은 것이라면 true 반환
  • to_tsvector('english', 'wanted') @@ to_tsvector('english', 'want') -> true

Defining GIN indexes

• 여러 칼럼의 텍스트 검색을 적용하려면 2가지 방법이 있음
  • functional index GIN생성
  • ready-to-use 컬럼 생성
    • 애초에 계산된 값을 저장하면 runtime 오버헤드를 줄일 수 있음
    • sync 는 trigger 를 이용
      

Debugging your search

• \x 로 확장 후
• ts_debug
  • ex) SELECT * FROM ts_debug('english', 'go to www.cybertec-postgresql.com');
    

Gathering word statistics

• ts_stat 함수

Taking advantage of exclusion operators

• b+tree 인덱스가 아닌 다른 인덱스는 여러가지 operator 를 사용할 수 있음
• GiST 의 경우 contains, overlaps 등 적용 가능
• ex)

CREATE EXTENSION btree_gist;
CREATE TABLE t_reservation (
		room int,
		from_to tsrange,
		EXCLUDE USING GiST (room with =,
			                from_to with &&) 
);

• room id eq, from_to exclude 이기 때문에 id 안에 중복되는 from_to 가 들어올 경우 exception

INSERT INTO t_reservation VALUES (13, '["2017-01-01", "2017-03-03"]');
INSERT 0 1
INSERT INTO t_reservation VALUES (13, '["2017-02-02", "2017-08-14"]');
ERROR:  conflicting key value violates exclusion constraint "t_reservation_room_from_to_excl"
DETAIL:  Key (room, from_to)=(13, ["2017-02-02 00:00:00","2017-08-14 00:00:00"]) conflicts with existing key (room, from_to)=(13, ["2017-01-01 00:00:00","2017-03-03 00:00:00"]).