PostgreSQL | 쿼리 성능 분석하기

PostgreSQL | 쿼리 성능 분석하기

PostgreSQL에서 EXPLAIN과 EXPLAIN ANALYZE는 쿼리 성능을 분석하고 최적화하는 데 매우 중요한 도구이다. 두 명령어는 쿼리가 실행될 때 사용되는 실행 계획과 그에 따른 자원 소비를 자세히 보여준다. 이를 통해 쿼리의 성능 병목을 파악하고 최적화할 수 있다. 아래에서 EXPLAIN과 EXPLAIN ANALYZE에 대해 설명하고, 실제 예시를 통해 그 차이를 살펴보겠다.

EXPLAIN

EXPLAIN 명령어는 주어진 쿼리가 어떤 실행 계획을 사용할지 설명한다. 쿼리를 실제로 실행하지 않고 실행 계획을 제공하므로, 데이터베이스가 데이터를 어떻게 검색하고 처리할지 이해하는 데 유용하다. 다음은 EXPLAIN 명령어의 기본적인 사용 예이다.

EXPLAIN SELECT * FROM employees WHERE salary > 50000;

이 명령어는 실행 계획을 텍스트 형식으로 출력한다. 출력 결과에는 각 단계별로 어떤 작업이 수행되는지, 각 작업의 예상 비용 등이 포함되어 있다.

EXPLAIN ANALYZE

EXPLAIN ANALYZE는 EXPLAIN과 유사하지만, 쿼리를 실제로 실행한 후 실행 계획과 함께 실제 실행 시간, 처리된 행 수 등의 추가 정보를 제공한다. 이는 쿼리의 실제 성능을 평가하고, 예상된 실행 계획과 실제 실행 결과를 비교하는 데 유용하다. 다음은 EXPLAIN ANALYZE 명령어의 사용 예이다.

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 50000;

이 명령어는 쿼리를 실행한 후, 실제로 소요된 시간과 처리된 행의 수 등을 출력한다. 이를 통해 쿼리의 성능 병목 지점을 파악하고 최적화할 수 있다.

주요차이점

• 실행 여부: EXPLAIN은 쿼리를 실행하지 않고 실행 계획만 보여준다. 반면 EXPLAIN ANALYZE는 쿼리를 실제로 실행하고 실행 계획과 실제 성능 데이터를 함께 보여준다.
• 성능 데이터: EXPLAIN은 예상된 비용과 행 수 등을 제공한다. EXPLAIN ANALYZE는 실제 실행 시간과 처리된 행 수 등을 추가로 제공한다.

실제 QUERY PLAN 분석

Merge Left Join  (cost=179.03..5220.89 rows=8031 width=12) (actual time=0.042..7.635 rows=11606 loops=1)
  Merge Cond: (mls1.meas_dtm = sbswco_meas_mstr04.meas_dtm)
  ->  Index Only Scan Backward using gjswms003m_pk_1 on sbswco_meas_mstr04 mls1  (cost=0.42..5021.30 rows=8031 width=8) (actual time=0.009..6.761 rows=11606 loops=1)
        Index Cond: ((rcs_id = 'RCS1'::text) AND (data_typ = 'AI'::text))
        Heap Fetches: 1970
  ->  Sort  (cost=178.61..178.80 rows=76 width=12) (actual time=0.030..0.031 rows=5 loops=1)
        Sort Key: sbswco_meas_mstr04.meas_dtm DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using gjswms003m_pk_1 on sbswco_meas_mstr04  (cost=0.43..176.24 rows=76 width=12) (actual time=0.024..0.026 rows=5 loops=1)
              Index Cond: ((meas_dtm >= to_timestamp(to_char((CURRENT_TIMESTAMP - '00:05:00'::interval), 'YYYY-MM-DD HH24:MI'::text), 'YYYY-MM-DD HH24:MI'::text)) AND (meas_dtm <= CURRENT_TIMESTAMP) AND ((rcs_id)::text = 'RCS1'::text) AND ((data_typ)::text = 'AI'::text))
Planning Time: 0.793 ms
Execution Time: 7.954 ms

이 예제는 두 테이블을 조인하는 복잡한 쿼리의 실행 계획이다. 주요 정보를 살펴보면

• Merge Left Join: 조인을 수행하는 방법을 나타낸다. 예상 비용(cost)과 실제 시간(actual time), 처리된 행(rows) 수가 포함되어 있다.
• Index Only Scan Backward: 인덱스만을 사용하여 데이터를 검색하는 방법을 나타낸다. Index Cond는 인덱스 조건을 나타내며, 실제로 읽은 행 수와 걸린 시간이 포함되어 있다.
• Sort: 조인 조건을 맞추기 위해 데이터를 정렬하는 과정이다. 정렬 키(Sort Key)와 정렬 방법(Sort Method), 메모리 사용량(Memory) 등이 포함되어 있다.
• Planning Time과 Execution Time은 EXPLAIN ANALYZE 명령어의 출력에서 중요한 부분으로, 각각 쿼리 계획을 세우는 데 걸린 시간과 실제 쿼리를 실행하는 데 걸린 시간을 나타낸다.
• Planning Time: 0.793 ms:
  이 시간은 매우 짧아 쿼리 계획 수립이 효율적임을 나타낸다.
• Execution Time: 7.954 ms:
  이 시간은 쿼리의 복잡성과 처리하는 데이터의 양에 따라 적절하게 평가할 필요가 있다.
  몇 밀리초에서 수십 밀리초의 실행 시간은 대부분의 응용 프로그램에서 충분히 빠르다.
• 비용(Cost):
  cost=179.03..5220.89: 비용이 낮을수록 좋다. 이 비용은 상대적인 척도이므로 다른 쿼리들과 비교하여 평가한다.
  비용이 높은 경우 인덱스 추가, 쿼리 리팩토링 등을 통해 최적화할 수 있다.
• Row Count:
  예상 행 수와 실제 행 수가 다를 경우, 실행 계획이 정확하지 않을 수 있다.
  예제에서 rows=8031로 예상했으나 실제로 rows=11606을 처리했다.
  이러한 차이가 지속적으로 발생하면 통계 정보를 갱신하거나 쿼리를 재작성해야 할 수 있다.

Planning Time

Planning Time은 PostgreSQL이 쿼리 실행 계획을 수립하는 데 소요된 시간을 의미한다. 이는 SQL 쿼리를 입력받아, 해당 쿼리를 효율적으로 실행하기 위한 최적의 실행 계획을 생성하는 과정에서 소요되는 시간이다. 예를 들어, 어떤 인덱스를 사용할지, 어떤 조인 방법을 사용할지 등을 결정하는 시간이 포함된다.

Execution Time

Execution Time은 쿼리가 실제로 실행되어 결과를 반환하는 데 소요된 시간을 의미한다. 이는 데이터베이스가 실행 계획에 따라 데이터를 검색하고, 필요한 연산을 수행하여 최종 결과를 생성하는 데 걸린 시간이다. Execution Time은 쿼리가 데이터베이스 성능에 미치는 실제 영향을 나타내는 지표로 사용된다.

 

이 두 시간은 쿼리의 전체 성능을 평가하는 데 중요한 지표가 된다.

Planning Time이 상대적으로 짧은 반면, Execution Time이 더 긴 경우, 쿼리 최적화가 필요할 수 있다. 이 정보를 통해 쿼리 성능 병목 지점을 파악하고, 적절한 최적화 작업을 수행할 수 있다.

 

성능좋은 쿼리

좋은 쿼리는 일반적으로 다음과 같은 특성을 가진다

 

1.  낮은 Planning Time

• 쿼리 실행 계획을 세우는 데 걸리는 시간이 짧을수록 좋다.
• 그러나, Planning Time은 대부분의 경우 매우 짧기 때문에, 이는 큰 문제가 되지 않는다.
• Planning Time이 매우 긴 경우, 복잡한 쿼리 또는 많은 테이블이 관련된 쿼리에서 문제가 될 수 있다.

2. 낮은 Execution Time

• 실제 쿼리를 실행하는 데 걸리는 시간이 짧을수록 좋다.
• 이는 데이터 검색, 조인, 정렬 등 모든 작업이 효율적으로 수행된다는 의미이다.

3. 낮은 Cost

• 비용(cost)은 쿼리 실행 계획에서 예상되는 자원 소모를 나타낸다.
• 낮은 비용은 쿼리가 CPU, 메모리, 디스크 I/O 등의 자원을 적게 사용한다는 것을 의미한다.
• 비용은 실제 시간이 아닌 상대적인 척도이지만, 비용이 낮을수록 성능이 좋은 쿼리일 가능성이 놓다

4. 적절한 Row Count

• 예상된 행(row) 수와 실제로 처리된 행 수가 일치하는 것이 좋다.
• 예상된 행 수와 실제 행 수가 크게 차이날 경우, 실행 계획이 부정확하다는 것을 의미할 수 있다.