로 시작하는 사람의 이름을 찾는 간단한 쿼리를 작성해야했습니다. B 또는 D :
SELECT s.name FROM spelers s WHERE s.name LIKE "B%" OR s.name LIKE "D%" ORDER BY 1 더 성능을 높이기 위해 이것을 다시 작성하는 방법이 있는지 궁금합니다. 그래서 or 및 / 또는 like?
댓글
답변
검색어가 거의 최적입니다. 구문은 “그다지 짧아지지 않고 쿼리”는 훨씬 빨라지지 않습니다.
SELECT name FROM spelers WHERE name LIKE "B%" OR name LIKE "D%" ORDER BY 1; y 인 경우 정말 구문을 줄이려면 분기 가있는 정규식을 사용하세요.
... WHERE name ~ "^(B|D).*" 또는 약간 문자 클래스 로 더 빠름 :
... WHERE name ~ "^[BD].*" 인덱스가없는 빠른 테스트는 두 경우 모두 저에게 있습니다.
적절한 B-Tree 인덱스를 사용하면 LIKE이 (가)이 경주에서 몇 배로 이깁니다.
매뉴얼에서 패턴 매칭에 대한 기본 사항 을 읽어보십시오.
우수한 성능을위한 색인
문제가되는 경우 성능을 고려하여 더 큰 테이블에 대해 다음과 같은 인덱스를 만듭니다.
CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 이러한 종류의 쿼리를 몇 배 더 빠르게 만듭니다. 로케일 별 정렬 순서에는 특별한 고려 사항이 적용됩니다. 매뉴얼에서 연산자 클래스 에 대해 자세히 알아보세요. 표준 ” C ” 로케일 (대부분의 사람들은 사용하지 않음)을 사용하는 경우 일반 색인 (기본 연산자 클래스 포함)은
이러한 색인은 왼쪽 고정 패턴 (문자열의 시작부터 일치)에만 유용합니다.
SIMILAR TO 또는 기본 왼쪽 고정 표현식이있는 정규 표현식도이 색인을 사용할 수 있습니다.하지만 분기 (B|D) 또는 문자 클래스 [BD] (적어도 PostgreSQL 9.0에 대한 내 테스트에서).
트리 그램 일치 또는 텍스트 검색은 특수 GIN 또는 GiST 색인을 사용합니다.
패턴 일치 연산자 개요
) p>
~ (정규 표현식 일치)는 강력하지만 더 복잡하며 기본 표현식 이상의 경우 속도가 느릴 수 있습니다.
SIMILAR TO 는 무의미 함 입니다. LIKE 및 정규 표현식의 독특한 반종입니다. 나는 그것을 사용하지 않습니다. 아래를 참조하세요.
% 는 추가 모듈 pg_trgm 유사성 ” 연산자입니다. . 아래를 참조하세요.
@@ 는 텍스트 검색 연산자입니다. 아래를 참조하세요.
pg_trgm-트라이 그램 일치
PostgreSQL 9.1로 시작 확장 프로그램 pg_trgm 를 사용하여 모든 LIKE / ILIKE 패턴 (및 ~이있는 간단한 정규식 패턴) GIN 또는 GiST 색인.
세부 정보, 예 및 링크 :
pg_trgm는 다음 연산자 도 제공합니다.
-
%-” 유사성 ” 연산자 -
<%(정류자 :%>)-” word_similarity ” 연산자 -
<<%(정류자 :%>>)-” strict_word_similarity ” Postgres 11 이상의 연산자
텍스트 검색
별도의 인프라 및 색인 유형과 일치하는 특수한 유형의 패턴입니다. 사전과 형태소 분석을 사용하며 특히 자연어의 경우 문서에서 단어를 찾는 데 유용한 도구입니다.
접두사 일치 도 지원됩니다.
Postgres 9.6 이후 구문 검색 :
매뉴얼 소개 및 연산자 및 기능 개요 .
퍼지 문자열 일치를위한 추가 도구
추가 모듈 fuzzystrmatch 는 더 많은 옵션을 제공하지만 성능은 일반적으로 위의 모든 옵션보다 열등합니다.
특히 다양한 levenshtein() 함수의 구현이 도움이 될 수 있습니다.
정규식 (~)이 항상
?
답은 간단합니다. SIMILAR TO 표현식은 내부적으로 정규 표현식으로 다시 작성됩니다. 따라서 모든 SIMILAR TO 표현식에 대해 적어도 하나의 더 빠른 정규 표현식이 있습니다 (표현식을 다시 작성하는 오버 헤드를 절약 함). SIMILAR TO ever 를 사용해도 성능이 향상되지 않습니다.
LIKE (~~)로 수행 할 수있는 간단한 표현식은 LIKE 어쨌든.
SIMILAR TO는 SQL 표준의 초기 초안으로 끝났기 때문에 PostgreSQL에서만 지원됩니다. 그들은 여전히 그것을 제거하지 않았습니다. 그러나 그것을 제거하고 대신 regexp 일치를 포함시킬 계획이 있습니다.-또는 그래서 들었습니다.
EXPLAIN ANALYZE가 그것을 드러냅니다. 아무 테이블이나 직접 사용해보세요!
EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO "B%"; 공개 :
... Seq Scan on spelers (cost= ... Filter: (name ~ "^(?:B.*)$"::text) SIMILAR TO가 정규 표현식 (~)으로 다시 작성되었습니다.
이 특정 경우에 대한 최고의 성능
그러나 EXPLAIN ANALYZE는 더 많은 것을 보여줍니다. 앞서 언급 한 색인을 사용하여 시도해보세요.
EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ "^B.*; Reveals :
... -> Bitmap Heap Scan on spelers (cost= ... Filter: (name ~ "^B.*"::text) -> Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ... Index Cond: ((prod ~>=~ "B"::text) AND (prod ~<~ "C"::text)) 내부적으로, 로케일을 인식하지 않는 색인 (text_pattern_ops 또는 로케일 사용 C) 간단한 왼쪽 고정식이 다음 텍스트 패턴 연산자로 다시 작성됩니다. ~>=~, ~<=~, ~>~, ~<~. ~, ~~ 또는 SIMILAR TO 동일합니다.
varchar 유형의 색인에 대해서도 마찬가지입니다. varchar_pattern_ops 또는 char와 bpchar_pattern_ops.
따라서 적용 원래 질문에 대한 가능한 가장 빠른 방법 :
SELECT name FROM spelers WHERE name ~>=~ "B" AND name ~<~ "C" OR name ~>=~ "D" AND name ~<~ "E" ORDER BY 1; 물론 인접 이니셜 을 검색해야한다면 더 단순화 할 수 있습니다.
WHERE name ~>=~ "B" AND name ~<~ "D" -- strings starting with B or C ~ 또는 ~~의 일반적인 사용에 비해 이득은 적습니다. 성능이 가장 중요한 요구 사항이 아닌 경우 표준 연산자를 사용하여 이미 질문에있는 내용에 도달해야합니다.
댓글
- OP에는 ‘ 이름에 대한 색인이 없지만 원래 쿼리에 2 개의 범위 검색과
similar스캔? - @MartinSmith :
EXPLAIN ANALYZE를 사용한 빠른 테스트는 2 개의 비트 맵 인덱스 스캔을 보여줍니다.여러 비트 맵 색인 스캔을 빠르게 결합 할 수 있습니다. - 감사합니다. 따라서
OR를UNION ALL로 바꾸거나name LIKE 'B%'를name >= 'B' AND name <'C'? - @MartinSmith :
UNION우승 ‘ 그러나 예, 범위를 하나의WHERE절로 결합하면 쿼리 속도가 빨라집니다. 나는 내 대답에 더 많은 것을 추가했습니다. 물론 로케일을 고려해야합니다. 로케일 인식 검색은 항상 느립니다. - @a_horse_with_no_name : 예상하지 않습니다. GIN 인덱스가있는 pg_tgrm의 새로운 기능은 일반 텍스트 검색을위한 치료법입니다. 시작 부분에 고정 된 검색은 이미 그것보다 빠릅니다.
답변
표. 실제 요구 사항에 따라 :
person_name_start_with_B_or_D (Boolean) person_name_start_with_char CHAR(1) person_name_start_with VARCHAR(30) PostgreSQL은 SQL에 따라 기본 테이블에서 계산 된 열을 지원하지 않습니다. 서버 이지만 새 열은 트리거를 통해 유지 될 수 있습니다. 분명히이 새 열은 색인이 생성됩니다.
또는 표현식의 색인 는 동일하고 저렴합니다. 예 :
CREATE INDEX spelers_name_initial_idx ON spelers (left(name, 1)); 조건의 표현식과 일치하는 쿼리는이 색인을 활용할 수 있습니다.
이렇게하면 데이터가 생성되거나 수정 될 때 성능 저하가 발생하므로 활동이 적은 환경 (즉, 읽기보다 쓰기가 훨씬 적음)에만 적합 할 수 있습니다.
답변
SELECT s.name FROM spelers s WHERE s.name SIMILAR TO "(B|D)%" ORDER BY s.name 위의 표현이나 원래 표현이 Postgres에서 잘 어울리는 지 모르겠습니다.
제안 된 색인을 만들면 방법을 듣고 싶습니다. 이것은 다른 옵션과 비교됩니다.
SELECT name FROM spelers WHERE name >= "B" AND name < "C" UNION ALL SELECT name FROM spelers WHERE name >= "D" AND name < "E" ORDER BY name 댓글
- 효과가 있었고 비용이 발생했습니다. 1.19에서 1.25. 감사합니다!
답변
과거에 비슷한 성능 문제를 겪으면서했던 일은 다음과 같습니다. 마지막 문자의 ASCII 문자를 증가시키고 BETWEEN을 수행하십시오. 그런 다음 LIKE 기능의 하위 집합에 대해 최상의 성능을 얻을 수 있습니다. 물론 특정 상황에서만 작동하지만 “예를 들어 이름을 검색하는 초대형 데이터 세트의 경우 성능이 극도로 나빠졌습니다.
Answer
매우 오래된 질문이지만이 문제에 대한 또 다른 빠른 해결책을 찾았습니다.
SELECT s.name FROM spelers s WHERE ascii(s.name) in (ascii("B"),ascii("D")) ORDER BY 1 함수 ascii ( ) 문자열의 첫 번째 문자 만 확인합니다.
설명
-
(name)?
답변
이니셜을 확인하기 위해 종종 (큰 따옴표 포함). 이식 할 수는 없지만 매우 빠릅니다. 내부적으로는 단순히 텍스트를 해독하고 첫 번째 문자를 반환하며 유형이 1 바이트 고정 길이이기 때문에 “char”비교 작업이 매우 빠릅니다.
SELECT s.name FROM spelers s WHERE s.name::"char" =ANY( ARRAY[ "char" "B", "D" ] ) ORDER BY 1 "char"로 캐스팅하는 것이 @ Sole021의 ascii() slution보다 빠르지 만 UTF8과 호환되지 않습니다 (또는 그 문제), 단순히 첫 번째 바이트를 반환하므로 비교가 일반 7 비트 ASCII 문자와 비교되는 경우에만 사용해야합니다.
Answer
이러한 경우를 처리하기 위해 아직 언급되지 않은 두 가지 방법이 있습니다.
-
부분 (또는 전체 범위에 대해 수동으로 생성 된 경우 분할 됨) 인덱스-가장 유용한 경우 데이터의 하위 집합 만 필요합니다 (예 : 일부 유지 보수 중 또는 일부보고를위한 임시) :
CREATE INDEX ON spelers WHERE name LIKE "B%" -
테이블 자체 분할 (첫 번째 문자를 파티션 키로 사용)-이 기술은 특히 wort입니다. h PostgreSQL 10+ (덜 고통스러운 파티셔닝) 및 11+ (쿼리 실행 중 파티션 프 루닝)를 고려합니다.
또한 테이블의 데이터를 정렬하면 BRIN 색인 (첫 번째 문자에 걸쳐)을 사용하면 이점이 있습니다.
답변
아마 더 빠른 단일 문자 비교 :
SUBSTR(s.name,1,1)="B" OR SUBSTR(s.name,1,1)="D" 댓글
- 아니요 정말.
column LIKE 'B%'는 열에 하위 문자열 함수를 사용하는 것보다 더 효율적입니다.
s.name색인이 생성 되었습니까?name의 색인이 여기에서 유용 할 수 있습니다.