Mengoptimalkan Join

Query join yang lambat hampir selalu disalahkan pada jumlah tabelnya — “lima join, ya tentu lambat” — padahal penyebab yang jauh lebih umum adalah pola aksesnya: kolom kunci join yang tidak diindeks, agregasi yang dilakukan setelah penggandaan baris, atau fungsi yang membungkus kolom join sehingga index tidak bisa dipakai. Semua penyakit itu tidak terlihat dari hasil query karena hasilnya tetap benar; hanya rencana eksekusinya yang diam-diam membayar mahal. Chapter ini membedah ketiga pola itu satu per satu pada domain penjualan Toko Maju Jaya, memperbaikinya, lalu membuktikan perbaikannya lewat EXPLAIN sebelum dan sesudah.

Kolom Join Tanpa Index: Foreign Key yang Terlupakan #

PostgreSQL tidak membuat index otomatis pada kolom foreign key — berbeda dengan beberapa database lain. Constraint REFERENCES pelanggan(id) menjaga keutuhan data, tetapi sama sekali tidak membantu query yang mencari pesanan milik satu pelanggan. File berikut membangun domain latihan dalam kondisi alami itu: kolom pesanan.pelanggan_id adalah foreign key tanpa index:

sql
buat-tabel.sql
-- Domain latihan: pelanggan -> pesanan -> detail_pesanan.
-- Kolom foreign key pesanan.pelanggan_id sengaja TIDAK diindeks
-- dulu: itulah kondisi awal yang mau kita perbaiki.
CREATE TABLE pelanggan (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text NOT NULL,
    kota text NOT NULL
);

INSERT INTO pelanggan (nama, kota)
SELECT 'Pelanggan ' || lpad(g::text, 3, '0'),
       (ARRAY['Jakarta','Bandung','Surabaya'])[1 + g % 3]
FROM generate_series(1, 200) AS g;

CREATE TABLE pesanan (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pelanggan_id integer NOT NULL REFERENCES pelanggan(id),
    total numeric(12,2) NOT NULL,
    tanggal date NOT NULL
);

INSERT INTO pesanan (pelanggan_id, total, tanggal)
SELECT 1 + (g * 7919) % 200,
       50000 + (g % 100) * 1250,
       DATE '2026-01-01' + (g % 180)
FROM generate_series(1, 20000) AS g;

CREATE TABLE detail_pesanan (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pesanan_id integer NOT NULL REFERENCES pesanan(id),
    barang text NOT NULL,
    qty integer NOT NULL
);

INSERT INTO detail_pesanan (pesanan_id, barang, qty)
SELECT g, (ARRAY['Kopi','Teh'])[1 + g % 2], 1 + g % 3
FROM generate_series(1, 20000) AS g
CROSS JOIN generate_series(1, 2) AS d;

ANALYZE pelanggan;
ANALYZE pesanan;
ANALYZE detail_pesanan;

SELECT count(*) AS jumlah_pelanggan FROM pelanggan;
SELECT count(*) AS jumlah_pesanan FROM pesanan;
SELECT count(*) AS jumlah_detail FROM detail_pesanan;

Output yang Dihasilkan:

text
CREATE TABLE
INSERT 0 200
CREATE TABLE
INSERT 0 20000
CREATE TABLE
INSERT 0 40000
ANALYZE
ANALYZE
ANALYZE
 jumlah_pelanggan 
------------------
              200
(1 row)

 jumlah_pesanan 
----------------
          20000
(1 row)

 jumlah_detail 
---------------
         40000
(1 row)

Skenario penyakitnya klasik: halaman “riwayat pesanan pelanggan” yang mengambil semua pesanan milik satu pelanggan. Rencana eksekusinya sebelum perbaikan:

sql
explain-sebelum-index.sql
-- Rencana SEBELUM ada index pada kolom join:
-- pesanan discan penuh 20.000 baris untuk menemukan
-- sekitar 100 baris milik satu pelanggan.
-- Angka cost bervariasi tipis antar eksekusi karena sampling ANALYZE.
EXPLAIN
SELECT p.nama, o.total
FROM pelanggan p JOIN pesanan o ON o.pelanggan_id = p.id
WHERE p.id = 42;

Output yang Dihasilkan:

text
                             QUERY PLAN                             
--------------------------------------------------------------------
 Nested Loop  (cost=0.00..383.50 rows=100 width=20)
   ->  Seq Scan on pelanggan p  (cost=0.00..4.50 rows=1 width=18)
         Filter: (id = 42)
   ->  Seq Scan on pesanan o  (cost=0.00..378.00 rows=100 width=10)
         Filter: (pelanggan_id = 42)
(5 rows)

Baris Seq Scan on pesanan itulah tagihannya: untuk menemukan sekitar 100 baris milik pelanggan 42, PostgreSQL membaca seluruh 20.000 baris tabel pesanan lalu membuang sisanya. Pada 20.000 baris latihan ini sakitnya baru terasa sebagai cost 383; pada tabel produksi berisi puluhan juta baris, pola yang sama berarti ratusan milidetik untuk setiap kunjungan halaman pelanggan. Angka cost pada output bervariasi tipis antar mesin karena statistik ANALYZE diambil dari sampling acak, tetapi bentuk rencananya — Seq Scan pada sisi banyak — stabil.

Satu Index pada Kolom Kunci, Rencana Berubah Total #

Perbaikannya satu baris:

sql
buat-index.sql
-- Index pada kolom foreign key: pencarian paling sering
-- dilupakan karena PostgreSQL tidak membuatnya otomatis.
CREATE INDEX idx_pesanan_pelanggan ON pesanan (pelanggan_id);

ANALYZE pesanan;

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'pesanan'
ORDER BY indexname;

Output yang Dihasilkan:

text
CREATE INDEX
ANALYZE
       indexname       |                                    indexdef                                     
-----------------------+---------------------------------------------------------------------------------
 idx_pesanan_pelanggan | CREATE INDEX idx_pesanan_pelanggan ON public.pesanan USING btree (pelanggan_id)
 pesanan_pkey          | CREATE UNIQUE INDEX pesanan_pkey ON public.pesanan USING btree (id)
(2 rows)

Query yang sama, rencana yang baru:

sql
explain-setelah-index.sql
-- Rencana SETELAH index pada kolom join:
-- pesanan kini dicapai lewat index, bukan scan penuh.
-- Angka cost bervariasi tipis antar eksekusi karena sampling ANALYZE.
EXPLAIN
SELECT p.nama, o.total
FROM pelanggan p JOIN pesanan o ON o.pelanggan_id = p.id
WHERE p.id = 42;

-- Urutan tabel di INNER JOIN bebas ditulis bagaimana pun:
-- planner menyusun ulang urutan secara internal, dan kedua
-- penulisan berikut menghasilkan rencana yang identik.
EXPLAIN
SELECT p.nama, o.total
FROM pesanan o JOIN pelanggan p ON o.pelanggan_id = p.id
WHERE p.id = 42;

Output yang Dihasilkan:

text
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Nested Loop  (cost=5.06..137.81 rows=100 width=20)
   ->  Seq Scan on pelanggan p  (cost=0.00..4.50 rows=1 width=18)
         Filter: (id = 42)
   ->  Bitmap Heap Scan on pesanan o  (cost=5.06..132.31 rows=100 width=10)
         Recheck Cond: (pelanggan_id = 42)
         ->  Bitmap Index Scan on idx_pesanan_pelanggan  (cost=0.00..5.04 rows=100 width=0)
               Index Cond: (pelanggan_id = 42)
(7 rows)

                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Nested Loop  (cost=5.06..137.81 rows=100 width=20)
   ->  Seq Scan on pelanggan p  (cost=0.00..4.50 rows=1 width=18)
         Filter: (id = 42)
   ->  Bitmap Heap Scan on pesanan o  (cost=5.06..132.31 rows=100 width=10)
         Recheck Cond: (pelanggan_id = 42)
         ->  Bitmap Index Scan on idx_pesanan_pelanggan  (cost=0.00..5.04 rows=100 width=0)
               Index Cond: (pelanggan_id = 42)
(7 rows)

Rencana berubah dari Seq Scan on pesanan menjadi Bitmap Index Scan using idx_pesanan_pelanggan: 20.000 baris yang dibaca penuh turun menjadi kunjungan langsung ke ±100 entri index, dan total cost turun dari 383 menjadi 138. Perhatikan pula bagian kedua file: query yang sama ditulis dengan urutan tabel terbalik (pesanan duluan), dan rencananya identik baris demi baris. Untuk INNER JOIN, urutan penulisan hanyalah urusan keterbacaan — planner bebas menyusun ulang, termasuk menurunkan filter WHERE p.id = 42 ke tabel yang paling dulu menyaring (prinsip filter early). Subquery dan derived table yang menaruh filter lebih dulu karenanya berfungsi sebagai alat bantu manusia membaca, bukan optimasi bagi planner.

Agregasi Sebelum Join, Bukan Sesudah #

Pola lambat kedua tidak berhubungan dengan scan, melainkan dengan aritmetika: join 1:N menggandakan baris sisi satu, sehingga agregat kolom sisi itu ikut tergandakan. Kasusnya “laporan ringkas per pelanggan” yang butuh dua angka sekaligus — total pendapatan dari tabel pesanan dan jumlah barang dari tabel detail_pesanan:

sql
agregasi-join.sql
-- SALAH: agregat SETELAH join 1:N. Setiap pesanan tampil dua kali
-- (satu per baris detail), sehingga sum(total) membengkak dua lipat.
SELECT p.nama, sum(o.total) AS pendapatan_membengkak, sum(d.qty) AS jumlah_barang
FROM pelanggan p
JOIN pesanan o ON o.pelanggan_id = p.id
JOIN detail_pesanan d ON d.pesanan_id = o.id
GROUP BY p.id
ORDER BY p.id
LIMIT 3;

-- BENAR: pre-agregasi di derived table, join hanya menyambungkan
-- hasil yang sudah ringkas -- satu baris per pelanggan.
SELECT p.nama, b.pendapatan, d.jumlah_barang
FROM pelanggan p
JOIN (
    SELECT pelanggan_id, sum(total) AS pendapatan
    FROM pesanan
    GROUP BY pelanggan_id
) b ON b.pelanggan_id = p.id
JOIN (
    SELECT o.pelanggan_id, sum(d.qty) AS jumlah_barang
    FROM pesanan o
    JOIN detail_pesanan d ON d.pesanan_id = o.id
    GROUP BY o.pelanggan_id
) d ON d.pelanggan_id = p.id
ORDER BY p.id
LIMIT 3;

Output yang Dihasilkan:

text
     nama      | pendapatan_membengkak | jumlah_barang 
---------------+-----------------------+---------------
 Pelanggan 001 |           10000000.00 |           402
 Pelanggan 002 |           29750000.00 |           400
 Pelanggan 003 |           24500000.00 |           402
(3 rows)

     nama      | pendapatan  | jumlah_barang 
---------------+-------------+---------------
 Pelanggan 001 |  5000000.00 |           402
 Pelanggan 002 | 14875000.00 |           400
 Pelanggan 003 | 12250000.00 |           402
(3 rows)

Bandingkan kolom pendapatan pada dua hasil itu: Pelanggan 001 tercatat 10000000.00 pada query pertama dan 5000000.00 pada query kedua — tepat dua lipat, karena setiap pesanan muncul dua kali (satu per baris detailnya) sebelum sum() dijalankan. Kolom jumlah_barang kebetulan tidak rusak karena arah penggandaannya kebalikan, dan itulah yang membuat bug semacam ini licik: sebagian angka terlihat wajar. Pola pembersihnya dibahas mendalam di chapter Menghindari Duplikasi Hasil Join — intinya sama: agregasikan di derived table dulu, join belakangan, sehingga join menyambungkan 200 baris ringkas, bukan 40.000 baris bengkak.

Fungsi pada Kolom Join: Index yang Terkunci #

Pola terakhir: memanggil fungsi di kolom join. Penggabungan data lama sering memakai lower() di kedua sisi untuk menetralkan kapitalisasi email — benar secara hasil, tetapi index biasa pada kolom email tidak bisa melayani kondisi lower(email) = ... karena yang dicari bukan lagi nilai kolom aslinya:

sql
join-fungsi.sql
-- Data kontak dengan kapitalisasi email yang berantakan,
-- khas hasil import dari sistem lama.
CREATE TABLE kontak (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text NOT NULL,
    email text NOT NULL
);

INSERT INTO kontak (nama, email)
SELECT 'Kontak ' || lpad(g::text, 3, '0'),
       'Kontak' || lpad(g::text, 3, '0') || '@Example.com'
FROM generate_series(1, 200) AS g;

CREATE TABLE kontak_lama (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama_lama text NOT NULL,
    email_lama text NOT NULL
);

INSERT INTO kontak_lama (nama_lama, email_lama)
SELECT 'Nama Lama ' || lpad(g::text, 3, '0'),
       'kontak' || lpad(g::text, 3, '0') || '@example.COM'
FROM generate_series(1, 50) AS g;

ANALYZE kontak;
ANALYZE kontak_lama;

-- Join dengan fungsi di kedua kolom: hasilnya benar,
-- tetapi index biasa pada email tidak bisa ikut bekerja.
SELECT count(*) AS pasangan_email
FROM kontak k
JOIN kontak_lama kl ON lower(k.email) = lower(kl.email_lama);

-- Perbaikannya: expression index atas hasil fungsi lower(email).
CREATE INDEX idx_kontak_email_bawah ON kontak (lower(email));

ANALYZE kontak;

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'kontak'
ORDER BY indexname;

Output yang Dihasilkan:

text
CREATE TABLE
INSERT 0 200
CREATE TABLE
INSERT 0 50
ANALYZE
ANALYZE
 pasangan_email 
----------------
             50
(1 row)

CREATE INDEX
ANALYZE
       indexname        |                                    indexdef                                     
------------------------+---------------------------------------------------------------------------------
 idx_kontak_email_bawah | CREATE INDEX idx_kontak_email_bawah ON public.kontak USING btree (lower(email))
 kontak_pkey            | CREATE UNIQUE INDEX kontak_pkey ON public.kontak USING btree (id)
(2 rows)

Join menemukan 50 pasangan email — hasilnya benar — dan pg_indexes memperlihatkan perbaikannya: idx_kontak_email_bawah didefinisikan atas lower(email), bukan atas email. Ekspresi yang diindeks harus sama persis dengan ekspresi di query; syarat dan batasannya (misalnya fungsi harus IMMUTABLE) dibahas tuntas di chapter Expression Index . Bukti index itu benar-benar terpakai:

sql
explain-fungsi.sql
-- Bukti expression index terpakai: index biasa pada kolom email
-- tidak bisa melayani kondisi lower(email) = ..., tetapi index
-- atas lower(email) bisa. Tabel ini kecil sehingga planner memilih
-- Seq Scan; enable_seqscan dimatikan SEKADAR DIAGNOSTIK untuk
-- memaksa rencana index tampil -- jangan dipakai di produksi.
SET enable_seqscan = off;

EXPLAIN
SELECT * FROM kontak WHERE lower(email) = '[email protected]';

RESET enable_seqscan;

Output yang Dihasilkan:

text
SET
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Index Scan using idx_kontak_email_bawah on kontak  (cost=0.14..8.16 rows=1 width=37)
   Index Cond: (lower(email) = '[email protected]'::text)
(2 rows)

RESET

Baris Index Cond: (lower(email) = ...) membuktikan expression index terpakai persis pada bentuk kondisinya.

Empat kebiasaan penutup chapter ini: indeks setiap kolom kunci join yang disaring atau dicocokkan, tulis join dalam urutan yang paling mudah dibaca karena planner menyusun ulang sendiri, agregasikan sebelum join ketika relasi 1:N ikut tersambung, dan bungkus kolom join dengan fungsi hanya bersama expression index yang cocok. Pada chapter berikutnya kita menghadapi pola lambat yang tidak melibatkan join sama sekali — halaman daftar yang diklik sampai kedalaman ke-seribu.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- SALAH: agregat SETELAH join 1:N. Setiap pesanan tampil dua kali
-- (satu per baris detail), sehingga sum(total) membengkak dua lipat.
SELECT p.nama, sum(o.total) AS pendapatan_membengkak, sum(d.qty) AS jumlah_barang
FROM pelanggan p
JOIN pesanan o ON o.pelanggan_id = p.id
JOIN detail_pesanan d ON d.pesanan_id = o.id
GROUP BY p.id
ORDER BY p.id
LIMIT 3;

-- BENAR: pre-agregasi di derived table, join hanya menyambungkan
-- hasil yang sudah ringkas -- satu baris per pelanggan.
SELECT p.nama, b.pendapatan, d.jumlah_barang
FROM pelanggan p
JOIN (
    SELECT pelanggan_id, sum(total) AS pendapatan
    FROM pesanan
    GROUP BY pelanggan_id
) b ON b.pelanggan_id = p.id
JOIN (
    SELECT o.pelanggan_id, sum(d.qty) AS jumlah_barang
    FROM pesanan o
    JOIN detail_pesanan d ON d.pesanan_id = o.id
    GROUP BY o.pelanggan_id
) d ON d.pelanggan_id = p.id
ORDER BY p.id
LIMIT 3;

sql
-- Index pada kolom foreign key: pencarian paling sering
-- dilupakan karena PostgreSQL tidak membuatnya otomatis.
CREATE INDEX idx_pesanan_pelanggan ON pesanan (pelanggan_id);

ANALYZE pesanan;

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'pesanan'
ORDER BY indexname;

sql
-- Domain latihan: pelanggan -> pesanan -> detail_pesanan.
-- Kolom foreign key pesanan.pelanggan_id sengaja TIDAK diindeks
-- dulu: itulah kondisi awal yang mau kita perbaiki.
CREATE TABLE pelanggan (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text NOT NULL,
    kota text NOT NULL
);

INSERT INTO pelanggan (nama, kota)
SELECT 'Pelanggan ' || lpad(g::text, 3, '0'),
       (ARRAY['Jakarta','Bandung','Surabaya'])[1 + g % 3]
FROM generate_series(1, 200) AS g;

CREATE TABLE pesanan (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pelanggan_id integer NOT NULL REFERENCES pelanggan(id),
    total numeric(12,2) NOT NULL,
    tanggal date NOT NULL
);

INSERT INTO pesanan (pelanggan_id, total, tanggal)
SELECT 1 + (g * 7919) % 200,
       50000 + (g % 100) * 1250,
       DATE '2026-01-01' + (g % 180)
FROM generate_series(1, 20000) AS g;

CREATE TABLE detail_pesanan (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pesanan_id integer NOT NULL REFERENCES pesanan(id),
    barang text NOT NULL,
    qty integer NOT NULL
);

INSERT INTO detail_pesanan (pesanan_id, barang, qty)
SELECT g, (ARRAY['Kopi','Teh'])[1 + g % 2], 1 + g % 3
FROM generate_series(1, 20000) AS g
CROSS JOIN generate_series(1, 2) AS d;

ANALYZE pelanggan;
ANALYZE pesanan;
ANALYZE detail_pesanan;

SELECT count(*) AS jumlah_pelanggan FROM pelanggan;
SELECT count(*) AS jumlah_pesanan FROM pesanan;
SELECT count(*) AS jumlah_detail FROM detail_pesanan;

sql
-- Bukti expression index terpakai: index biasa pada kolom email
-- tidak bisa melayani kondisi lower(email) = ..., tetapi index
-- atas lower(email) bisa. Tabel ini kecil sehingga planner memilih
-- Seq Scan; enable_seqscan dimatikan SEKADAR DIAGNOSTIK untuk
-- memaksa rencana index tampil -- jangan dipakai di produksi.
SET enable_seqscan = off;

EXPLAIN
SELECT * FROM kontak WHERE lower(email) = '[email protected]';

RESET enable_seqscan;

sql
-- Rencana SEBELUM ada index pada kolom join:
-- pesanan discan penuh 20.000 baris untuk menemukan
-- sekitar 100 baris milik satu pelanggan.
-- Angka cost bervariasi tipis antar eksekusi karena sampling ANALYZE.
EXPLAIN
SELECT p.nama, o.total
FROM pelanggan p JOIN pesanan o ON o.pelanggan_id = p.id
WHERE p.id = 42;

sql
-- Rencana SETELAH index pada kolom join:
-- pesanan kini dicapai lewat index, bukan scan penuh.
-- Angka cost bervariasi tipis antar eksekusi karena sampling ANALYZE.
EXPLAIN
SELECT p.nama, o.total
FROM pelanggan p JOIN pesanan o ON o.pelanggan_id = p.id
WHERE p.id = 42;

-- Urutan tabel di INNER JOIN bebas ditulis bagaimana pun:
-- planner menyusun ulang urutan secara internal, dan kedua
-- penulisan berikut menghasilkan rencana yang identik.
EXPLAIN
SELECT p.nama, o.total
FROM pesanan o JOIN pelanggan p ON o.pelanggan_id = p.id
WHERE p.id = 42;

sql
-- Data kontak dengan kapitalisasi email yang berantakan,
-- khas hasil import dari sistem lama.
CREATE TABLE kontak (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text NOT NULL,
    email text NOT NULL
);

INSERT INTO kontak (nama, email)
SELECT 'Kontak ' || lpad(g::text, 3, '0'),
       'Kontak' || lpad(g::text, 3, '0') || '@Example.com'
FROM generate_series(1, 200) AS g;

CREATE TABLE kontak_lama (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama_lama text NOT NULL,
    email_lama text NOT NULL
);

INSERT INTO kontak_lama (nama_lama, email_lama)
SELECT 'Nama Lama ' || lpad(g::text, 3, '0'),
       'kontak' || lpad(g::text, 3, '0') || '@example.COM'
FROM generate_series(1, 50) AS g;

ANALYZE kontak;
ANALYZE kontak_lama;

-- Join dengan fungsi di kedua kolom: hasilnya benar,
-- tetapi index biasa pada email tidak bisa ikut bekerja.
SELECT count(*) AS pasangan_email
FROM kontak k
JOIN kontak_lama kl ON lower(k.email) = lower(kl.email_lama);

-- Perbaikannya: expression index atas hasil fungsi lower(email).
CREATE INDEX idx_kontak_email_bawah ON kontak (lower(email));

ANALYZE kontak;

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'kontak'
ORDER BY indexname;