[postgresql] PostgreSQL LIKE 쿼리 성능 변화

FTS는 지원하지 않습니다 LIKE

이전에 허용 대답은 잘못되었습니다. 전체 텍스트 인덱스가있는 전체 텍스트 검색 은 연산자를 위한 것이 아닙니다.LIKE 자체 연산자가 있으며 임의의 문자열에 대해서는 작동하지 않습니다. 사전 및 형태소 분석을 기반으로 하는 단어로 작동합니다 . 그것은 않습니다 지원 단어에 대한 접두사 일치를 에 있지만 LIKE운영자 :

• GIN 인덱싱 된 TSVECTOR 열에서 부분 일치 가져 오기

Trigram 인덱스 LIKE

GIN 및 GiST 트라이 그램 인덱스 에 pg_trgm대한 연산자 클래스를 제공 하는 추가 모듈 을 설치하여 왼쪽 앵커뿐만 아니라 모든 및 패턴 을 지원 합니다.LIKEILIKE

색인 예 :

CREATE INDEX tbl_col_gin_trgm_idx  ON tbl USING gin  (col gin_trgm_ops);

또는:

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

• GiST와 GIN 지수의 차이점

쿼리 예 :

SELECT * FROM tbl WHERE col LIKE '%foo%';   -- leading wildcard
SELECT * FROM tbl WHERE col ILIKE '%foo%';  -- works case insensitively as well

트라이 그램? 짧은 문자열은 어떻습니까?

인덱싱 된 값 이 3 자 미만인 단어는 여전히 작동합니다. 매뉴얼 :

각 단어는 문자열에 포함 된 트라이 그램 집합을 결정할 때 접두사 2 개와 접미사 1 개 공백이있는 것으로 간주됩니다.

그리고 3 자 미만의 검색 패턴? 매뉴얼 :

LIKE정규식 검색과 정규식 검색 모두 에 대해 추출 가능한 트라이 그램이없는 패턴은 전체 인덱스 스캔으로 저하된다는 점에 유의하십시오.

즉, 인덱스 / 비트 맵 인덱스 스캔은 여전히 ​​작동하지만 (준비된 명령문에 대한 쿼리 계획은 중단되지 않음) 더 나은 성능을 얻을 수 없습니다. 일반적으로 1 자 또는 2 자 문자열은 선택성이 거의없고 (기본 테이블 일치의 몇 퍼센트 이상) 인덱스 지원으로 인해 전체 테이블 스캔이 더 빠르기 때문에 성능이 향상되지 않기 때문에 큰 손실이 없습니다.

text_pattern_ops 접두사 일치

단지를 들어 왼쪽 고정 된 패턴 당신은 적절한에 최적의 수 (더 와일드 카드를 선도 없음) 연산자 클래스 하십시오 BTREE 지수를 text_pattern_ops나 varchar_pattern_ops. 표준 Postgres의 두 내장 기능 모두 추가 모듈이 필요하지 않습니다. 성능은 비슷하지만 인덱스가 훨씬 작습니다.

색인 예 :

CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops);

쿼리 예 :

SELECT * FROM tbl WHERE col LIKE 'foo%';  -- no leading wildcard

또는 ‘C’ 로케일로 데이터베이스를 실행해야하는 경우 (실제로 는 로케일 )로 어쨌든 모든 것이 바이트 순서에 따라 정렬되고 기본 연산자 클래스가있는 일반 btree 인덱스가 작업을 수행합니다.

dba.SE의 관련 답변에 대한 자세한 내용, 설명, 예제 및 링크 :

• LIKE, SIMILAR TO 또는 PostgreSQL의 정규식을 사용한 패턴 일치
• LIKE는 어떻게 구현됩니까?
• PostgreSQL로 유사한 문자열을 빠르게 찾기

답변

CREATE INDEX <indexname> ON <tablename> USING btree (<fieldname> text_pattern_ops)

SELECT owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%');

CREATE INDEX ix_parcels ON parcels(position(lower('someones name') in lower(owner1)));

SELECT owner1 FROM parcels
WHERE position(lower('someones name') in lower(owner1)) > 0;

 Bitmap Heap Scan on parcels  (cost=7.66..25.59 rows=453 width=32) (actual time=0.006..0.006 rows=0 loops=1)
   Recheck Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
   ->  Bitmap Index Scan on ix_parcels  (cost=0.00..7.55 rows=453 width=0) (actual time=0.004..0.004 rows=0 loops=1)
         Index Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
 Planning time: 0.075 ms
 Execution time: 0.025 ms