PostgreSQL LIKE 쿼리 성능 변동

PostgreSQL LIKE 쿼리 성능 변동

관련하여 응답 시간에 상당히 큰 변화가 있었습니다.LIKE내 데이터베이스의 특정 테이블에 쿼리합니다.때로는 200-400ms(매우 적합) 이내에 결과를 얻을 수도 있지만, 결과를 반환하는 데 30초 정도 걸릴 수도 있습니다.

는 그것을 합니다.LIKE쿼리는 리소스 집약적이지만 응답 시간에 왜 이렇게 큰 차이가 있는지 이해할 수 없습니다.는 나를 Btree 에 했습니다.owner1하지만 도움이 되지는 않을 것 같습니다.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 열에서 부분 일치 항목 가져오기

다음에 대한 삼각형 인덱스LIKE

왼쪽 고정 패턴뿐만 아니라 모든 및 패턴을 지원하기 위해 GIN 및 GiST 트리그램 인덱스에 대한 연산자 클래스를 제공하는 추가 모듈을 설치합니다.

예제 색인:

진(colgin_trgm_ops)을 사용하여 인덱스 tbl_col_gin_trgm_idx On tbl;

또는:

gist(colist_trgm_ops)를 사용하여 인덱스 tbl_col_gist_trgm_idx Ontbl을 생성합니다.

• GiST와 GIN 지수의 차이

쿼리 예제:

선택 * tbl에서 콜은 'foo%'와 같습니다.;선택 * tbl에서 col LIKE '%foo%'; -- 선행 와일드카드와도 작동합니다.선택 * tbl에서 col I like '%foo%'; -- 대소문자를 구분하지 않고 작업합니다.

트램?짧은 줄은 어떻습니까?

인덱스 값이 3자 미만인 단어는 여전히 작동합니다.설명서:

문자열에 포함된 삼각형 집합을 결정할 때 각 단어에는 접두사가 두 개, 접미사가 하나 있는 것으로 간주됩니다.

그리고 3자 미만의 검색 패턴?설명서:

다 둘 다에 LIKE및 정규 변환 검색에서는 추출 가능한 트리거가 없는 패턴이 전체 인덱스 검색으로 전락합니다.

즉, 인덱스/비트맵 인덱스 검색은 여전히 작동합니다(준비된 문에 대한 쿼리 계획은 깨지지 않습니다). 더 나은 성능을 얻을 수 없습니다.일반적으로 1-문자 또는 2-문자 문자열은 거의 선택할 수 없으며(기본 테이블 일치의 몇 퍼센트 이상) 인덱스 지원은 전체 테이블 검색 속도가 빠르기 때문에 성능이 크게 향상되지 않습니다.

접두사 일치

와일드카드가 : " " " " 은 다음과 같습니다.col LIKE 'foo%'.

^@/ 연산자 /starts_with()를 수행

Postgres 11의 릴리스 노트 인용:

SP-GiST(Ildus Kurbangaliev)에서 지원하는 접두사 일치 연산자 텍스트 ^@ 텍스트 추가

이는 var LIKE 'word%'를 btree 인덱스와 함께 사용하는 것과 비슷하지만 더 효율적입니다.

쿼리 예제:

선택 * tbl WHERE col @ 'foo'; -- 추가 와일드카드 없음

그러나 Postgres 15에서 플래너 지원이 개선되고 운영자가 적절하게 문서화될 때까지 운영자와 기능의 잠재력은 제한적으로 유지됩니다.릴리스 정보:

허용^@ 및 starts-with 연산및starts_with()Collation(Tom Lane)을 사용하는 경우 btree 인덱스를 사용하는 함수

이전에는 SP-GiST 인덱스만 사용할 수 있었습니다.

COLLATE "C"

9, Postgres 9.1을 가진 COLLATE "C" 클래스 과 을 제공합니다.text_pattern_ops아래에 설명되어 있습니다. 항목

• text_pattern_ops와 COLLATE "C" 사이에 차이점이 있습니까?

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);

쿼리 예제:

선택 * tbl에서 콜은 'foo%'와 같습니다; -- 선행 와일드카드 없음

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

---

진일보한 내용

• Postgre의 LIKE, SILLIX TO 또는 정규 표현식과 패턴 일치SQL
• LIKE는 어떻게 구현됩니까?
• Postgre에서 유사한 문자열 찾기신속한 SQL 처리

가장 빠른 패턴은 인덱스를 사용할 수 있는 대소문자를 구분하는 고정 패턴일 수 있습니다. 즉, 일치 문자열의 시작 부분에 와일드카드가 없으므로 실행자가 인덱스 범위 검색을 사용할 수 있습니다.(문서의 관련 설명은 여기에 있습니다) Lower and Ilike는 사용자가 해당 목적을 위해 특별히 인덱스를 만들지 않는 한 인덱스를 사용할 수 있는 기능도 잃게 됩니다(기능 인덱스 참조).

필드 중간에 문자열을 검색하려면 전체 텍스트 또는 트리그램 인덱스를 조사해야 합니다.첫 번째는 Postgres 코어에 있고, 다른 하나는 기여 모듈에서 사용할 수 있습니다.

Postgre에 다른 유형의 인덱스인 Wildspeed를 설치할 수 있습니다.SQL. Wildspeed는 %word% 와일드카드로 작동합니다. 문제 없습니다.단점은 인덱스의 크기입니다. 이 크기는 매우 클 수 있습니다.

최근에 200,000개의 레코드가 포함된 테이블에서 비슷한 문제가 발생하여 반복적인 LIKE 쿼리를 수행해야 합니다.저의 경우, 검색 중인 문자열이 수정되었습니다.다른 분야는 다양했습니다.왜냐하면, 저는 다음을 다시 쓸 수 있었기 때문입니다.

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;

쿼리가 빠르게 돌아왔고 인덱스가 사용되고 있음을 확인했을 때 기뻤습니다.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

열에 LIKE, ILIKE, upper, lower 등의 함수가 있는 절을 사용할 때마다그러면 포스트그레스는 당신의 정상 지수를 고려하지 않을 것입니다.테이블이 각 행을 통과하는 전체 검색을 수행하므로 속도가 느려집니다.

올바른 방법은 쿼리에 따라 새 인덱스를 만드는 것입니다.예를 들어 대소문자를 구분하지 않고 열을 일치시키려면 열이 막대형이어야 합니다.그러면 이렇게 하면 됩니다.

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

마찬가지로 열이 텍스트인 경우 다음과 같은 작업을 수행합니다.

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

마찬가지로 함수 상한을 원하는 다른 함수로 변경할 수 있습니다.

postgresql에서 LIKE 쿼리 성능을 향상시키려면 아래에 언급된 쿼리를 실행하십시오. 더 큰 테이블에 대해 다음과 같은 인덱스를 만드십시오.

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

Django ORM은 가치가 있는 것을 사용하는 경향이 있습니다.UPPER(text)절대로LIKE대소문자를 구분하지 않도록 쿼리합니다.

인덱스 추가UPPER(column::text)다른 것과는 달리 시스템 속도가 크게 빨라졌습니다.

선도적인 %에 관해서는, 예, 그것은 색인을 사용하지 않습니다.자세한 설명은 이 블로그를 참조하십시오.

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

유사 쿼리는 다음과 같은 이유로 사용자가 만든 인덱스를 사용할 수 없습니다.

LIKE 기준은 와일드카드로 시작합니다.

LIKE 기준으로 함수를 사용했습니다.

언급URL : https://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations