7.4 파티션 가지치기

-- plan-time: WHERE의 값이 상수 → 즉시 가지치기
EXPLAIN SELECT * FROM events WHERE created_at >= '2026-05-01';
-- 결과: events_2026_05만 스캔

-- execution-time: 파라미터가 prepare 시점에 모름
PREPARE q AS SELECT * FROM events WHERE created_at >= $1;
EXPLAIN EXECUTE q ('2026-05-01');
-- 결과: "Subplans Removed: N" 형태로 실행 시점에 가지치기

 Append  (cost=...)
   Subplans Removed: 11             -- 12개 중 11개 가지치기
   ->  Seq Scan on events_2026_05  -- 남은 1개만 스캔

EXPLAIN SELECT * FROM events
 WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01';

 Seq Scan on events_2026_05    -- 단일 파티션만

EXPLAIN SELECT * FROM orders WHERE country IN ('KR', 'JP');

 Append
   ->  Seq Scan on orders_kr
   ->  Seq Scan on orders_jp

EXPLAIN SELECT * FROM users WHERE id = 42;
 Seq Scan on users_p3    -- 해시 결과로 한 파티션만

SET enable_partitionwise_join = on;     -- PG 11~13 기본 off
-- PG 14+: 기본 on

 Append
   ->  Hash Join
         ->  Seq Scan on a_2026_05
         ->  Hash on b_2026_05
   ->  Hash Join
         ->  Seq Scan on a_2026_06
         ->  Hash on b_2026_06

SET enable_partitionwise_aggregate = on;

SELECT user_id, count(*)
  FROM events
 WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01'
 GROUP BY user_id;

-- 한 파티션 안에서 집계 → 결과 작음

WHERE created_at > now() - interval '1 day'

since = datetime.now() - timedelta(days=1)
cur.execute("SELECT ... WHERE created_at > %s", (since,))
-- 이 식은 prepared statement의 $1 → execution-time pruning

WHERE date_trunc('day', created_at) = '2026-05-15'  -- 모든 파티션 스캔
WHERE created_at::date = '2026-05-15'                -- 같음
WHERE EXTRACT(month FROM created_at) = 5             -- 모든 파티션 스캔

WHERE created_at >= '2026-05-15' AND created_at < '2026-05-16'  -- 가지치기 OK

WHERE created_at IN (SELECT max(t) FROM ...)

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
;