PostgreSQL LIKE 쿼리 성능 변화

PostgreSQL LIKE 쿼리 성능 변화

---

LIKE내 데이터베이스의 특정 테이블에 대한 쿼리 와 관련하여 응답 시간이 상당히 많이 변하는 것을 보았습니다 . 때로는 200-400ms (매우 수용 가능) 내에 결과를 얻을 수 있지만 결과를 반환하는 데 30 초 정도 걸릴 수도 있습니다.

LIKE쿼리가 매우 리소스 집약적 이라는 것을 이해 하지만 응답 시간에 그렇게 큰 차이가있는 이유를 이해할 수 없습니다. owner1필드 에 btree 인덱스를 만들었지 만 LIKE쿼리에 도움이되지 않는다고 생각 합니다. 누구나 아이디어가 있습니까?

샘플 SQL :

SELECT gid, owner1 FORM parcels
WHERE owner1 ILIKE '%someones name%' LIMIT 10

나는 또한 시도했다 :

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%') LIMIT 10

과:

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('someones name%') LIMIT 10

비슷한 결과가 있습니다.
테이블 행 수 : 약 95,000.

---

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로 유사한 문자열을 빠르게 찾기

---

Possibly the fast ones are anchored patterns with case-sensitive like that can use indexes. i.e. there is no wild card at the beginning of the match string so the executor can use an index range scan. (the relevant comment in the docs is here) Lower and ilike will also lose your ability to use the index unless you specifically create an index for that purpose (see functional indexes).

If you want to search for string in the middle of the field, you should look into full text or trigram indexes. First of them is in Postgres core, the other is available in the contrib modules.

---

You could install Wildspeed, a different type of index in PostgreSQL. Wildspeed does work with %word% wildcards, no problem. The downside is the size of the index, this can be large, very large.

---

Please Execute below mentioned query for improve the LIKE query performance in postgresql. create an index like this for bigger tables:

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

---

I recently had a similar issue with a table containing 200000 records and I need to do repeated LIKE queries. In my case, the string being search was fixed. Other fields varied. Because that, I was able to rewrite:

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

as

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;

I was delighted when the queries came back fast and verified the index is being used with EXPLAIN ANALYZE:

 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

---

Your like queries probably cannot use the indexes you created because:

1) your LIKE criteria begins with a wildcard.

2) you've used a function with your LIKE criteria.

---

for what it's worth, Django ORM tends to use UPPER(text) for all LIKE queries to make it case insensitive,

Adding an index on UPPER(column::text) has greatly sped up my system, unlike any other thing.

As far as leading %, yes that will not use an index. See this blog for a great explanation:

https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

---

When ever you use a clause on a column with functions eg LIKE, ILIKE, upper, lower etc. Then postgres wont take your normal index into consideration. It will do a full scan of the table going through each row and therefore it will be slow.

The correct way would be to create a new index according to your query. For example if i want to match a column without case sensitivity and my column is a varchar. Then you can do it like this.

create index ix_tblname_col_upper on tblname (UPPER(col) varchar_pattern_ops);

Similarly if your column is a text then you do something like this

create index ix_tblname_col_upper on tblname (UPPER(col) text_pattern_ops);

Similarly you can change the function upper to any other function that you want.

참고URL : https://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations