PostgreSQL에서 인덱스의 모든 기능 사용

Postgres 세계에서 인덱스는 데이터베이스 저장소("힙"이라고 함)를 효율적으로 탐색하는 데 필수적입니다. Postgres는 클러스터링을 지원하지 않으며 MVCC 아키텍처로 인해 동일한 튜플의 여러 버전이 생성됩니다. 따라서 애플리케이션을 지원하기 위해 효율적인 인덱스를 생성하고 유지 관리하는 것이 매우 중요합니다.

다음은 인덱스 사용을 최적화하고 개선하기 위한 몇 가지 팁입니다.

참고: 아래 표시된 쿼리는 수정되지 않은 파길라 샘플 데이터베이스.

커버링 인덱스 사용

비활성 사용자의 이메일 주소 추출 요청을 살펴보겠습니다. 테이블 customer 칼럼이 있다 active, 쿼리는 간단합니다.

pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on customer  (cost=0.00..16.49 rows=15 width=32)
   Filter: (active = 0)
(2 rows)

쿼리는 전체 테이블 스캔 시퀀스를 호출합니다. customer. 열에 인덱스를 만들어 봅시다 active:

pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using idx_cust1 on customer  (cost=0.28..12.29 rows=15 width=32)
   Index Cond: (active = 0)
(2 rows)

도움이 되었고 후속 스캔은 "index scan". 이는 Postgres가 "idx_cust1"라고 말한 다음 테이블 힙을 계속 검색하여 다른 열의 값을 읽습니다(이 경우 열 email) 쿼리에 필요한 것입니다.

포괄 인덱스는 PostgreSQL 11에 도입되었습니다. 인덱스 자체에 하나 이상의 추가 열을 포함할 수 있습니다. 해당 값은 인덱스 데이터 저장소에 저장됩니다.

이 기능을 활용하고 인덱스 내부에 이메일 값을 추가하면 Postgres는 값에 대해 테이블 ​​힙을 검색할 필요가 없습니다. email. 이것이 작동하는지 봅시다:

pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Only Scan using idx_cust2 on customer  (cost=0.28..12.29 rows=15 width=32)
   Index Cond: (active = 0)
(2 rows)

«Index Only Scan'는 이제 쿼리에 인덱스만 필요하므로 테이블 힙을 읽기 위한 모든 디스크 I/O를 방지하는 데 도움이 된다고 알려줍니다.

커버링 인덱스는 현재 B-트리에만 사용할 수 있습니다. 그러나 이 경우 유지 관리 노력이 더 많이 듭니다.

부분 인덱스 사용

부분 인덱스는 테이블에 있는 행의 하위 집합만 인덱싱합니다. 이렇게 하면 인덱스 크기가 절약되고 스캔 속도가 빨라집니다.

캘리포니아에 있는 고객의 이메일 주소 목록을 얻고 싶다고 가정해 보겠습니다. 요청은 다음과 같습니다.

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                              QUERY PLAN
----------------------------------------------------------------------
 Hash Join  (cost=15.65..32.22 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=15.54..15.54 rows=9 width=4)
         ->  Seq Scan on address a  (cost=0.00..15.54 rows=9 width=4)
               Filter: (district = 'California'::text)
(6 rows)

일반 인덱스가 제공하는 것:

pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Hash Join  (cost=12.98..29.55 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=12.87..12.87 rows=9 width=4)
         ->  Bitmap Heap Scan on address a  (cost=4.34..12.87 rows=9 width=4)
               Recheck Cond: (district = 'California'::text)
               ->  Bitmap Index Scan on idx_address1  (cost=0.00..4.34 rows=9 width=0)
                     Index Cond: (district = 'California'::text)
(8 rows)

스캔 address 인덱스 스캔으로 대체되었습니다. idx_address1그런 다음 힙을 스캔했습니다. address.

이것은 빈번한 쿼리이고 최적화가 필요하기 때문에 부분 인덱스를 사용할 수 있습니다. ‘California’:

pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Hash Join  (cost=12.38..28.96 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=12.27..12.27 rows=9 width=4)
         ->  Index Only Scan using idx_address2 on address a  (cost=0.14..12.27 rows=9 width=4)
(5 rows)

이제 쿼리는 읽기만 합니다. idx_address2 그리고 테이블을 만지지 않는다 address.

다중 값 인덱스 사용

인덱싱할 일부 열은 스칼라 데이터 유형을 포함하지 않을 수 있습니다. 다음과 같은 열 유형 jsonb, arrays и tsvector 복합 또는 다중 값을 포함합니다. 이러한 열을 인덱싱해야 하는 경우 일반적으로 해당 열의 모든 개별 값을 검색해야 합니다.

실패한 테이크의 컷이 포함된 모든 영화의 제목을 찾아봅시다. 테이블 film 라는 텍스트 열이 있습니다. special_features. 영화에 이 "특수 속성"이 있는 경우 열에는 요소가 텍스트 배열로 포함됩니다. Behind The Scenes. 이러한 모든 영화를 검색하려면 "Behind The Scenes"가 포함된 모든 행을 선택해야 합니다. 어떤 배열 값 special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

중첩 연산자 @> 오른쪽이 왼쪽의 하위 집합인지 확인합니다.

요청 계획:

pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)
   Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)

67의 비용으로 전체 힙 스캔을 요청합니다.

일반 B-트리 인덱스가 도움이 되는지 살펴보겠습니다.

pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)
   Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)

지수는 고려조차 하지 않았다. B-트리 인덱스는 인덱싱된 값의 개별 요소 존재를 인식하지 못합니다.

GIN 인덱스가 필요합니다.

pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on film  (cost=8.04..23.58 rows=5 width=15)
   Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
   ->  Bitmap Index Scan on idx_film2  (cost=0.00..8.04 rows=5 width=0)
         Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)

GIN 인덱스는 인덱싱된 복합 값에 대한 단일 값 매핑을 지원하므로 쿼리 계획 비용이 절반 이상 감소합니다.

중복 인덱스 제거

인덱스는 시간이 지남에 따라 누적되며 때로는 새 인덱스가 이전 인덱스 중 하나와 동일한 정의를 포함할 수 있습니다. 카탈로그 뷰를 사용하여 사람이 읽을 수 있는 인덱스의 SQL 정의를 얻을 수 있습니다. pg_indexes. 동일한 정의를 쉽게 찾을 수도 있습니다.

 SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=#   SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-#     FROM pg_indexes
pagila-# GROUP BY defn
pagila-#   HAVING count(*) > 1;
                                indexes                                 |                                defn
------------------------------------------------------------------------+------------------------------------------------------------------
 {payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX  ON public.payment_p2017_01 USING btree (customer_id
 {payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX  ON public.payment_p2017_02 USING btree (customer_id
 {payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX  ON public.payment_p2017_03 USING btree (customer_id
 {idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_04 USING btree (customer_id
 {payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX  ON public.payment_p2017_05 USING btree (customer_id
 {idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_06 USING btree (customer_id
(6 rows)

상위 집합 인덱스

많은 인덱스가 생길 수 있으며 그 중 하나는 다른 인덱스를 인덱싱하는 열의 상위 집합을 인덱싱합니다. 이는 바람직할 수도 있고 바람직하지 않을 수도 있습니다. 상위 집합은 인덱스 전용 스캔을 생성할 수 있지만 이는 좋지만 너무 많은 공간을 차지하거나 상위 집합이 최적화하려는 쿼리가 더 이상 사용되지 않을 수 있습니다.

이러한 인덱스의 정의를 자동화해야 하는 경우 다음으로 시작할 수 있습니다. pg_index 테이블에서 pg_catalog.

사용되지 않은 인덱스

데이터베이스를 사용하는 애플리케이션이 발전함에 따라 이들이 사용하는 쿼리도 발전합니다. 이전에 추가된 인덱스는 더 이상 쿼리에서 사용할 수 없습니다. 인덱스가 스캔될 때마다 통계 관리자에 의해 표시되고 시스템 카탈로그 보기에 표시됩니다. pg_stat_user_indexes 값을 볼 수 있습니다 idx_scan, 누적 카운터입니다. 일정 기간(예: 한 달) 동안 이 값을 추적하면 어떤 인덱스가 사용되지 않고 삭제될 수 있는지에 대한 좋은 아이디어를 얻을 수 있습니다.

다음은 스키마에 있는 모든 인덱스의 현재 스캔 수를 가져오는 쿼리입니다. ‘public’:

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM   pg_catalog.pg_stat_user_indexes
pagila-# WHERE  schemaname = 'public'
pagila-# LIMIT  10;
    relname    |    indexrelname    | idx_scan
---------------+--------------------+----------
 customer      | customer_pkey      |    32093
 actor         | actor_pkey         |     5462
 address       | address_pkey       |      660
 category      | category_pkey      |     1000
 city          | city_pkey          |      609
 country       | country_pkey       |      604
 film_actor    | film_actor_pkey    |        0
 film_category | film_category_pkey |        0
 film          | film_pkey          |    11043
 inventory     | inventory_pkey     |    16048
(10 rows)

더 적은 수의 잠금으로 인덱스 재구축

예를 들어 인덱스가 비대해지면 인덱스를 다시 빌드해야 하는 경우가 많으며 다시 빌드하면 스캔 속도가 빨라질 수 있습니다. 또한 인덱스가 손상될 수 있습니다. 인덱스 매개변수를 변경하려면 인덱스를 다시 빌드해야 할 수도 있습니다.

병렬 인덱스 생성 활성화

PostgreSQL 11에서는 B-Tree 인덱스 생성이 동시에 이루어집니다. 생성 프로세스의 속도를 높이기 위해 여러 병렬 작업자를 사용할 수 있습니다. 그러나 다음 구성 옵션이 올바르게 설정되었는지 확인하십시오.

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

기본값이 너무 작습니다. 이상적으로 이러한 숫자는 프로세서 코어의 수와 함께 증가해야 합니다. 더 읽어보기 선적 서류 비치.

백그라운드 인덱스 생성

옵션을 사용하여 백그라운드에서 색인을 생성할 수 있습니다. CONCURRENTLY 팀 CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

이 인덱스 생성 절차는 테이블에 대한 잠금이 필요하지 않으므로 쓰기 작업을 차단하지 않는다는 점에서 일반적인 인덱스 생성 절차와 다릅니다. 반면에 더 많은 시간이 걸리고 더 많은 자원을 소비합니다.

Postgres는 애플리케이션이 폭발적으로 성장하는 경우 데이터베이스를 관리하는 방법뿐만 아니라 특별한 경우를 해결하는 방법과 색인 생성을 위한 많은 유연성을 제공합니다. 이 팁이 쿼리 속도를 높이고 데이터베이스 확장을 준비하는 데 도움이 되기를 바랍니다.

출처 : habr.com