EDB Query Advisor 사용법: PostgreSQL 인덱스 추천과 쿼리 성능 최적화

작성자: Dilip Kumar
2023년 8월 7일

PostgreSQL 쿼리 성능을 최적화하려면 실제 워크로드에 적합한 인덱스를 선택해야 합니다. 이 글에서는 EDB Query Advisor가 워크로드를 분석해 인덱스를 추천하는 원리와 구성 파라미터, 추천 생성 방법을 살펴보고 실제 SQL 예제로 적용 과정을 확인합니다.

엔터프라이즈 데이터베이스의 쿼리 성능과 DBA의 역할

엔터프라이즈급 데이터베이스에서 데이터베이스 관리자(Database Administrator, DBA)는 쿼리 성능을 최적화하는 중요한 역할을 담당합니다. 특히 대규모 데이터베이스를 운영할 때는 DBA의 수작업 부담을 줄이면서도 적절한 인덱스를 선택하는 것이 중요합니다.

이를 위해 DBA는 데이터베이스 워크로드의 광범위한 부분을 분석하고, 정기적으로 실행되거나 워크로드에서 큰 비중을 차지하는 쿼리의 실행 계획을 검토해야 합니다. 이후 어떤 인덱스가 도움이 될지 평가할 수 있지만, 분석 결과가 항상 정확한 것은 아니며 특정 인덱스를 추가했을 때 성능이 오히려 저하될 수도 있습니다.

자동 인덱스 추천 도구가 필요한 이유

자동 인덱스 선택 도구는 실제 인덱스를 생성하지 않고도 전체 워크로드에 유용한 인덱스를 식별하는 데 도움을 줄 수 있습니다.

EDB Query Advisor는 실시간 워크로드 통계를 지속적으로 수집하고 이를 기반으로 인덱스를 추천하는 자동 인덱스 추천 도구입니다. 실제 워크로드에 가상(hypothetical) 인덱스를 적용해 실험하므로 유용하지 않을 가능성이 있는 인덱스를 추천에서 제외하도록 설계되었습니다.

또한 추천된 각 인덱스의 예상 크기와 비용 절감률을 제공합니다. DBA는 이 정보를 바탕으로 해당 인덱스를 실제로 생성할지 판단할 수 있습니다.

EDB Query Advisor의 주요 차별점

일부 데이터베이스의 인덱스 추천 도구와 달리 EDB Query Advisor는 여러 열로 구성된 복합 인덱스뿐만 아니라 워크로드 분석을 기반으로 다양한 유형의 인덱스를 추천할 수 있습니다.

또한 최소한의 부하로 작동하도록 설계되어 운영 중인 프로덕션 시스템에서도 사용할 수 있습니다.

EDB Query Advisor 작동 원리

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

현재는 인덱스 크기와 관련된 부하만 계산하고 있지만, 앞으로는 특정 인덱스 생성으로 인한 추가 부하도 계산할 계획입니다.

EDB Query Advisor는 DBA의 인덱스 분석 및 선정 업무를 지원하고 데이터베이스 쿼리 성능을 최적화하는 데 활용할 수 있습니다.

EDB Query Advisor 사용 방법

EDB Query Advisor 구성

postgresql.conf 파일의 shared_preload_libraries 파라미터에 query_advisor를 추가합니다.

shared_preload_libraries = 'query_advisor'

설정을 적용하려면 Postgres를 재시작합니다. 이후 데이터베이스에서 다음 명령을 실행해 EDB Query Advisor 확장을 생성합니다.

CREATE EXTENSION query_advisor;

Query Advisor 구성 파라미터

다음 사용자 정의 GUC 파라미터는 EDB Query Advisor 확장의 동작을 제어합니다. 다음 파라미터를 수정한 후 변경 사항을 적용하려면 Postgres를 재로드합니다.

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

다음 파라미터를 수정한 경우에는 Postgres를 재시작해 변경 사항을 적용합니다.

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

인덱스 추천 생성

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

EDB Query Advisor 사용 예제

Query Advisor 확장을 생성하고 테스트 테이블을 만든 후 100만 개의 레코드를 삽입합니다.

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)

EDB Query Advisor FAQ

EDB Query Advisor란 무엇인가요?

EDB Query Advisor는 실시간 워크로드 통계를 수집하고 가상 인덱스를 실험해 유용한 인덱스를 추천하는 자동 인덱스 추천 도구입니다. 추천 인덱스의 예상 크기와 비용 절감률도 함께 제공합니다.

EDB Query Advisor는 실제 인덱스를 바로 생성하나요?

아니요. EDB Query Advisor는 HypoPG를 사용해 가상 인덱스를 생성하고 워크로드에 미칠 영향을 평가합니다. DBA는 추천 결과를 검토한 후 실제 인덱스를 생성할지 결정할 수 있습니다.

EDB Query Advisor를 사용하려면 어떻게 설정해야 하나요?

postgresql.conf 파일의 shared_preload_libraries 파라미터에 query_advisor를 추가하고 Postgres를 재시작해야 합니다. 이후 데이터베이스에서 CREATE EXTENSION query_advisor; 명령을 실행해 확장을 생성합니다.

인덱스 추천 결과에서는 어떤 정보를 확인할 수 있나요?

추천 인덱스를 생성할 수 있는 SQL 문과 예상 인덱스 크기, 워크로드에 대한 예상 비용 절감 비율을 확인할 수 있습니다. DBA는 이 정보를 바탕으로 실제 인덱스 생성 여부를 판단할 수 있습니다.

query_advisor.sample_rate 파라미터는 무엇을 설정하나요?

query_advisor.sample_rate는 샘플링할 쿼리의 비율을 설정하는 Double 형식의 파라미터입니다. 예를 들어 0.1은 10개 쿼리 중 1개를 샘플링한다는 의미이며, 기본값 -1에서는 1 / max_connections 값이 사용됩니다.

원문: EDB Tutorial: How To Get the Most Out of EDB Query Advisor

EDB 영업 기술 문의: 02-501-5113

이메일: salesinquiry@enterprisedb.com

홈페이지 문의