Untuk query yang memfilter tenant_id dan status dengan kondisi equality, membatasi rentang created_at, lalu menjalankan ORDER BY created_at, kandidat awal yang masuk akal adalah index komposit (tenant_id, status, created_at DESC). Susunan ini menempatkan filter equality sebelum filter range sehingga PostgreSQL dapat mempersempit pencarian sekaligus membaca baris dalam urutan waktu yang diminta.

Namun, susunan tersebut bukan aturan universal. Distribusi data, jenis predicate, banyaknya baris yang dikembalikan, kolom yang dipilih, dan pola query lain dapat membuat index berbeda lebih efektif. Karena itu, keputusan akhir harus diverifikasi dengan EXPLAIN (ANALYZE, BUFFERS) pada data yang representatif.

Contoh tabel dan query awal

Anggap tabel orders menyimpan jutaan pesanan dari banyak tenant. Setiap tenant memiliki pesanan dengan beberapa status dan waktu pembuatan yang berbeda.

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL,
    customer_id bigint NOT NULL,
    total_amount numeric(14, 2) NOT NULL,
    payload jsonb
);

Query aplikasi mengambil 50 pesanan terbaru untuk satu tenant dan satu status dalam rentang waktu tertentu:

SELECT id, tenant_id, status, created_at, total_amount
FROM orders
WHERE tenant_id = 8421
  AND status = 'paid'
  AND created_at >= TIMESTAMPTZ '2025-01-01 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2025-02-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 50;

Batas atas eksklusif dengan operator < umumnya lebih aman daripada mencoba menentukan detik atau mikrodetik terakhir sebuah periode. Pola ini juga bekerja konsisten untuk nilai timestamp dengan presisi tinggi.

Memeriksa rencana sebelum optimasi

Jalankan query dengan instrumen planner berikut:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, tenant_id, status, created_at, total_amount
FROM orders
WHERE tenant_id = 8421
  AND status = 'paid'
  AND created_at >= TIMESTAMPTZ '2025-01-01 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2025-02-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN ANALYZE benar-benar menjalankan query. Untuk contoh SELECT ini risikonya rendah, tetapi kehati-hatian diperlukan saat menggunakannya pada perintah yang mengubah data.

Tanpa index yang sesuai, bentuk rencana yang umum terlihat seperti berikut. Detail biaya, waktu, dan jumlah buffer sengaja tidak dicantumkan karena nilainya harus berasal dari sistem yang diuji:

Limit
  -> Sort
       Sort Key: created_at DESC
       -> Seq Scan on orders
            Filter: tenant_id = 8421
                    AND status = 'paid'
                    AND created_at >= ...
                    AND created_at < ...

Pada tabel besar, PostgreSQL mungkin harus membaca banyak halaman, membuang baris yang tidak cocok, lalu mengurutkan hasil. Planner juga dapat memilih Parallel Seq Scan dan Gather Merge. Itu bukan kesalahan planner; pilihan tersebut dapat menjadi yang termurah ketika belum ada index yang relevan atau query mengambil sebagian besar tabel.

Menentukan urutan kolom index komposit PostgreSQL

Index B-tree berikut merupakan titik awal yang sesuai dengan pola query tersebut:

CREATE INDEX idx_orders_tenant_status_created_at
ON orders (tenant_id, status, created_at DESC);

Di lingkungan produksi, pembangunan index dapat memakai CONCURRENTLY untuk mengurangi pemblokiran operasi tulis:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created_at
ON orders (tenant_id, status, created_at DESC);

CREATE INDEX CONCURRENTLY memerlukan waktu dan pekerjaan tambahan, serta tidak boleh dijalankan di dalam transaction block eksplisit. Pantau prosesnya dan periksa index invalid jika pembangunan gagal atau dibatalkan.

Equality sebelum range

Query menggunakan equality pada dua kolom pertama:

  • tenant_id = 8421
  • status = 'paid'

Setelah PostgreSQL menemukan bagian index untuk kombinasi tenant dan status tersebut, database dapat memindai rentang created_at. Secara konseptual, urutannya adalah:

  1. Temukan satu tenant.
  2. Di dalam tenant tersebut, temukan satu status.
  3. Pindai hanya rentang waktu yang diminta.
  4. Hentikan pemindaian setelah memenuhi LIMIT 50, jika tidak ada kondisi lain yang memaksa pemeriksaan lebih lanjut.

Prinsip praktisnya adalah menempatkan kolom equality yang relevan sebelum kolom range. Setelah predicate range digunakan pada sebuah key index, kolom key berikutnya biasanya tidak seefektif kolom sebelumnya untuk membatasi rentang pemindaian. Meski demikian, detail pemanfaatannya tetap bergantung pada predicate dan kemampuan planner.

Leftmost prefix

Index (tenant_id, status, created_at) paling mudah dimanfaatkan oleh query yang menggunakan awalan kolomnya, misalnya:

  • tenant_id = ...
  • tenant_id = ... AND status = ...
  • tenant_id = ... AND status = ... AND created_at ...

Index ini tidak ideal untuk query yang hanya memfilter status atau hanya created_at, karena kolom paling kiri tidak dibatasi. PostgreSQL memiliki beberapa strategi tambahan yang kadang dapat memanfaatkan index secara terbatas, tetapi jangan mengandalkannya sebagai pengganti desain index yang sesuai dengan pola query utama.

Selektivitas dan urutan dua kolom equality

Aturan sederhana seperti menaruh kolom paling selektif di depan tidak selalu cukup. Untuk query yang selalu menggunakan equality pada tenant_id dan status, kedua urutan berikut dapat mempersempit pencarian ke kombinasi yang sama:

(tenant_id, status, created_at)
(status, tenant_id, created_at)

Pilihannya juga harus mempertimbangkan leftmost prefix dan query lain. Jika hampir semua akses dibatasi tenant, menaruh tenant_id di depan biasanya lebih berguna karena index dapat mendukung query tenant tanpa filter status. Sebaliknya, sistem yang sering mencari satu status lintas tenant mungkin memiliki kebutuhan berbeda.

Selektivitas sendiri berarti seberapa kecil bagian tabel yang dipilih oleh suatu kondisi. Status dengan dua nilai yang distribusinya seimbang biasanya kurang selektif daripada tenant tertentu di sistem multi-tenant besar. Distribusi yang timpang juga penting: status langka dapat sangat selektif, sementara status yang dimiliki hampir semua baris tidak demikian.

Mendukung ORDER BY created_at

Karena tenant_id dan status dikunci ke satu nilai, entri yang relevan di index sudah tersusun berdasarkan created_at DESC. PostgreSQL dapat melakukan ordered index scan tanpa node Sort terpisah.

Setelah index dibuat dan statistik diperbarui, jalankan:

ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, tenant_id, status, created_at, total_amount
FROM orders
WHERE tenant_id = 8421
  AND status = 'paid'
  AND created_at >= TIMESTAMPTZ '2025-01-01 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2025-02-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 50;

Bentuk rencana yang diharapkan, jika planner menilai index menguntungkan, adalah:

Limit
  -> Index Scan using idx_orders_tenant_status_created_at on orders
       Index Cond: tenant_id = 8421
                   AND status = 'paid'
                   AND created_at >= ...
                   AND created_at < ...

Indikator utama bukan sekadar munculnya Index Scan. Bandingkan juga:

  • Execution Time pada beberapa eksekusi yang representatif.
  • Jumlah shared hit dan shared read di bagian Buffers.
  • actual rows dibandingkan dengan estimasi rows.
  • Ada atau tidaknya node Sort.
  • Jumlah Rows Removed by Filter.
  • Perbedaan saat cache dingin dan cache hangat.

B-tree PostgreSQL juga dapat dipindai maju atau mundur. Karena hanya satu kolom pengurutan setelah equality prefix, index dengan urutan default ASC sering tetap dapat melayani ORDER BY created_at DESC melalui backward scan. Deklarasi DESC membuat tujuan index lebih jelas dan menjadi lebih relevan ketika terdapat beberapa kolom pengurutan dengan arah campuran.

Kapan memakai INCLUDE atau partial index

INCLUDE untuk peluang index-only scan

Index dasar belum memuat id dan total_amount, padahal kedua kolom tersebut dipilih. PostgreSQL harus mengakses heap untuk mengambil nilainya. Jika query ini sangat sering dijalankan dan tambahan ukuran dapat diterima, kolom keluaran dapat ditambahkan sebagai non-key columns:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created_covering
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_amount);

INCLUDE tidak ikut menentukan urutan pencarian. Tujuannya adalah menyediakan payload agar query memiliki peluang menggunakan Index Only Scan. Keuntungan ini tidak dijamin: PostgreSQL masih perlu memeriksa heap jika visibility map belum menunjukkan bahwa halaman terkait terlihat untuk semua transaksi. Tabel yang sering diperbarui juga cenderung mendapat manfaat lebih kecil.

Jangan memasukkan payload JSONB yang besar hanya untuk membuat covering index. Kolom lebar memperbesar index, meningkatkan I/O, dan menambah biaya setiap operasi tulis.

Partial index untuk subset yang konsisten

Jika aplikasi hampir selalu mencari status tertentu dan hanya sebagian kecil baris memiliki status tersebut, partial index dapat lebih kecil:

CREATE INDEX CONCURRENTLY idx_orders_pending_tenant_created_at
ON orders (tenant_id, created_at DESC)
INCLUDE (id, total_amount)
WHERE status = 'pending';

Index ini cocok untuk query yang predicate-nya secara logis menjamin status = 'pending':

SELECT id, tenant_id, created_at, total_amount
FROM orders
WHERE tenant_id = 8421
  AND status = 'pending'
  AND created_at >= TIMESTAMPTZ '2025-01-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 50;

Partial index tidak cocok sebagai pengganti index umum jika aplikasi mencari banyak status. Planner juga harus dapat membuktikan bahwa predicate query memenuhi predicate index. Pada prepared statement dengan parameter status, khususnya ketika rencana generik digunakan, pembuktian tersebut dapat gagal karena nilai parameter tidak diketahui saat perencanaan.

Memverifikasi dampak secara aman

Pengukuran sebelum dan sesudah sebaiknya menggunakan parameter yang mewakili beberapa kondisi: tenant besar, tenant kecil, status umum, status langka, rentang sempit, dan rentang lebar. Satu tenant yang kebetulan memiliki sedikit data tidak mewakili keseluruhan beban kerja.

  1. Simpan hasil EXPLAIN (ANALYZE, BUFFERS) sebelum membuat index.
  2. Buat index, kemudian jalankan ANALYZE orders jika diperlukan.
  3. Jalankan query yang sama beberapa kali dan pisahkan pengamatan cache dingin dari cache hangat.
  4. Pastikan hasil query tetap identik.
  5. Bandingkan node rencana, estimasi baris, waktu eksekusi, dan buffer.
  6. Amati efek terhadap latensi operasi INSERT, UPDATE, dan DELETE.

Statistik pemakaian index dapat diperiksa melalui view bawaan:

SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

Ukuran index juga perlu dipantau:

SELECT
    pg_size_pretty(pg_relation_size('idx_orders_tenant_status_created_at'))
        AS index_size;

Nilai idx_scan harus dibaca sesuai periode statistik server. Nilai rendah tidak otomatis berarti index tidak berguna; index untuk proses bulanan atau penanganan insiden mungkin jarang digunakan tetapi tetap bernilai. Sebaliknya, index yang tidak pernah digunakan dan tumpang tindih dengan index lain patut ditinjau.

Mengapa planner tetap memilih sequential scan?

Sequential scan setelah index dibuat tidak selalu menunjukkan masalah. Planner dapat memilihnya karena:

  • Query mengambil terlalu banyak baris. Membaca tabel secara berurutan dapat lebih murah daripada banyak akses heap melalui index.
  • Filter kurang selektif. Tenant atau status tertentu mungkin mencakup sebagian besar tabel.
  • Rentang waktu terlalu lebar. Index kehilangan keuntungan jika hampir semua periode dibaca.
  • Statistik usang. Perubahan data besar dapat membuat estimasi jumlah baris tidak akurat; jalankan ANALYZE dan periksa aktivitas autovacuum.
  • Korelasi kolom tidak terwakili. Misalnya, status tertentu hanya muncul pada tenant tertentu. Extended statistics dapat membantu estimasi hubungan antar-kolom.
  • Ekspresi tidak cocok. Cast, fungsi pada kolom, atau perbedaan tipe parameter dapat menghalangi penggunaan index secara efektif.
  • Prepared statement menggunakan generic plan. Rencana generik mungkin tidak optimal untuk distribusi tenant yang sangat berbeda.
  • Biaya akses acak diperkirakan tinggi. Planner membandingkan biaya I/O, CPU, dan jumlah halaman, bukan sekadar keberadaan index.

Jika estimasi gabungan tenant_id dan status buruk, extended statistics dapat dipertimbangkan:

CREATE STATISTICS st_orders_tenant_status
ON tenant_id, status
FROM orders;

ANALYZE orders;

Statistik ini membantu estimasi planner, tetapi bukan struktur akses dan tidak menggantikan index.

Hindari menjadikan SET enable_seqscan = off sebagai solusi produksi. Pengaturan itu berguna untuk eksperimen apakah suatu rencana index mungkin tersedia, bukan untuk memaksa planner mengabaikan biaya sebenarnya.

Trade-off dan kesalahan umum

  • Ukuran index: semakin banyak key dan kolom INCLUDE, semakin besar ruang disk, cache, serta I/O yang dibutuhkan.
  • Write amplification: setiap INSERT, DELETE, atau perubahan kolom terindeks harus memperbarui index terkait.
  • Index redundan: index (tenant_id, status) mungkin tumpang tindih dengan (tenant_id, status, created_at), tetapi evaluasi constraint, ukuran, dan pola query sebelum menghapusnya.
  • Terlalu mengandalkan selektivitas tunggal: urutan kolom harus mempertimbangkan equality, range, pengurutan, dan leftmost prefix secara bersamaan.
  • Mengabaikan variasi tenant: satu rencana dapat bagus untuk tenant kecil tetapi buruk untuk tenant yang menyimpan bagian besar data.
  • Mengharapkan index-only scan terus-menerus: efektivitasnya dipengaruhi visibility map, vacuum, dan frekuensi perubahan data.
  • Membuat banyak index serupa: variasi kecil untuk setiap query dapat memperlambat operasi tulis tanpa manfaat yang sebanding.

Untuk query contoh, mulai dengan (tenant_id, status, created_at DESC), lalu ukur. Tambahkan INCLUDE hanya jika akses heap menjadi biaya dominan dan kolom payload cukup kecil. Gunakan partial index jika predicate status stabil, cukup selektif, dan benar-benar muncul pada query. Desain terbaik adalah desain yang sesuai dengan distribusi data serta beban kerja nyata, bukan susunan kolom yang dianggap selalu optimal.