Postgres EXPLAIN ANALYZE tuning step-by-step

Query laporan penjualan bulanan 8 detik di Postgres? EXPLAIN ANALYZE adalah X-ray query — bisa lihat di node mana waktunya habis. Snippet ini step-by-step optimasi query order Tokopedia dari Seq Scan jadi Index Only Scan.

Kode

-- ==========================================
-- Setup: schema dan data sample
-- ==========================================
CREATE TABLE pesanan (
    id          BIGSERIAL PRIMARY KEY,
    user_id     INT NOT NULL,
    status      TEXT NOT NULL,         -- 'paid', 'pending', 'canceled'
    total       BIGINT NOT NULL,        -- dalam rupiah
    kota_kirim  TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Insert 5 juta row test data
INSERT INTO pesanan (user_id, status, total, kota_kirim, created_at)
SELECT
    (random() * 100000)::INT,
    (ARRAY['paid','pending','canceled'])[floor(random()*3)+1],
    (random() * 5000000)::BIGINT,
    (ARRAY['Jakarta','Bandung','Surabaya','Medan','Tangerang'])[floor(random()*5)+1],
    now() - (random() * interval '365 days')
FROM generate_series(1, 5000000);

ANALYZE pesanan;

-- ==========================================
-- Step 1: query awal — SLOW
-- ==========================================
-- Hitung total penjualan paid di Jakarta bulan lalu
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT COUNT(*) AS jumlah, SUM(total) AS omzet
FROM pesanan
WHERE status = 'paid'
  AND kota_kirim = 'Jakarta'
  AND created_at >= '2026-05-01'
  AND created_at < '2026-06-01';

-- Output (rangkuman):
--  Gather (cost=104872..104873 rows=1 width=16) (actual time=7892..7895 ms)
--   ->  Parallel Seq Scan on pesanan
--         Filter: (status = 'paid' AND kota_kirim = 'Jakarta' AND ...)
--         Rows Removed by Filter: 1,664,283 per worker
--  Execution Time: 7894 ms

-- DIAGNOSIS: Seq Scan + filter buang 99% row → indeks tidak cocok

-- ==========================================
-- Step 2: bikin indeks naif — masih lambat
-- ==========================================
CREATE INDEX idx_pesanan_status ON pesanan (status);
ANALYZE pesanan;

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*), SUM(total)
FROM pesanan
WHERE status = 'paid' AND kota_kirim = 'Jakarta'
  AND created_at >= '2026-05-01' AND created_at < '2026-06-01';

-- Output (rangkuman):
--  Bitmap Heap Scan on pesanan (cost=44820..98302 rows=27340 width=8)
--    Recheck Cond: status = 'paid'
--    Filter: kota_kirim = 'Jakarta' AND created_at >= ... AND created_at < ...
--    Heap Blocks: exact=78920
--  Execution Time: 2842 ms

-- DIAGNOSIS: index 'status' tidak selective (33% paid). Filter berikut tetap mahal.

DROP INDEX idx_pesanan_status;

-- ==========================================
-- Step 3: indeks composite — CEPAT
-- ==========================================
-- Order kolom dalam composite penting: equality dulu, range terakhir
CREATE INDEX idx_pesanan_status_kota_created
    ON pesanan (status, kota_kirim, created_at);
ANALYZE pesanan;

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*), SUM(total)
FROM pesanan
WHERE status = 'paid' AND kota_kirim = 'Jakarta'
  AND created_at >= '2026-05-01' AND created_at < '2026-06-01';

-- Output:
--  Aggregate (cost=12042..12043 rows=1 width=16) (actual time=31..31 ms)
--   ->  Bitmap Heap Scan on pesanan
--         Recheck Cond: status='paid' AND kota_kirim='Jakarta' AND created_at >= ... < ...
--         Heap Blocks: exact=27800
--         ->  Bitmap Index Scan on idx_pesanan_status_kota_created
--               Index Cond: (status='paid' AND kota_kirim='Jakarta' AND created_at >= ... < ...)
--  Execution Time: 33 ms

-- DIAGNOSIS: dari 7894 ms → 33 ms = 240x lebih cepat

-- ==========================================
-- Step 4: Index Only Scan untuk COUNT — lebih cepat lagi
-- ==========================================
-- INCLUDE non-key column 'total' supaya bisa Index Only Scan
DROP INDEX idx_pesanan_status_kota_created;
CREATE INDEX idx_pesanan_status_kota_created_v2
    ON pesanan (status, kota_kirim, created_at)
    INCLUDE (total);

-- Pastikan VACUUM up-to-date supaya visibility map ada
VACUUM pesanan;
ANALYZE pesanan;

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*), SUM(total)
FROM pesanan
WHERE status = 'paid' AND kota_kirim = 'Jakarta'
  AND created_at >= '2026-05-01' AND created_at < '2026-06-01';

-- Output:
--  Aggregate (actual time=8.2..8.2 ms)
--   ->  Index Only Scan using idx_pesanan_status_kota_created_v2
--         Index Cond: (status='paid' AND kota_kirim='Jakarta' AND created_at >= ... < ...)
--         Heap Fetches: 0
--  Execution Time: 8 ms

-- 8 ms — heap tidak di-touch sama sekali

Pemakaian

-- Pattern umum: equality column → range column dalam composite index
-- Query:   WHERE a = $1 AND b = $2 AND c BETWEEN $3 AND $4
-- Index:   (a, b, c)        -- BENAR
-- Index:   (c, a, b)        -- SALAH (range first)
-- Index:   (a, b, c, d)     -- OK kalau ada query d, tapi rugi storage

-- Partial index untuk skenario dengan filter sering sama
CREATE INDEX idx_pesanan_paid_recent
    ON pesanan (kota_kirim, created_at)
    WHERE status = 'paid';

-- Query yang sama tanpa 'status' filter di WHERE
EXPLAIN ANALYZE
SELECT COUNT(*) FROM pesanan
WHERE kota_kirim = 'Jakarta'
  AND created_at >= '2026-05-01' AND created_at < '2026-06-01'
  AND status = 'paid';
-- Index lebih kecil → fit cache → query lebih cepat

-- Cek index unused supaya gak buang storage
SELECT s.schemaname, s.relname AS tabel, s.indexrelname AS index_name,
       s.idx_scan, pg_size_pretty(pg_relation_size(s.indexrelid)) AS ukuran
FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid
WHERE NOT i.indisunique
ORDER BY s.idx_scan ASC, pg_relation_size(s.indexrelid) DESC
LIMIT 20;

Kapan dipakai

• Query report dashboard yang sering di-run.
• API endpoint yang slow di production logs.
• Migrasi dari MySQL — planner Postgres beda perilakunya.
• Setiap kali kena complaint user “kenapa lambat?”.

Catatan

• EXPLAIN ANALYZE benar-benar eksekusi — hati-hati di production untuk DELETE/UPDATE. Wrap di BEGIN; … ROLLBACK; kalau butuh.
• (ANALYZE, BUFFERS) — BUFFERS show shared hits vs reads, indikasi cache pressure.
• actual rows vs estimated — kalau estimasi off banget (10x), jalankan ANALYZE table. Statistik usang bikin planner ngaco.
• work_mem — kalau lihat “Disk: 38000kB” di Sort, naikkan work_mem session. Hash Join butuh memory besar.
• Multi-column index order — pakai aturan: equality > range > IS NULL. Kolom yang sering di-WHERE equality taruh di depan.
• INCLUDE column — bukan bagian index key, tapi disimpan di leaf node. Bikin Index Only Scan possible tanpa heap fetch.

Index banyak gak gratis — slow down INSERT/UPDATE, ambil disk, dan butuh maintenance VACUUM. Drop yang gak dipakai. Cek pg_stat_user_indexes.idx_scan = 0 setelah beberapa minggu.