역사: PostgreSQL은 I/O 작업에 추상적인 금전 단위를 할당하는 비용 기반 최적화기를 사용합니다. 초기 데이터베이스 시스템은 주로 회전식 디스크를 타겟으로 했으며, 이로 인해 탐색 페널티로 인해 무작위 I/O는 순차적 읽기보다 약 40배 더 비쌌습니다. 이러한 비대칭을 완화하기 위해 Bitmap Index Scans가 도입되어 매칭되는 튜플 위치의 메모리 내 비트맵을 생성하고 큰 순서로 힙에 접근해 무작위 페이지 가져오기를 분산시켰습니다.
문제: 핵심 문제는 수천 개의 데이터 페이지에 흩어진 많은 행과 일치하는 보통 선택적인 술어를 필터링할 때 발생합니다. Index Scan은 매칭되는 튜플 포인터마다 하나의 무작위 I/O를 수행하여 기계식 디스크 난동을 일으키거나 SSD에서 과도한 I/O 요청을 초래하게 됩니다. 반대로, Bitmap Index Scan은 비트맵 구조를 구축하는 데 비용이 들고, 비트맵이 work_mem 제약으로 인해 손실되면 관련 없는 행을 처리할 수 있습니다.
해결책: 결정 기준은 cost_index()와 cost_bitmap_heap_scan() 함수 내에 존재합니다. 계획자는 쿼리를 만족시키기 위해 필요한 고유한 힙 페이지 수(pages_fetched)를 추정합니다. pages_fetched가 random_page_cost / seq_page_cost 비율을 초과하면, 최적화기는 비트맵 접근 방식을 선호하는데, 이는 정렬된 페이지 검색 비용이 무작위 접근 페널티보다 크기 때문입니다. random_page_cost를 줄이면(예: SSD 저장소의 경우 4.0에서 1.1로) 무작위 I/O에 대한 인지된 페널티가 낮아져, 계획자가 이전에 비트맵 생성을 유도했던 선택성에 대해 표준 Index Scans로 다시 돌아가게 됩니다.
재무 보고 플랫폼은 현재 회계 분기의 account_id별 transactions를 집계하는 대시보드 쿼리에서 심각한 대기 시간을 경험했습니다. 테이블은 회전식 디스크가 장착된 구형 SAN에서 5억 개의 행을 포함하고 있었습니다. 조건자 account_id = 12345는 힙에 무작위로 흩어진 약 12%의 행과 일치했습니다. 실행 계획은 14초가 소요되는 일반 Index Scan을 보여주었고, 이는 수천 개의 리프 페이지에 걸쳐 무작위 I/O 폭풍 덕분에 발생했습니다.
random_page_cost를 4.0에서 8.0으로 증가시키는 것은 무작위 디스크 탐색이 엄청나게 비용이 든다는 것을 최적화기에 명확히 신호로 보냈습니다. 이 즉각적인 변화는 계획자가 Bitmap Index Scan을 선택하게 하여 페이지 요청을 정렬된 범위로 배치하여 실행 시간을 1.8초로 단축시켰습니다. 그러나 이 전역 설정은 애플리케이션의 다른 OLTP 포인트 조회 쿼리에 페널티를 주어, 피크 거래 시간 동안 잠금 경쟁을 증가시킨 비효율적인 순차 스캔으로 전환하게 만들었습니다.
**(account_id, transaction_date, amount)**에 대한 커버링 인덱스를 생성함으로써 힙을 완전히 우회하는 Index Only Scan이 가능해져 응답 시간이 80ms로 줄어들었습니다. 읽기에 최적이었으나, 복합 인덱스는 테이블 크기를 35% 부풀리고 흡수량을 22% 감소시켰습니다. 이제 각 삽입은 두 개의 대형 B-트리 구조를 유지해야 했기 때문입니다. 이로 인해 실시간 거래 기록을 위한 엄격한 SLA를 위반하게 되었습니다.
우리는 created_at 기준으로 범위에 따라 테이블 파티셔닝을 구현하기로 선택했습니다. 이 조합된 접근 방식은 현재 분기의 파티션으로 쿼리를 제한하여 비트맵 임계값 아래로 절대적인 페이지 수를 줄였으며, 증가된 비용 매개변수는 교차 파티션 역사 쿼리가 여전히 비트맵을 활용하여 무작위 I/O 포화 상태를 방지하도록 했습니다. 이 솔루션은 트레이딩 시스템의 쓰기 성능 제약을 존중하면서 읽기 중심의 보고 경로를 최적화했습니다.
결과: 대시보드 쿼리는 OLTP 삽입 성능을 저하시키지 않으면서 400ms로 안정화되었고, 보고 노드의 디스크 I/O 활용도는 근무 시간 동안 95%에서 30%로 감소했습니다.
effective_cache_size가 계획자의 비용 모델에서 random_page_cost 와 어떻게 상호작용하며, 실제 대규모 캐시가 있는 시스템에서 random_page_cost 를 낮추면 특정 조인 유형의 성능이 저하될 수 있는 이유는 무엇인가?
effective_cache_size는 디스크 캐싱을 위한 메모리를 정량화합니다. 높게 설정되면 계획자는 많은 페이지가 RAM에 존재한다고 가정하여 random_page_cost 설정에 관계없이 I/O 비용을 무시하게 됩니다. 만약 random_page_cost를 1.1로 공격적으로 낮추고(일반적으로 NVMe SSD에 해당) 큰 effective_cache_size를 유지하면, 최적화기는 Index Scans를 사용하는 Nested Loop 조인보다 Hash Joins를 비합리적으로 선호할 수 있습니다. 이 모델은 무작위 I/O가 저렴하고 캐시되기 때문에 내부 관계의 인덱스 프로브가 거의 무료라고 가정하지만, 대규모 내부 루프는 여전히 튜플 처리로 CPU를 포화시켜 캐시 퇴출을 유발하며, 단일 대량 해시 작업보다 벽 시계 시간을 악화시킵니다.
LIMIT 절이 있는 쿼리는 왜 PostgreSQL이 여전히 전통적인** Index Scan 이 유리함에도 불구하고 Bitmap Index Scan 을 선택할 수 있는가, 그리고 어떻게 오래된 통계가 이 오차를 영향을 미치는가?
일반 Index Scan은 인덱스가 필수 정렬을 지원하는 경우 LIMIT N 행을 가져온 후 즉시 종료할 수 있어 I/O를 최소화합니다. 그러나 만약 계획자가 조건을 만족하는 행 수를 과소 평가하면—오래된 ANALYZE 통계나 상관 열 때문—Index Scan이 매칭을 찾기 위해 과도한 수의 리프 페이지를 탐색할 것이라고 가정합니다. 따라서 I/O 비용을 분산시키기 위해 Bitmap Index Scan을 선택합니다. 비트맵은 힙에 접근하기 전에 완전히 물질화되어야 하므로, 실행자는 조기 종료가 불가능하고, 수천 개의 행을 포함한 비트맵을 구축하여 첫 10개만 남기고 모두 버리는 결과적으로 계획자의 낙관적인 추정 결과에 비해 치명적인 대기 시간이 발생하게 됩니다.