Query Planner

Chapter-chapter sebelumnya kita membaca hasil keputusan planner: pohon rencana, biaya, dan estimasi baris di EXPLAIN. Sekarang kita buka dapurnya. Query Planner adalah komponen PostgreSQL yang menerima query hasil parsing, menyusun semua rencana eksekusi yang mungkin, memberi harga setiap kandidat dengan sebuah model biaya, lalu mengeksekusi yang termurah. Kita tidak bisa berbicara langsung kepada planner — tidak ada perintah “pakai index ini” — tetapi kita bisa membaca konstanta biayanya, menghitung sendiri angka di EXPLAIN, dan mengamati bagaimana planner berpindah pilihan ketika asumsi biayanya kita ubah. Chapter ini membedah ketiga hal itu memakai dua tabel domain penjualan yang sama dengan chapter sebelumnya.

-- Dua tabel domain penjualan untuk mengamati keputusan planner.
-- pelanggan: 2.000 baris, tanpa index pada kolom kota.
-- order_penjualan: 20.000 baris, dengan dua index.
CREATE TABLE pelanggan (
    id   integer PRIMARY KEY,
    nama text NOT NULL,
    kota text NOT NULL
);

INSERT INTO pelanggan (id, nama, kota)
SELECT g,
       'Pelanggan ' || g,
       (ARRAY['Jakarta','Bandung','Surabaya','Medan'])[1 + g % 4]
FROM generate_series(1, 2000) AS g;

CREATE TABLE order_penjualan (
    id           bigint PRIMARY KEY,
    pelanggan_id integer NOT NULL REFERENCES pelanggan(id),
    tanggal      date NOT NULL,
    total        numeric(12,2) NOT NULL,
    status       text NOT NULL
);

INSERT INTO order_penjualan (id, pelanggan_id, tanggal, total, status)
SELECT g,
       1 + (g * 37) % 2000,
       DATE '2026-01-01' + (g - 1) / 112,
       ((g % 900) * 1000 + 25000)::numeric(12,2),
       (ARRAY['lunas','diproses','menunggu','batal'])[1 + g % 4]
FROM generate_series(1, 20000) AS g;

CREATE INDEX idx_order_pelanggan ON order_penjualan (pelanggan_id);
CREATE INDEX idx_order_total ON order_penjualan (total);

ANALYZE pelanggan;
ANALYZE order_penjualan;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 2000
CREATE TABLE
INSERT 0 20000
CREATE INDEX
CREATE INDEX
ANALYZE
ANALYZE

Kandidat Rencana dan Ruang Pencarian #

Untuk satu tabel, kandidatnya masih sedikit: Seq Scan, Index Scan, Index Only Scan, atau Bitmap Heap Scan lewat setiap index yang cocok. Untuk query join, ruang pencarian meledak. Dengan 4 tabel, planner harus menimbang siapa yang menjadi tabel luar dan dalam, urutan penggabungannya, dan metode join (Nested Loop, Hash Join, Merge Join) — dikali lagi pilihan scan per tabel. Jumlah kombinasi tumbuh jauh lebih cepat daripada jumlah tabel: pada belasan tabel, kemungkinan rencana mencapai miliaran.

Karena itu PostgreSQL memakai dua strategi. Di bawah geqo_threshold (default 12 tabel), planner menelusuri ruang pencarian secara sistematis dengan pemangkasan (pruning): kombinasi yang jelas mahal dibuang lebih awal. Mulai 12 tabel ke atas, algoritma genetik GEQO mengambil alih — planner membiarkan “populasi” rencana berevolusi beberapa generasi lalu mengambil yang termurah, menerima kemungkinan melewatkan rencana optimal sebagai ganti waktu perencanaan yang wajar. Satu batasan lain juga bekerja: klausa JOIN ... ON bermakna INNER JOIN boleh diurut-ulang bebas oleh planner, sedangkan LEFT/RIGHT/FULL OUTER JOIN tidak — menyusun ulang tabel pada outer join dapat mengubah hasil query, jadi planner hanya boleh memilih urutan yang menjaga maknanya.

Model Biaya dan Konstantanya #

Bagaimana planner menentukan rencana mana yang “termurah”? Semua kandidat dihargai dengan angka biaya — dan angka itu bukan milidetik, melainkan satuan arbitrer: satu baca halaman secara berurutan didefinisikan bernilai 1,0 (seq_page_cost), lalu semua operasi lain dihargai relatif terhadapnya. Konstanta-konstanta ini adalah parameter server biasa yang bisa kita baca dari view katalog pg_settings:

-- Konstanta biaya planner dapat dibaca langsung dari view katalog pg_settings.
-- Semuanya berupa parameter tingkat sesi sehingga bisa diuji tanpa menyentuh
-- konfigurasi server.
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('seq_page_cost',
               'random_page_cost',
               'cpu_tuple_cost',
               'cpu_index_tuple_cost',
               'cpu_operator_cost',
               'geqo_threshold',
               'effective_cache_size')
ORDER BY name;

Output yang Dihasilkan:

         name         | setting | unit 
----------------------+---------+------
 cpu_index_tuple_cost | 0.005   | 
 cpu_operator_cost    | 0.0025  | 
 cpu_tuple_cost       | 0.01    | 
 effective_cache_size | 524288  | 8kB
 geqo_threshold       | 12      | 
 random_page_cost     | 4       | 
 seq_page_cost        | 1       | 
(7 rows)

Mari kita bedah arti setiap baris hasilnya:

1. seq_page_cost = 1: harga satu halaman yang dibaca berurutan — patokan seluruh model biaya.
2. random_page_cost = 4: harga satu halaman yang dibaca secara acak (lompatan index). Default 4 kali baca berurutan mengasumsikan piringan mekanis (HDD), tempat seek head mahal.
3. cpu_tuple_cost = 0.01: biaya memroses satu baris (membaca, meneruskan ke node berikutnya).
4. cpu_index_tuple_cost = 0.005: biaya memroses satu entri index.
5. cpu_operator_cost = 0.0025: biaya mengevaluasi satu operator — setiap pembandingan di WHERE atau Index Cond dihargai sekali per baris.
6. geqo_threshold = 12: jumlah tabel yang memicu algoritma genetik GEQO.
7. effective_cache_size = 524288 (satuan 8kB = 4 GB): asumsi planner berapa banyak data hangat di cache — memengaruhi harga baca acak, bukan alokasi memori sungguhan.

Angka random_page_cost = 4 adalah konstanta paling sering diperdebatkan. Pada SSD dan NVMe, seek hampir tidak ada harganya, dan pada beban kerja yang cache-nya hangat, banyak administrator menurunkannya ke kisaran 1,1–2,0 agar planner berani memakai index lebih sering. Argumen lawannya: cache tidak selalu hangat, disk bersama cloud masih menampilkan latensi baca acak yang nyata, dan random_page_cost yang terlalu rendah membuat planner memilih rencana yang tampak murah di atas kertas tetapi lambat di kenyataan. Patokan pragmatisnya: ukur dulu dengan EXPLAIN ANALYZE pada query nyata sebelum mengubahnya.

Menghitung Sendiri Biaya Sebuah Seq Scan #

Angka biaya di EXPLAIN sering terasa seperti sihir sampai kita menghitung satu kali sendiri. Formula Seq Scan dengan satu kondisi filter adalah penjumlahan tiga komponen: relpages × seq_page_cost untuk membaca semua halaman, reltuples × cpu_tuple_cost untuk memroses semua baris, dan reltuples × cpu_operator_cost untuk mengevaluasi filter sekali per baris. Dua angka pertama tersimpan di katalog pg_class hasil pemindaian ANALYZE:

-- pg_class menyimpan ukuran hasil pemindaian ANALYZE:
-- relpages = jumlah halaman tabel, reltuples = perkiraan baris hidup.
-- Tiga kolom berikutnya menghitung komponen biaya Seq Scan dengan
-- konstanta default: seq_page_cost=1, cpu_tuple_cost=0.01, cpu_operator_cost=0.0025.
SELECT relpages                                AS halaman,
       reltuples::bigint                       AS baris,
       round(relpages * 1.0, 2)                AS baca_halaman,
       round(reltuples::numeric * 0.01, 2)     AS proses_baris,
       round(reltuples::numeric * 0.0025, 2)   AS evaluasi_filter,
       round(relpages * 1.0
             + reltuples::numeric * 0.01
             + reltuples::numeric * 0.0025, 2) AS total_scan
FROM pg_class
WHERE relname = 'order_penjualan';

Output yang Dihasilkan:

 halaman | baris | baca_halaman | proses_baris | evaluasi_filter | total_scan 
---------+-------+--------------+--------------+-----------------+------------
     148 | 20000 |       148.00 |       200.00 |           50.00 |     398.00
(1 row)

Tabel order_penjualan tercatat 148 halaman dan 20.000 baris. Aritmetikanya: 148 × 1 = 148 untuk membaca seluruh halaman, 20.000 × 0,01 = 200 untuk memroses baris, dan 20.000 × 0,0025 = 50 untuk mengevaluasi satu kondisi filter — total 398,00. Sekarang kita minta rencana planner untuk query yang persis berbentuk formula itu: satu filter total < 700000 yang menyaring sekitar 75% baris:

-- Rencana pilihan planner untuk query yang membaca 75% baris tabel.
-- Angka biaya dapat sedikit berbeda di mesin Anda.
EXPLAIN
SELECT count(status)
FROM order_penjualan
WHERE total < 700000;

Output yang Dihasilkan:

                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Aggregate  (cost=435.61..435.62 rows=1 width=8)
   ->  Seq Scan on order_penjualan  (cost=0.00..398.00 rows=15044 width=7)
         Filter: (total < '700000'::numeric)
(3 rows)

Node Seq Scan menampilkan cost=0.00..398.00 — persis angka total_scan hasil hitungan kita. Biaya startup nol berarti baris pertama bisa keluar seketika; biaya total 398,00 adalah harga membaca seluruh tabel sampai selesai. Angka rows=15044 di baris yang sama datang dari tempat lain: statistik kolom total yang dianalisis ANALYZE memberi tahu planner sekitar 75% baris lolos filter. Biaya datang dari model biaya; jumlah baris datang dari statistik. Dua sumber berbeda — dan kualitas rencana bergantung pada keduanya.

Melihat Kandidat yang Tidak Dipilih #

Rencana di EXPLAIN hanyalah pemenangnya. Bagaimana dengan yang kalah? Parameter enable_seqscan, enable_bitmapscan, dan kawan-kawannya tidak benar-benar menghapus opsi dari daftar kandidat — mereka hanya menambahkan denda 1,0×10¹⁰ pada biayanya sehingga planner memilih kandidat termurah berikutnya. Denda itu bahkan terlihat di EXPLAIN bila tidak ada kandidat lain:

-- Denda 1.0e10: mematikan enable_seqscan tidak menghapus seq scan dari
-- daftar kandidat, hanya menjadikannya tampak sangat mahal.
-- (jit dimatikan agar bagian JIT tidak ikut tercetak pada rencana berdenda.)
SET jit = off;
SET enable_seqscan = off;

-- pelanggan tidak punya index pada kolom kota: seq scan tetap terpaksa,
-- dan dendanya terlihat langsung pada angka biayanya.
EXPLAIN
SELECT count(*)
FROM pelanggan
WHERE kota = 'Jakarta';

-- order_penjualan punya index pada total: dengan seq scan terdenda,
-- bitmap scan menjadi kandidat termurah berikutnya.
EXPLAIN
SELECT count(status)
FROM order_penjualan
WHERE total < 700000;

-- Larang juga bitmap scan: tersisa index scan murni.
SET enable_bitmapscan = off;

EXPLAIN
SELECT count(status)
FROM order_penjualan
WHERE total < 700000;

Output yang Dihasilkan:

SET
SET
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Aggregate  (cost=10000000041.25..10000000041.26 rows=1 width=8)
   ->  Seq Scan on pelanggan  (cost=10000000000.00..10000000040.00 rows=500 width=0)
         Filter: (kota = 'Jakarta'::text)
(3 rows)

                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Aggregate  (cost=662.54..662.55 rows=1 width=8)
   ->  Bitmap Heap Scan on order_penjualan  (cost=288.88..624.93 rows=15044 width=7)
         Recheck Cond: (total < '700000'::numeric)
         ->  Bitmap Index Scan on idx_order_total  (cost=0.00..285.12 rows=15044 width=0)
               Index Cond: (total < '700000'::numeric)
(5 rows)

SET
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Aggregate  (cost=1064.93..1064.94 rows=1 width=8)
   ->  Index Scan using idx_order_total on order_penjualan  (cost=0.29..1027.32 rows=15044 width=7)
         Index Cond: (total < '700000'::numeric)
(3 rows)

Tiga rencana untuk query yang sama kini terbanding lurus. Pada pelanggan, denda tampil telanjang: cost=10000000000.00..10000000040.00 — biaya asli 40,00 ditambah denda sepuluh miliar di posisi startup. Pada order_penjualan, urutan kandidat setelah seq scan dilarang:

Planner sudah memberi harga ketiganya sebelum memilih Seq Scan — rencana 398 benar-benar yang termurah menurut model biaya, bukan kebetulan. Memaksa kandidat yang kalah lewat parameter enable_* hanya menghasilkan rencana 1,6 sampai 2,6 kali lebih mahal; itulah mengapa parameter ini berstatus alat diagnosis, bukan alat tuning permanen.

Eksperimen Model Biaya di Tingkat Sesi #

Kalau biaya dihitung dari konstanta, mengubah konstanta harus mengubah keputusan. Karena semua konstanta biaya berupa parameter tingkat sesi, kita bisa bereksperimen tanpa menyentuh server. Query berikut menyaring sekitar 20% baris lewat rentang harga — pada default random_page_cost = 4 planner memilih Bitmap Heap Scan:

-- Query yang sama, data yang sama, statistik yang sama;
-- hanya asumsi harga baca acak (random_page_cost) yang diubah.

-- Default 4.0: satu baca acak dianggap semahal empat baca berurutan.
EXPLAIN
SELECT count(status)
FROM order_penjualan
WHERE total BETWEEN 25000 AND 200000;

-- Eksperimen tingkat sesi: anggap baca acak hampir semurah baca berurutan
-- (asumsi yang lazim untuk SSD dengan cache yang hangat).
SET random_page_cost = 1.1;

EXPLAIN
SELECT count(status)
FROM order_penjualan
WHERE total BETWEEN 25000 AND 200000;

Output yang Dihasilkan:

                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Aggregate  (cost=308.54..308.55 rows=1 width=8)
   ->  Bitmap Heap Scan on order_penjualan  (cost=89.75..298.42 rows=4045 width=7)
         Recheck Cond: ((total >= '25000'::numeric) AND (total <= '200000'::numeric))
         ->  Bitmap Index Scan on idx_order_total  (cost=0.00..88.74 rows=4045 width=0)
               Index Cond: ((total >= '25000'::numeric) AND (total <= '200000'::numeric))
(5 rows)

SET
                                            QUERY PLAN                                             
--------------------------------------------------------------------------------------------------
 Aggregate  (cost=267.23..267.24 rows=1 width=8)
   ->  Index Scan using idx_order_total on order_penjualan  (cost=0.29..257.12 rows=4045 width=7)
         Index Cond: ((total >= '25000'::numeric) AND (total <= '200000'::numeric))
(3 rows)

Data, index, dan statistik tidak berubah sedikit pun — hanya asumsi harga baca acak. Dengan random_page_cost = 1.1, lompatan halaman lewat index menjadi murah, Index Scan murni turun ke 257, dan planner berpindah pilihan dari Bitmap Heap Scan ke Index Scan. Inilah inti cara kerja planner: keputusan hanyalah fungsi dari (1) apa yang tersedia — index dan data, (2) apa yang diketahui — statistik, dan (3) bagaimana harga diberikan — konstanta biaya. Mengubah salah satu mengubah rencana.

Tanpa Hint: Apa yang Bisa Anda Kendalikan #

Berbeda dengan Oracle yang punya /*+ INDEX(...) */ atau MySQL yang punya FORCE INDEX, PostgreSQL sengaja tidak menyediakan hint rencana. Filosofinya: kalau planner memilih rencana buruk, masalahnya hampir selalu di masukan — statistik yang menipu, index yang kurang, atau query yang ditulis secara membengkalkan — dan memaksa rencana hanya menyembunyikan gejalanya. Ekstensi pihak ketiga pg_hint_plan memang menyediakan sintaks hint gaya Oracle bagi yang benar-benar membutuhkannya, tetapi jalur utama yang didukung komunitas adalah memperbaiki masukan. Alat kendali Anda yang sebenarnya:

Dari empat kendali itu, statistik menempati posisi istimewa: angka rows=15044 pada seluruh rencana di chapter ini lahir dari statistik yang dijaga ANALYZE. Model biaya secanggih apa pun tidak bisa menolong kalau perkiraan barisnya bohong. Chapter berikutnya membuka isi statistik itu sendiri — cara ANALYZE mengambil sampel, cara membaca pg_stats, dan cara statistik ekstensif menyelamatkan estimasi pada kolom yang saling berkorelasi.

Referensi #

• Planner/Optimizer
• Query Planning (Konfigurasi)
• Using EXPLAIN

-- Denda 1.0e10: mematikan enable_seqscan tidak menghapus seq scan dari
-- daftar kandidat, hanya menjadikannya tampak sangat mahal.
-- (jit dimatikan agar bagian JIT tidak ikut tercetak pada rencana berdenda.)
SET jit = off;
SET enable_seqscan = off;

-- pelanggan tidak punya index pada kolom kota: seq scan tetap terpaksa,
-- dan dendanya terlihat langsung pada angka biayanya.
EXPLAIN
SELECT count(*)
FROM pelanggan
WHERE kota = 'Jakarta';

-- order_penjualan punya index pada total: dengan seq scan terdenda,
-- bitmap scan menjadi kandidat termurah berikutnya.
EXPLAIN
SELECT count(status)
FROM order_penjualan
WHERE total < 700000;

-- Larang juga bitmap scan: tersisa index scan murni.
SET enable_bitmapscan = off;

EXPLAIN
SELECT count(status)
FROM order_penjualan
WHERE total < 700000;

-- Dua tabel domain penjualan untuk mengamati keputusan planner.
-- pelanggan: 2.000 baris, tanpa index pada kolom kota.
-- order_penjualan: 20.000 baris, dengan dua index.
CREATE TABLE pelanggan (
    id   integer PRIMARY KEY,
    nama text NOT NULL,
    kota text NOT NULL
);

INSERT INTO pelanggan (id, nama, kota)
SELECT g,
       'Pelanggan ' || g,
       (ARRAY['Jakarta','Bandung','Surabaya','Medan'])[1 + g % 4]
FROM generate_series(1, 2000) AS g;

CREATE TABLE order_penjualan (
    id           bigint PRIMARY KEY,
    pelanggan_id integer NOT NULL REFERENCES pelanggan(id),
    tanggal      date NOT NULL,
    total        numeric(12,2) NOT NULL,
    status       text NOT NULL
);

INSERT INTO order_penjualan (id, pelanggan_id, tanggal, total, status)
SELECT g,
       1 + (g * 37) % 2000,
       DATE '2026-01-01' + (g - 1) / 112,
       ((g % 900) * 1000 + 25000)::numeric(12,2),
       (ARRAY['lunas','diproses','menunggu','batal'])[1 + g % 4]
FROM generate_series(1, 20000) AS g;

CREATE INDEX idx_order_pelanggan ON order_penjualan (pelanggan_id);
CREATE INDEX idx_order_total ON order_penjualan (total);

ANALYZE pelanggan;
ANALYZE order_penjualan;

-- Query yang sama, data yang sama, statistik yang sama;
-- hanya asumsi harga baca acak (random_page_cost) yang diubah.

-- Default 4.0: satu baca acak dianggap semahal empat baca berurutan.
EXPLAIN
SELECT count(status)
FROM order_penjualan
WHERE total BETWEEN 25000 AND 200000;

-- Eksperimen tingkat sesi: anggap baca acak hampir semurah baca berurutan
-- (asumsi yang lazim untuk SSD dengan cache yang hangat).
SET random_page_cost = 1.1;

EXPLAIN
SELECT count(status)
FROM order_penjualan
WHERE total BETWEEN 25000 AND 200000;

-- Konstanta biaya planner dapat dibaca langsung dari view katalog pg_settings.
-- Semuanya berupa parameter tingkat sesi sehingga bisa diuji tanpa menyentuh
-- konfigurasi server.
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('seq_page_cost',
               'random_page_cost',
               'cpu_tuple_cost',
               'cpu_index_tuple_cost',
               'cpu_operator_cost',
               'geqo_threshold',
               'effective_cache_size')
ORDER BY name;

-- Rencana pilihan planner untuk query yang membaca 75% baris tabel.
-- Angka biaya dapat sedikit berbeda di mesin Anda.
EXPLAIN
SELECT count(status)
FROM order_penjualan
WHERE total < 700000;

-- pg_class menyimpan ukuran hasil pemindaian ANALYZE:
-- relpages = jumlah halaman tabel, reltuples = perkiraan baris hidup.
-- Tiga kolom berikutnya menghitung komponen biaya Seq Scan dengan
-- konstanta default: seq_page_cost=1, cpu_tuple_cost=0.01, cpu_operator_cost=0.0025.
SELECT relpages                                AS halaman,
       reltuples::bigint                       AS baris,
       round(relpages * 1.0, 2)                AS baca_halaman,
       round(reltuples::numeric * 0.01, 2)     AS proses_baris,
       round(reltuples::numeric * 0.0025, 2)   AS evaluasi_filter,
       round(relpages * 1.0
             + reltuples::numeric * 0.01
             + reltuples::numeric * 0.0025, 2) AS total_scan
FROM pg_class
WHERE relname = 'order_penjualan';