[SQL] PostgreSQL을 신속하게 비슷한 문자열 찾기

PostgreSQL을 신속하게 비슷한 문자열 찾기

나는 테이블에 유사한 문자열의 순위를 작성해야합니다.

나는 다음과 같은 테이블이

create table names (
name character varying(255)
);

현재, 나는 유사성 기능을 제공 pg_trgm 모듈을 사용하고 있습니다,하지만 난 효율성 문제가 있습니다. Postgres의 설명서가 제안처럼 인덱스를 생성 :

CREATE INDEX trgm_idx ON names USING gist (name gist_trgm_ops);

나는 다음과 같은 쿼리를 실행하고 있습니다 :

select (similarity(n1.name, n2.name)) as sim, n1.name, n2.name
from names n1, names n2
where n1.name != n2.name and similarity(n1.name, n2.name) > .8
order by sim desc;

쿼리는 작동하지만, 당신은 이름의 수백이있을 때 정말 느립니다. 또한, 어쩌면 내가 SQL의 비트를 잊었지만 나는 오류 "존재하지 않는 열 시뮬레이션의"를받지 않고 조건 및 시뮬레이션> 0.8을 사용할 수없는 이유를 이해하지 않습니다.

나는 빠른 쿼리를 만들기 위해 어떤 힌트를하고 싶습니다.

해결법

1. ==============================

   1.포스트 그레스 9.6의 구성 파라미터는 pg_trgm.similarity_threshold set_limit 함수 () 및 show_limit 대체 (). 기능은 여전히 ​​작동되지하지만 있습니다.

   포스트 그레스 9.6의 구성 파라미터는 pg_trgm.similarity_threshold set_limit 함수 () 및 show_limit 대체 (). 기능은 여전히 ​​작동되지하지만 있습니다.

   GIN 및 GIST 인덱스의 성능은 또한 포스트 그레스 9.1 이후 여러 가지 방법으로 개선되었다.

   사용 set_limit () 대신 % 연산자. 둘 다 pg_trgm 모듈에 의해 제공됩니다.

   당신이이 방법은 모든 요소와 테이블의 모든 다른 요소 사이의 유사성은 (거의 교차 가입)을 계산해야합니다. 테이블 1000 개 행이있는 경우 그이 조건에 대해 확인하고 정렬 할 수 있습니다 전에, 즉, 이미 1000000 (!) 유사성 계산이다. 대신보십시오 :

   SET pg_trgm.similarity_threshold = 0.8; -- Postgres 9.6 or later
   -- SELECT set_limit(0.8);               -- for older versions
   
   SELECT similarity(n1.name, n2.name) AS sim, n1.name, n2.name
   FROM   names n1
   JOIN   names n2 ON n1.name <> n2.name
                  AND n1.name % n2.name
   ORDER  BY sim DESC;
   

   빠른 진도의 명령에 의해,하지만 여전히 느린.

   당신은 가입 크로스 (지원이 일치하는 기능 지수) 전 (첫 글자와 일치하는 등의) 전제 조건을 추가하여 가능한 한 쌍의 수를 제한 할 수 있습니다. 십자가의 성능은 O (N²)와 열화 가입.

   지사 질문에 관해서는 :

   WHERE ... sim > 0.8
   

   당신이 WHERE 또는 HAVING 절에 출력 열을 참조 할 수 없기 때문에 작동하지 않습니다. 그에 따라입니다 (약간의 혼란, 부여) SQL 표준 - 어떤 다른 RDBMS가 아니라 느슨하게 처리됩니다.

   한편 :

   ORDER BY sim DESC
   

   출력 열이 GROUP BY와 ORDER BY에서 사용할 수 있기 때문에 작동합니다. 세부:

   나는 내 주장을 확인하기 위해 내 기존 테스트 서버에서 빠른 테스트를 실행. PostgreSQL의 9.1.4. 촬영 시간 (5 최고의)을 분석 설명한다.

   CREATE TEMP table t AS 
   SELECT some_col AS name FROM some_table LIMIT 1000;  -- real life test strings
   

   GIN 인덱스 시험의 첫 번째 라운드 :

   CREATE INDEX t_gin ON t USING gin(name gin_trgm_ops);  -- round1: with GIN index
   

   GIST 지수 테스트의 두 번째 라운드 :

   DROP INDEX t_gin;
   CREATE INDEX t_gist ON t USING gist(name gist_trgm_ops);
   

   새로운 쿼리 :

   SELECT set_limit(0.8);
   
   SELECT similarity(n1.name, n2.name) AS sim, n1.name, n2.name
   FROM   t n1
   JOIN   t n2 ON n1.name <> n2.name
              AND n1.name % n2.name
   ORDER  BY sim DESC;
   

   GIN 지수는 64 안타를 사용 : 총 런타임 : 484.022 밀리 GIST 지수는 64 안타를 사용 : 총 런타임 : 248.772 밀리

   올드 쿼리 :

   SELECT (similarity(n1.name, n2.name)) as sim, n1.name, n2.name
   FROM   t n1, t n2
   WHERE  n1.name != n2.name
   AND    similarity(n1.name, n2.name) > 0.8
   ORDER  BY sim DESC;
   

   GIN 지수는 64 안타를 사용하지 : 총 런타임 : 6345.833 밀리 GIST 지수는 64 안타를 사용하지 : 총 런타임 : 6335.975 밀리

   그렇지 않으면 동일한 결과. 조언이 좋다. 그리고 이것은 단지 1000 행을 위해!

   GIN는 종종 뛰어난 읽기 성능을 제공합니다 :

   하지만이 특별한 경우 :

from https://stackoverflow.com/questions/11249635/finding-similar-strings-with-postgresql-quickly by cc-by-sa and MIT license