PostgreSQL 데이터베이스가 있는데 인덱스와 파티셔닝을 사용할 때 이상한 동작을 발견했습니다. 엔진 버전은 10.21입니다.
이제 다음과 같은 구조의 테이블이 있습니다.
guid varchar(50) PK
guid_a varchar(50)
data text
part_key varchar(2)
다른 열이 있지만 중요하지 않습니다. 이 테이블에서 실행해야 하는 쿼리는 다음과 같습니다.'
select * from mytable where guid_a = 'jxxxxx-xxxxxxx' and data like '%7263628%';
설명해 보겠습니다. guid_a 열에는 "jxxxx-xxxxxxx" 형식으로 사람을 식별하는 코드가 포함되어 있습니다. 여기서 "x"는 숫자입니다. 처음 두 자리 숫자의 범위는 00부터 99까지입니다. 예를 들면 다음과 같습니다.
j01xxx-xxxxxx
j02xxx-xxxxxx
...
j99xxx-xxxxxx
이 열에 인덱스를 생성한 다음 trgm 모듈을 사용하여 데이터 열에도 인덱스를 생성했습니다. 쿼리를 시작한 후 성능이 크게 향상되었습니다. 여태까지는 그런대로 잘됐다.
나는 또한 파티셔닝을 사용하기로 결정했습니다(테이블에는640만 개의 레코드) 그리고 나는 guid_a 값의 처음 두 자리만 포함하는 part_key 열에 99개의 파티션(목록 기준)을 만들었습니다. 파티션당 평균 65,000개의 행이 있는 99개의 파티션을 얻었습니다. 각 파티션에는 이전에 설명한 것과 동일한 인덱스가 있습니다. 성능이 다시 향상되었습니다. 분명히 파일 쿼리에는 part_key에 대한 또 다른 조건이 있어서 엔진이 쿼리해야 할 파티션을 알 수 있습니다.
이제 이상한 일을 해보자. 파티션 없이 테이블에 trgm 인덱스를 삭제했는데 놀랍게도 속도가 더 빨랐습니다. 분할된 테이블보다 훨씬 빠릅니다. 분할된 테이블의 trgm 인덱스도 삭제합니다.
설명에서 제가 발견한 것은 분할되지 않은 테이블에 대한 쿼리는 엔진이 인덱스 스캔만 수행하도록 강제한다는 것입니다(그러면 데이터 테이블의 두 번째 조건에 대한 또 다른 스캔이 있어야 하지 않습니까?).
반면, 분할된 테이블에서는 Hitman 인덱스 스캔, 힙 스캔, 추가를 차례로 수행합니다. 이는 640만 개의 행을 모두 인덱싱하는 것보다 확실히 비용이 더 많이 듭니다.
다른 값으로 여러 테스트를 수행했지만 결과는 동일했습니다.
성능:
일반적으로:
분할된 테이블에서 11ms guid_a에 하나의 인덱스만 있는 분할되지 않은 테이블에서 9ms 2개의 인덱스(trgm을 사용하는 데이터 열의 두 번째 인덱스)가 있는 분할되지 않은 테이블에서 20ms.
여기서 무슨 일이 일어나고 있는 걸까요?