Aplikasi jarang mencari data persis seperti disimpannya. Login mencocokkan email tanpa peduli huruf besar-kecil, laporan bulanan menyaring EXTRACT(year FROM tanggal), dan pengawasan SLA menghitung selisih dua kolom waktu. Semua query ini menyaring hasil olahan kolom — dan index biasa yang menyimpan nilai kolom mentah sama sekali tidak bisa membantu, karena yang dicari memang bukan isi kolom. PostgreSQL menjawab dengan expression index: index yang kuncinya bukan kolom, melainkan hasil sebuah ekspresi seperti lower(email). Chapter ini membangun index semacam itu, membuktikan lewat EXPLAIN bahwa query harus menulis ekspresi yang sama persis, lalu menabrak dua batas kerasnya: fungsi non-IMMUTABLE ditolak, dan keunikan bisa ditegakkan atas hasil ekspresi.

Masalahnya: Kolom Asli Bukan yang Dicari #

Tabel pelanggan berikut menyimpan 1000 baris dengan email yang huruf besar-kecilnya beragam — persis seperti data nyata hasil impor dari beberapa sistem. Dua query penutup file ini memperlihatkan masalah intinya dalam angka:

sql buat-tabel.sql 
-- 1000 pelanggan dengan email campuran huruf besar-kecil.
CREATE TABLE pelanggan (
    id        integer PRIMARY KEY,
    nama      text NOT NULL,
    email     text NOT NULL,
    tgl_lahir date NOT NULL
);

INSERT INTO pelanggan
SELECT g,
       'Pelanggan ' || g,
       CASE g % 3
           WHEN 0 THEN 'Pelanggan'
           WHEN 1 THEN 'PELANGGAN'
           ELSE 'pelanggan'
       END || g || '@Example.com',
       DATE '1980-01-01' + (g * 11)
FROM generate_series(1, 1000) AS g;

ANALYZE pelanggan;

SELECT id, nama, email, tgl_lahir
FROM pelanggan
WHERE id <= 5
ORDER BY id;

-- Pencarian peka huruf: email dengan huruf berbeda tidak ketemu.
SELECT count(*) AS sama_persis
FROM pelanggan
WHERE email = '[email protected]';

-- Pencarian dengan lower(): huruf besar-kecil diabaikan.
SELECT count(*) AS abaikan_huruf
FROM pelanggan
WHERE lower(email) = '[email protected]';

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 1000
ANALYZE
 id |    nama     |         email          | tgl_lahir  
----+-------------+------------------------+------------
  1 | Pelanggan 1 | [email protected] | 1980-01-12
  2 | Pelanggan 2 | [email protected] | 1980-01-23
  3 | Pelanggan 3 | [email protected] | 1980-02-03
  4 | Pelanggan 4 | [email protected] | 1980-02-14
  5 | Pelanggan 5 | [email protected] | 1980-02-25
(5 rows)

 sama_persis 
-------------
           0
(1 row)

 abaikan_huruf 
---------------
             1
(1 row)

Perbandingan count di bagian akhir adalah inti masalahnya. Email pelanggan 500 tersimpan sebagai [email protected], sehingga pencarian email = '[email protected]' menghasilkan nol baris — huruf besar pada domain membuatnya meleset. Pembungkus lower(email) menemukan baris itu dengan satu baris hasil, dan itulah bentuk query yang benar secara semantik untuk login. Masalahnya: WHERE lower(email) = ... tidak bisa memakai index apa pun yang dibangun di atas kolom email mentah, sebab urutan nilai di index mengikuti nilai asli kolom, bukan hasil lower().

Mengindeks Hasil Ekspresi #

Solusinya: minta PostgreSQL menghitung ekspresi sekali pada saat index dibangun, lalu menyimpan hasilnya sebagai kunci index:

sql index-ekspresi.sql 
-- Index menyimpan hasil lower(email), bukan email mentah.
CREATE INDEX idx_pelanggan_email_bawah
    ON pelanggan (lower(email));

-- ANALYZE setelah CREATE INDEX: planner mendapat statistik ekspresi.
ANALYZE pelanggan;

-- Query yang memakai ekspresi YANG SAMA bisa menikmati index.
SELECT id, nama, email
FROM pelanggan
WHERE lower(email) = '[email protected]';

Output yang Dihasilkan:

text 
CREATE INDEX
ANALYZE
 id  |     nama      |          email           
-----+---------------+--------------------------
 500 | Pelanggan 500 | [email protected]
(1 row)

Baris ON pelanggan (lower(email)) adalah satu-satunya perbedahan sintaks dibanding index biasa — kolom diganti ekspresi yang dibungkus tanda kurung. Kini setiap entri index berisi teks yang sudah dikecilkan hurufnya, dan query ber-WHERE lower(email) = ... mencari tepat di wilayah itu. Perhatikan juga ANALYZE yang dijalankan setelah index dibuat: ANALYZE turut mengumpulkan statistik untuk ekspresi yang diindeks, sehingga planner mengestimasi jumlah baris hasil secara jujur alih-alih memakai tebakan default.

Rencana Query: Ekspresi Harus Sama Persis #

Janji index baru ini bisa diverifikasi. EXPLAIN berikut membandingkan query yang menulis ulang ekspresi index dengan query yang menyentuh kolom mentah:

sql rencana-ekspresi.sql 
-- Ekspresi di query = ekspresi di index: index terpakai.
EXPLAIN
SELECT id, nama
FROM pelanggan
WHERE lower(email) = '[email protected]';

-- Ekspresi berbeda (kolom mentah): index tidak terpakai.
EXPLAIN
SELECT id, nama
FROM pelanggan
WHERE email = '[email protected]';

Output yang Dihasilkan:

text 
                                         QUERY PLAN                                          
--------------------------------------------------------------------------------------------
 Index Scan using idx_pelanggan_email_bawah on pelanggan  (cost=0.28..8.29 rows=1 width=17)
   Index Cond: (lower(email) = '[email protected]'::text)
(2 rows)

                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on pelanggan  (cost=0.00..22.50 rows=1 width=17)
   Filter: (email = '[email protected]'::text)
(2 rows)

Rencana pertama memakai Index Scan using idx_pelanggan_email_bawah dengan Index Cond yang menampilkan ekspresi lower(email) — sama persis dengan kunci index. Rencana kedua menjatuhkan diri ke Seq Scan meskipun nilainya secara kebetulan cocok dengan satu baris: kolom email mentah bukan kunci index mana pun yang ada, jadi tidak ada yang bisa dicari. Angka cost dan rows pada output ini bergantung pada statistik dan lingkungan planner Anda; bentuk rencananya — index scan lawan seq scan — yang menjadi kesimpulan yang stabil. Implikasinya tegas untuk penulis aplikasi: kalau login memakai WHERE lower(email) = $1, seluruh query lain yang menyaring email harus konsisten memakai bentuk yang sama.

Hanya Fungsi IMMUTABLE yang Diizinkan #

Index harus bisa dipercaya bertahun-tahun, jadi ekspresinya wajib menghasilkan nilai yang sama untuk input yang sama, selamanya — PostgreSQL menyebut kualitas ini IMMUTABLE. Fungsi age() menghitung usia relatif terhadap waktu sekarang: hasilnya berubah setiap hari untuk input yang sama, sehingga berstatus STABLE dan layak ditolak:

sql non-immutable.sql 
-- age() bergantung pada waktu sekarang: STABLE, bukan IMMUTABLE.
CREATE INDEX idx_pelanggan_usia
    ON pelanggan (age(tgl_lahir));

Output yang Dihasilkan:

text 
psql:non-immutable.sql:3: ERROR:  functions in index expression must be marked IMMUTABLE

Error functions in index expression must be marked IMMUTABLE datang dari parser CREATE INDEX sebelum satu baris data pun diindeks — PostgreSQL menolaknya di muka, bukan menunggu index rusak diam-diam. Tiga tingkat volatilitas perlu diingat: IMMUTABLE (aman untuk index), STABLE (per-request, seperti now() dan age()), dan VOLATILE (berubah kapan pun). Contoh yang lolos: lower(), upper(), abs(), EXTRACT(year FROM kolom_tanggal), dan to_tsvector('nama_config', teks) dengan config eksplisit. Contoh yang ditolak: age(), now(), to_tsvector(teks) satu argumen — versi ini mengikuti pengaturan default_text_search_config yang bisa diubah admin, sehingga tidak immutable. Daftar lengkapnya dirinci di halaman Function Volatility .

Unik atas Hasil Ekspresi #

Index ekspresi kompatibel penuh dengan UNIQUE — kombinasi yang menegakkan aturan “email unik tanpa peduli huruf besar-kecil”, persis perilaku yang diharapkan sistem pendaftaran:

sql unik-ekspresi.sql 
-- Ganti index biasa dengan index unik pada ekspresi yang sama.
DROP INDEX idx_pelanggan_email_bawah;

CREATE UNIQUE INDEX idx_pelanggan_email_unik
    ON pelanggan (lower(email));

-- Pastikan 1000 email memang unik setelah di-lower().
SELECT count(DISTINCT lower(email)) AS email_unik_bawah
FROM pelanggan;

Output yang Dihasilkan:

text 
DROP INDEX
CREATE INDEX
 email_unik_bawah 
------------------
             1000
(1 row)

Index biasa dijatuhkan lebih dulu supaya tidak ada dua index pada ekspresi yang sama — varian unik sudah mencakup seluruh fungsi pendahulunya. Query penutup memastikan count(DISTINCT lower(email)) bernilai 1000, sama dengan jumlah baris: tidak ada duplikat tersembunyi yang akan membuat pembuatan index unik gagal. Uji penolakannya tinggal menambahkan email yang hurufnya berbeda tetapi lower() -nya sama:

sql duplikat-ekspresi.sql 
-- Huruf besar-kecil berbeda, tapi lower() -nya sama: ditolak.
INSERT INTO pelanggan (id, nama, email, tgl_lahir) VALUES
    (1001, 'Pelanggan Baru', '[email protected]', DATE '1995-06-15');

Output yang Dihasilkan:

text 
psql:duplikat-ekspresi.sql:3: ERROR:  duplicate key value violates unique constraint "idx_pelanggan_email_unik"
DETAIL:  Key (lower(email))=([email protected]) already exists.

Barangkali [email protected] terlihat berbeda dari baris lama, tetapi DETAIL pada error menunjukkan penghitung kunci index bekerja pada lower(email) yang bernilai [email protected] — kecocokan ditemukan dan INSERT ditolak. Hubungan index unik dengan UNIQUE constraint dan primary key sudah dibedah di chapter Unique Index ; pola partial + unik pada subset juga tersedia lewat kombinasi dengan klausa WHERE.

Ekspresi Selain Fungsi: Selisih Dua Kolom Waktu #

Ekspresi tidak harus panggilan fungsi — operasi aritmetika antar dua kolom pun sah sebagai kunci. Kasus klasiknya pengawasan SLA kedatangan barang: pertanyaan operasional bukan “kapan barang datang” melainkan “berapa jam ia terlambat”:

sql variasi-ekspresi.sql 
-- Ekspresi tidak harus fungsi: selisih dua timestamp pun bisa diindeks.
CREATE TABLE kedatangan (
    id      integer PRIMARY KEY,
    pemasok text NOT NULL,
    rencana timestamp NOT NULL,
    aktual  timestamp NOT NULL
);

-- 500 kedatangan; sebagian kecil telat lebih dari 3 jam.
INSERT INTO kedatangan
SELECT g,
       'Pemasok ' || (g % 25),
       TIMESTAMP '2026-03-02 06:00:00' + (g * 5 || ' minutes')::interval,
       TIMESTAMP '2026-03-02 06:00:00' + (g * 5 || ' minutes')::interval
           + (CASE WHEN g % 25 = 0
                   THEN 6 + (g % 4)
                   ELSE (g % 3) - 1
              END || ' hours')::interval
FROM generate_series(1, 500) AS g;

CREATE INDEX idx_kedatangan_telat
    ON kedatangan ((aktual - rencana));

ANALYZE kedatangan;

SELECT count(*) AS sangat_telat
FROM kedatangan
WHERE (aktual - rencana) > interval '3 hours';

SELECT id, pemasok, aktual - rencana AS keterlambatan
FROM kedatangan
WHERE (aktual - rencana) > interval '3 hours'
ORDER BY id
LIMIT 3;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 500
CREATE INDEX
ANALYZE
 sangat_telat 
--------------
           20
(1 row)

 id |  pemasok  | keterlambatan 
----+-----------+---------------
 25 | Pemasok 0 | 07:00:00
 50 | Pemasok 0 | 08:00:00
 75 | Pemasok 0 | 09:00:00
(3 rows)

Mari kita bedah konstruksinya:

  1. Baris CREATE INDEX ... ((aktual - rencana)): Kunci index adalah interval selisih dua kolom — tanda kurung ganda wajib; satu lapis kurung akan dibaca sebagai daftar kolom dan ditolak parser.
  2. Baris CASE WHEN g % 25 = 0 THEN 6 + (g % 4) ...: Distribusi keterlambatan dibuat timpang — mayoritas baris hanya meleset satu jam ke segala arah, sedangkan 20 baris telat 6 sampai 9 jam. Bentuk data inilah yang membuat query “telat lebih dari 3 jam” selektif dan layak diindeks.
  3. Query WHERE (aktual - rencana) > interval '3 hours': Query menulis ulang ekspresi index sama persis, sehingga penyaringan terjadi di atas nilai selisih yang sudah tersimpan — hasilnya 20 baris dengan keterlambatan terpanjang di muka.

Pola yang sama meluas ke banyak bentuk lain: UPPER(kode) untuk pencarian kode voucher seragam, EXTRACT(year FROM tgl_lahir) untuk laporan tahunan, hingga (harga * stok) untuk query nilai inventori. Batasannya selalu dua: ekspresi harus immutable, dan query harus menulisnya ulang sama persis. Kalau semua kolom yang dibutuhkan query sudah tersedia, pertanyaan berikutnya bukan lagi apa yang diindeks melainkan apa saja yang bisa dibawa sebagai muatan index — jawabannya klausa INCLUDE dan index-only scan di chapter Covering Index .

Referensi #

Berkas Sumber

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

buat-tabel.sql

Unduh berkas
sql 
-- 1000 pelanggan dengan email campuran huruf besar-kecil.
CREATE TABLE pelanggan (
    id        integer PRIMARY KEY,
    nama      text NOT NULL,
    email     text NOT NULL,
    tgl_lahir date NOT NULL
);

INSERT INTO pelanggan
SELECT g,
       'Pelanggan ' || g,
       CASE g % 3
           WHEN 0 THEN 'Pelanggan'
           WHEN 1 THEN 'PELANGGAN'
           ELSE 'pelanggan'
       END || g || '@Example.com',
       DATE '1980-01-01' + (g * 11)
FROM generate_series(1, 1000) AS g;

ANALYZE pelanggan;

SELECT id, nama, email, tgl_lahir
FROM pelanggan
WHERE id <= 5
ORDER BY id;

-- Pencarian peka huruf: email dengan huruf berbeda tidak ketemu.
SELECT count(*) AS sama_persis
FROM pelanggan
WHERE email = '[email protected]';

-- Pencarian dengan lower(): huruf besar-kecil diabaikan.
SELECT count(*) AS abaikan_huruf
FROM pelanggan
WHERE lower(email) = '[email protected]';

duplikat-ekspresi.sql

Unduh berkas
sql 
-- Huruf besar-kecil berbeda, tapi lower() -nya sama: ditolak.
INSERT INTO pelanggan (id, nama, email, tgl_lahir) VALUES
    (1001, 'Pelanggan Baru', '[email protected]', DATE '1995-06-15');

index-ekspresi.sql

Unduh berkas
sql 
-- Index menyimpan hasil lower(email), bukan email mentah.
CREATE INDEX idx_pelanggan_email_bawah
    ON pelanggan (lower(email));

-- ANALYZE setelah CREATE INDEX: planner mendapat statistik ekspresi.
ANALYZE pelanggan;

-- Query yang memakai ekspresi YANG SAMA bisa menikmati index.
SELECT id, nama, email
FROM pelanggan
WHERE lower(email) = '[email protected]';

non-immutable.sql

Unduh berkas
sql 
-- age() bergantung pada waktu sekarang: STABLE, bukan IMMUTABLE.
CREATE INDEX idx_pelanggan_usia
    ON pelanggan (age(tgl_lahir));

rencana-ekspresi.sql

Unduh berkas
sql 
-- Ekspresi di query = ekspresi di index: index terpakai.
EXPLAIN
SELECT id, nama
FROM pelanggan
WHERE lower(email) = '[email protected]';

-- Ekspresi berbeda (kolom mentah): index tidak terpakai.
EXPLAIN
SELECT id, nama
FROM pelanggan
WHERE email = '[email protected]';

unik-ekspresi.sql

Unduh berkas
sql 
-- Ganti index biasa dengan index unik pada ekspresi yang sama.
DROP INDEX idx_pelanggan_email_bawah;

CREATE UNIQUE INDEX idx_pelanggan_email_unik
    ON pelanggan (lower(email));

-- Pastikan 1000 email memang unik setelah di-lower().
SELECT count(DISTINCT lower(email)) AS email_unik_bawah
FROM pelanggan;

variasi-ekspresi.sql

Unduh berkas
sql 
-- Ekspresi tidak harus fungsi: selisih dua timestamp pun bisa diindeks.
CREATE TABLE kedatangan (
    id      integer PRIMARY KEY,
    pemasok text NOT NULL,
    rencana timestamp NOT NULL,
    aktual  timestamp NOT NULL
);

-- 500 kedatangan; sebagian kecil telat lebih dari 3 jam.
INSERT INTO kedatangan
SELECT g,
       'Pemasok ' || (g % 25),
       TIMESTAMP '2026-03-02 06:00:00' + (g * 5 || ' minutes')::interval,
       TIMESTAMP '2026-03-02 06:00:00' + (g * 5 || ' minutes')::interval
           + (CASE WHEN g % 25 = 0
                   THEN 6 + (g % 4)
                   ELSE (g % 3) - 1
              END || ' hours')::interval
FROM generate_series(1, 500) AS g;

CREATE INDEX idx_kedatangan_telat
    ON kedatangan ((aktual - rencana));

ANALYZE kedatangan;

SELECT count(*) AS sangat_telat
FROM kedatangan
WHERE (aktual - rencana) > interval '3 hours';

SELECT id, pemasok, aktual - rencana AS keterlambatan
FROM kedatangan
WHERE (aktual - rencana) > interval '3 hours'
ORDER BY id
LIMIT 3;