PostgreSQL LIKE 쿼리가 왜 이렇게 느린가요? 그리고 어떻게 하면 더 빠르게 만들 수 있나요?

PostgreSQL LIKE 쿼리 최적화: 성능 문제 해결

부분 문자열 일치에 사용되는 PostgreSQL의 LIKE 쿼리는 종종 밀리초에서 초까지 예측할 수 없는 성능을 나타냅니다. 이 기사에서는 근본 원인과 효과적인 최적화 전략을 살펴봅니다.

성과 변동성의 이해

성능 불일치는 LIKE 쿼리의 고유한 리소스 요구로 인해 발생합니다. 인덱스를 효과적으로 활용하는 동일성 검사와 달리 LIKE 쿼리에는 일반적으로 전체 테이블 검색이 필요합니다.

색인 제한

B-트리 인덱스는 성능에 매우 중요하지만 본질적으로 LIKE 작업에는 적합하지 않습니다. 열의 인덱스(예: owner1)는 정확한 일치(SELECT * FROM parcels WHERE owner1 = 'John Doe')를 향상시키지만 LIKE 쿼리

에 대해서는 제한된 이점을 제공합니다.

성능 향상 기법

여러 기술을 사용하면 LIKE 쿼리 성능을 크게 향상할 수 있습니다.

전체 텍스트 검색(FTS)

PostgreSQL의 FTS는 효율적인 텍스트 검색을 제공합니다. 그러나 LIKE 연산자를 직접 지원하지 않으며 임의의 하위 문자열이 아닌 단어에 대해 작동합니다.

접두사 매칭 최적화

선행 와일드카드가 없는 패턴(예: 'foo%')의 경우 B-트리 인덱스와 함께 text_pattern_ops 또는 varchar_pattern_ops과 같은 연산자 클래스를 활용하세요. 이는 왼쪽 고정 패턴 일치를 최적화합니다.

유연한 매칭을 위한 트라이그램 인덱스

GIN 또는 GiST 인덱스와 결합된 pg_trgm 확장은 트라이그램 인덱싱을 제공합니다. 이는 와일드카드 위치에 관계없이 모든 LIKE 및 ILIKE 패턴을 지원합니다.

추가 고려사항

• 문자열 길이: 3자보다 짧은 색인 단어는 작동하지만 3자 미만의 패턴은 여전히 ​​전체 색인 스캔을 트리거할 수 있습니다.
• PostgreSQL 11 이상: PostgreSQL 11에서는 SP-GiST 인덱스와의 효율적인 접두어 매칭을 위해 ^@ 연산자와 starts_with() 함수를 도입했습니다.
• 데이터 정렬 및 인덱싱: 특정 데이터 정렬에 맞게 조정된 인덱스 또는 연산자 클래스를 사용하여 COLLATE "C"를 사용하면 특정 시나리오에 대한 접두사 일치 및 LIKE 쿼리 성능을 최적화할 수 있습니다.

위 내용은 PostgreSQL LIKE 쿼리가 왜 이렇게 느린가요? 그리고 어떻게 하면 더 빠르게 만들 수 있나요?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!