EDB Tutorial: EDB Query Advisor를 최대한 활용하는 방법

Dilip Kumar August 07, 2023

엔터프라이즈급 데이터베이스와 DBA의 역할

자동 인덱스 선택 도구의 필요성

EDB Query Advisor의 차별점

작동 원리

1. 워크로드 데이터 수집 쿼리가 실행될 때, Query Advisor는 쿼리의 조건(predicate)과 워크로드 정보를 수집합니다. 이를 위해 pg_qualstats 코드를 수정한 버전을 사용하며, 수집된 데이터는 해시(hash) 형태로 저장됩니다.
2. 인덱스 추천 프로세스 사용자가 인덱스 추천을 요청하면, 수집된 통계를 바탕으로 인덱스 후보를 생성합니다. 이후, HypoPG 도구를 사용하여 가상(hypothetical) 인덱스를 생성하고 이를 기반으로 최적의 인덱스를 추천합니다.
3. 최적화된 인덱스 선정 프로세스의 각 단계에서 수집된 워크로드에 가장 큰 이점을 제공하면서 부하를 최소화하는 인덱스를 선택합니다.

Query Advisor 사용 방법

EDB Query Advisor 구성

• postgresql.conf 파일에서 shared_preload_libraries 파라미터에 query_advisor를 추가하세요:

• Postgres를 재시작하세요.
• 데이터베이스에서 EDB Query Advisor 확장을 생성하세요:

Query Advisor 구성 파라미터

• query_advisor.enabled
  • Query Advisor를 활성화할지 여부를 지정합니다. Boolean 값을 가지며, 기본값은 true입니다.
• query_advisor.sample_rate
  • 샘플링할 쿼리 비율을 설정합니다. Double 값을 가지며, 예를 들어 0.1은 10개의 쿼리 중 1개를 샘플링한다는 의미입니다.
  • 기본값은 -1이며, 이는 자동으로 설정되며 1 / max_connections 값을 사용합니다.

• query_advisor.max_qual_entries
  • 추적할 최대 조건(predicate) 수를 설정합니다.
• query_advisor.max_workload_entries
  • 추적할 최대 워크로드 쿼리 수를 설정합니다.
• query_advisor.max_workload_query_size
  • 워크로드 쿼리의 최대 크기를 설정합니다.

인덱스 추천 생성

• 인덱스 추천을 생성하려면 query_advisor_index_recommendation() 함수를 호출하세요.
• 이 함수는 추천된 인덱스를 생성하는 정확한 SQL 문을 제공합니다.
• 인덱스 문과 함께 워크로드에 대한 예상 크기와 예상 비용 절감 비율(%)도 제공합니다.
• 따라서 크기와 비용 절감 비율에 따라 DBA는 해당 인덱스를 생성할 가치가 있는지 결정할 수 있습니다.

CREATE EXTENSION query_advisor ;
SET query_advisor.sample_rate =1;

CREATE TABLE test(a int, b varchar, c date);
INSERT INTO test SELECT i, 'test' || i, now() FROM generate_series(1, 1000000) AS i;
ANALYZE test;

\timing on
SELECT * FROM test WHERE c < '2023-01-30';
Time: 34.534 ms

EXPLAIN SELECT * FROM test WHERE c < '2023-01-30';
                              QUERY PLAN                               
------------------------------------------------------------------------
 Gather  (cost=1000.00..12577.43 rows=1 width=18)
   Workers Planned: 2
   ->  Parallel Seq Scan on test  (cost=0.00..11577.33 rows=1 width=18)
         Filter: (c < '2023-01-30'::date)
(4 rows)

SELECT * FROM query_advisor_index_recommendations(0,0);
       index                                                                 | estimated_size_in_bytes | estimated_pct_cost_reduction 
-------------------------------------------------------------------+---------------------------------+----------------------------------------
 CREATE INDEX ON public.test USING btree (c);  |                       26124288  |                                99.97712
(1 row)

CREATE INDEX ON public.test USING btree (c);

SELECT * FROM test WHERE c < '2023-01-30';
Time: 1.394 ms

EXPLAIN SELECT * FROM test WHERE c < '2023-01-30';
                               QUERY PLAN                               
------------------------------------------------------------------------
 Index Scan using test_c_idx on test  (cost=0.42..4.44 rows=1 width=18)
   Index Cond: (c < '2023-01-30'::date)