[SQL] PostgreSQL은 부분 인덱스를 사용하지 않습니다

PostgreSQL은 부분 인덱스를 사용하지 않습니다

나는 텍스트 열이 PostgreSQL의 9.2의 테이블이있다. 의이 text_col를 부르 자. 이 열의 값 (기껏해야 5-6 중복을 포함 할 수있다) 매우 독특하다. 표는 ~ 500 만 개 행이 있습니다. 절반을 다음 행은 text_col에 대한 널 (null) 값이 포함되어 있습니다. 나는 다음과 같은 쿼리를 실행할 때 나는 1-5 행을 기대합니다. 대부분의 경우 (> 80 %) 난 단지 1 개 행을 기대합니다.

explain analyze SELECT col1,col2.. colN
FROM table 
WHERE text_col = 'my_value';

BTREE 지수는 text_col에 존재합니다. 이 인덱스는 쿼리 계획에 의해 사용되지 않습니다와 나는 확실히 왜 안입니다. 이 쿼리의 출력입니다.

Seq Scan on two (cost=0.000..459573.080 rows=93 width=339) (actual time=1392.864..3196.283 rows=2 loops=1)
Filter: (victor = 'foxtrot'::text)
Rows Removed by Filter: 4077384

(또는 text_pattern_ops없이. 내 쿼리로 표현에는 LIKE 조건을 고려하지 text_pattern_ops 필요하지 않습니다,하지만 그들은 또한 평등과 일치) 내가 널되지 않은 그 값을 필터링합니다 다른 부분 인덱스를 추가,하지만 도움이되지 않았다.

CREATE INDEX name_idx
  ON table
  USING btree
  (text_col COLLATE pg_catalog."default" text_pattern_ops)
  WHERE text_col IS NOT NULL;

세트 enable_seqscan = 오프 시퀀스를 사용하여 검사를 중지; 플래너는 여전히 index_scan 통해 seqscan을 선택합니다. 요약해서 말하자면...

해결법

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

   1.부분 인덱스는 분명히 필요하지 않은 테이블의 절반 행을 제외하는 좋은 아이디어이다. 간단한 :

   부분 인덱스는 분명히 필요하지 않은 테이블의 절반 행을 제외하는 좋은 아이디어이다. 간단한 :

   CREATE INDEX name_idx ON table (text_col)
   WHERE text_col IS NOT NULL;
   

   인덱스를 생성 한 후 테이블을 분석 실행해야합니다. (자동 진공 자동으로 수동으로하지 않지만, 당신이 바로 생성 후 테스트하는 경우, 테스트가 실패하면 잠시 후 그 않습니다.)

   그런 다음, 특정 부분 인덱스를 사용할 수있는 쿼리 플래너를 설득, 반복 조건이 쿼리에서 어디 - 완전히 중복 보인다하더라도 :

   SELECT col1,col2, .. colN
   FROM   table 
   WHERE  text_col = 'my_value'
   AND   text_col IS NOT NULL;  -- repeat condition

   짜잔.

   문서 별 :

   매개 변수가있는 쿼리에 관해서는 : 다시, WHERE 조건을 추가, 상수로 부분 인덱스의 (중복) 술어를 추가하고 그것을 잘 작동합니다.

   포스트 그레스 9.6에서 중요한 업데이트는 크게 (쿼리를 저렴하게 할 수 있으며 쿼리 계획이 더 쉽게 같은 쿼리 계획을 선택합니다) 인덱스 만 스캔을위한 기회를 향상시킵니다. 관련 :

2. ==============================

   2.조건이 일치 WHERE 경우 부분 인덱스에만 사용됩니다. 따라서 text_col 당신이 당신의 선택에 동일한 조건을 사용하는 경우에만 사용할 수 있습니다 NULL NOT IS WHERE와 인덱스. 정렬 불일치는 해를 입힐 수 있습니다.

   조건이 일치 WHERE 경우 부분 인덱스에만 사용됩니다. 따라서 text_col 당신이 당신의 선택에 동일한 조건을 사용하는 경우에만 사용할 수 있습니다 NULL NOT IS WHERE와 인덱스. 정렬 불일치는 해를 입힐 수 있습니다.

   다음을 시도해보십시오

3. ==============================

   3.나는 그것을 알아. 빌드를하는 데 도움이 분석 pg_stats보기에 면밀한 관찰을 복용하면, 나는 문서에서 발췌을 가로 질러왔다.

   나는 그것을 알아. 빌드를하는 데 도움이 분석 pg_stats보기에 면밀한 관찰을 복용하면, 나는 문서에서 발췌을 가로 질러왔다.

   내 로컬 상자에서 상관 관계 수는 0.97이며, 생산에 0.05이었다. 따라서 플래너는 모든 행을 이동 순차적으로 대신 디스크 블록의 랜덤 액세스에 인덱스 매번 다이빙을 찾는 것이 쉽다는 것을 추정된다. 이 상관 관계 번호로 들여다하는 데 사용되는 쿼리 I입니다.

   select * from pg_stats where tablename = 'table_name' and attname = 'text_col';
   

   이 테이블은 또한 행에서 수행 몇 가지 업데이트가 있습니다. 행의 avg_width 20 바이트로 추정된다. 업데이트 텍스트 컬럼에 대한 큰 값을 가지는 경우에는 평균값을 초과하고 또한 느린 업데이트 될 수있다. 내 생각은 물리적 및 논리적 순서는 각 업데이트와 떨어져 이동 둔화되는 것이 었습니다. 나는 다음과 같은 쿼리를 실행 한 수정합니다.

   ALTER TABLE table_name SET (FILLFACTOR = 80);
   VACUUM FULL table_name;
   REINDEX TABLE table_name;
   ANALYZE table_name;
   

   아이디어는 내가 잃어버린 공간을 확보하고 물리적 및 논리적 질서를 유지하기 위해 20 %의 버퍼와 진공 각 디스크 블록을 전체 테이블을 줄 수 있다는 것입니다. 내가 이런 짓을 한 후 쿼리는 인덱스를 선택합니다.

   explain analyze SELECT col1,col2... colN
   FROM table_name 
   WHERE text_col is not null 
   AND 
   text_col = 'my_value';
   

   Index Scan using tango on two (cost=0.000..165.290 rows=40 width=339) (actual time=0.083..0.086 rows=1 loops=1)
   Index Cond: ((victor five NOT NULL) AND (victor = 'delta'::text))
   

   널 (NULL) 조건을 제외하면 비트 맵 힙 검사와 다른 인덱스를 선택합니다.

   Bitmap Heap Scan on two  (cost=5.380..392.150 rows=98 width=339) (actual time=0.038..0.039 rows=1 loops=1)
       Recheck Cond: (victor = 'delta'::text)
     ->  Bitmap Index Scan on tango  (cost=0.000..5.360 rows=98 width=0) (actual time=0.029..0.029 rows=1 loops=1)
             Index Cond: (victor = 'delta'::text)
   

   그것은 처음에 상관처럼 보였다 동안 자신의 데이터베이스에 0에 가까운 상관 값은 여전히 ​​인덱스 스캔으로 이어진 @ 마이크 스캔 인덱스 선택에 중요한 역할을 관찰했다한다. 완전히 채우기 비율을 변경하고 진공 청소기로 청소하는 것은 도움이하지만 난 이유를 확실 해요했다.

from https://stackoverflow.com/questions/26030354/postgresql-does-not-use-a-partial-index by cc-by-sa and MIT license