쿼리 최적화 (Postgres vs Oracle)

쿼리 최적화란?

쿼리 최적화(Query Optimization)는 SQL 문을 최소한의 자원으로 최대한 빠르게 실행되도록 만드는 과정입니다.

이를 위해 DBMS는 실행계획(Execution Plan)을 생성하고, 다양한 전략과 알고리즘을 활용합니다.

---

공통적으로 사용되는 최적화 전략

전략	설명
인덱스 사용	WHERE / JOIN / ORDER BY 등에서 인덱스 활용
조인 순서 최적화	JOIN 테이블의 순서에 따라 성능 달라짐
필터 조건 푸시다운	필요한 행만 일찍 필터링하여 줄임
LIMIT/OFFSET 최적화	페이징 시 필요 없는 데이터 처리 최소화
서브쿼리 플래튼(flattening)	서브쿼리를 JOIN으로 변경해 효율 향상

---

PostgreSQL의 쿼리 최적화 전략

내부 구조

• PostgreSQL은 코스트 기반 최적화기(Cost-Based Optimizer, CBO) 사용
• 가능한 실행계획을 생성하고 비용(cost)을 계산해 가장 비용이 적은 계획 선택

PostgreSQL 주요 특징

항목	설명
실행계획 보기	EXPLAIN (ANALYZE, BUFFERS)
조인 알고리즘	Nested Loop, Hash Join, Merge Join
인덱스 활용	B-tree, Hash, GIN, GiST 등 다양한 인덱스
통계 기반 계획	ANALYZE 명령으로 최신 통계 유지 필요
실행계획 캐시 없음	같은 쿼리라도 매번 최적화 (prepared 제외)

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE age > 30;

PostgreSQL 팁

• 쿼리 반복 실행 시 prepared statement 고려 (단, 실행계획 고정 위험도 있음)
• pg_stat_statements로 느린 쿼리 탐지 가능

---

Oracle의 쿼리 최적화 전략

내부 구조

• Oracle도 CBO 기반, 하지만 더 정교하고 다양한 힌트(Hint)를 지원
• Oracle은 실행계획을 Library Cache에 저장해 재사용

Oracle 주요 특징

항목	설명
실행계획 보기	EXPLAIN PLAN, AUTOTRACE, DBMS_XPLAN.DISPLAY_CURSOR
조인 알고리즘	Nested Loop, Hash Join, Merge Join
힌트 사용 가능	/*+ INDEX(t idx_name) */ 형식
쿼리 플랜 캐싱	동일 SQL 재사용 시 빠름
Query Transformation 기능	View merging, Subquery unnesting 등 적극적

SELECT /*+ FULL(emp) */ * FROM emp;

Oracle 팁

• 쿼리 실행 히스토리, Adaptive Plan, Cardinality Feedback 기능 존재
• SQL Plan Baseline을 통해 실행계획을 고정할 수도 있음

---

PostgreSQL vs Oracle 쿼리 최적화 비교

항목	PostgreSQL	Oracle
실행계획 최적화 방식	Cost-based	Cost-based + 다양한 힌트 지원
실행계획 캐시	거의 없음 (prepared 제외)	있음 (Library Cache)
조인 알고리즘	Nested Loop, Hash Join, Merge Join	동일
힌트 사용	없음 (3rd-party 확장 필요)	풍부한 힌트 문법
통계 관리	ANALYZE 수동 필요	자동 수집 및 피드백
자동 최적화 기능	단순	매우 정교 (Adaptive Plan 등)

---