Indeks PostgreSQL tidak harus merepresentasikan setiap baris dalam tabelnya. Partial index menambahkan predikat ke definisi indeks, sehingga hanya baris yang memenuhi predikat tersebut yang memperoleh entri indeks. Hal ini mengubah cakupan fisik indeks sekaligus kumpulan query yang dapat menggunakan indeks tersebut melalui planner.

Mekanisme ini cocok untuk workload ketika subset baris yang stabil menerima porsi query yang jauh lebih besar. Aplikasi mungkin berulang kali memeriksa job yang masih pending sementara job yang selesai sebagian besar hanya menjadi riwayat, atau melakukan query pada akun aktif sementara akun yang dinonaktifkan tetap berada dalam tabel yang sama.

Predikat menentukan keanggotaan indeks

Partial index menggunakan klausa WHERE dalam CREATE INDEX. Pertimbangkan tabel job yang sebagian besar barisnya pada akhirnya mencapai state terminal:

CREATE TABLE jobs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    queue text NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX jobs_pending_queue_created_idx
    ON jobs (queue, created_at)
    WHERE status = 'pending';

Indeks hanya berisi entri (queue, created_at) untuk baris dengan status bernilai pending. Baris dengan status lain tetap berada di heap tetapi tidak ada dalam indeks ini.

Kolom predikat tidak harus muncul di antara kolom yang diindeks. Pada contoh ini, status mengendalikan keanggotaan, sedangkan queue dan created_at menyediakan key indeks yang dipakai untuk menemukan dan mengurutkan baris yang cocok.

Perbedaan ini berpengaruh saat write. Memasukkan job pending menambahkan entri indeks. Mengubah baris tersebut dari pending menjadi running mengeluarkannya dari isi logis partial index karena versi baris baru tidak lagi memenuhi predikat. Baris yang tidak pernah memenuhi predikat tidak memerlukan entri dalam indeks khusus ini.

Kondisi query harus menetapkan predikat

Keanggotaan indeks saja tidak cukup agar planner dapat menggunakannya. Pada waktu planning, PostgreSQL harus dapat menetapkan bahwa kondisi query mengimplikasikan predikat indeks.

Query yang secara eksplisit membatasi status ke subset yang diindeks merupakan kecocokan langsung:

SELECT id, created_at
FROM jobs
WHERE status = 'pending'
  AND queue = 'email'
ORDER BY created_at
LIMIT 50;

Kondisi status = 'pending' menetapkan bahwa setiap baris yang relevan bagi query memenuhi syarat untuk berada dalam partial index. Planner kemudian dapat mempertimbangkan indeks ini bersama plan lain yang tersedia.

Jika kondisi status dihapus, jaminan tersebut hilang:

SELECT id, created_at
FROM jobs
WHERE queue = 'email'
ORDER BY created_at
LIMIT 50;

Partial index tidak dapat merepresentasikan job email yang berstatus running atau completed, sehingga tidak dapat berfungsi sebagai indeks umum untuk query ini.

PostgreSQL mengenali beberapa implikasi logis sederhana, termasuk ketaksamaan tertentu, tetapi tidak mencoba pembuktian simbolis arbitrer antara kondisi query dan predikat indeks. Karena itu, cara predikat ditulis dan struktur query memengaruhi apakah partial index yang tampak sesuai akan dipertimbangkan.

Parameterisasi dapat menyembunyikan subset yang sebenarnya dapat digunakan

Implikasi pada waktu planning juga menciptakan batas pada kondisi berparameter. Indeks seperti berikut:

CREATE INDEX jobs_recent_priority_idx
    ON jobs (created_at)
    WHERE priority < 10;

dapat dipertimbangkan untuk query dengan kondisi tetap yang diketahui mengimplikasikan priority < 10. Kondisi parameter generik seperti priority < $1 tidak dapat menetapkan implikasi itu untuk setiap kemungkinan nilai parameter. Planner tidak dapat mengasumsikan bahwa $1 selalu membuat query menjadi subset dari predikat indeks.

Ini merupakan sifat pembuktian predikat, bukan keterbatasan lookup B-tree. Partial index dapat memiliki kolom key yang sepenuhnya sesuai tetapi tetap tidak tersedia karena query tidak menetapkan keanggotaan dalam subset yang diindeks selama planning.

Cakupan yang lebih kecil mengubah biaya pemeliharaan

Indeks konvensional pada (queue, created_at) merepresentasikan baris pada setiap status. Versi partial hanya merepresentasikan baris pending. Ketika baris pending hanya merupakan sebagian kecil tabel, partial index dapat menggunakan ruang lebih sedikit dan menghindari pemeliharaan indeks untuk versi baris di luar predikatnya.

Sifat tersebut tidak otomatis membuat partial index lebih baik. Kegunaannya bergantung pada hubungan antara predikat, distribusi data, dan kondisi query yang benar-benar dijalankan. Jika hampir setiap baris memenuhi predikat, hanya sedikit cakupan yang dihilangkan. Jika query aplikasi menyatakan subset target dalam bentuk yang tidak dapat dihubungkan planner dengan predikat, indeks mungkin jarang digunakan.

Subset juga dapat berubah seiring waktu. Predikat yang dipilih berdasarkan distribusi data sementara dapat menjadi kurang cocok ketika isi tabel atau pola akses berubah. Partial index paling konsisten ketika predikat mencerminkan perbedaan yang bertahan lama dalam model data atau workload, bukan ambang yang hanya relevan sementara.

Keunikan parsial menggunakan aturan keanggotaan yang sama

Menambahkan UNIQUE membuat indeks menegakkan keunikan hanya di antara baris yang memenuhi predikatnya. Sebagai contoh, sistem dapat mengizinkan banyak record historis untuk sebuah akun sambil hanya mengizinkan satu record aktif dengan identifier eksternal tertentu:

CREATE UNIQUE INDEX account_active_external_id_idx
    ON account_records (external_id)
    WHERE active;

Baris dengan active bernilai true ikut dalam pemeriksaan keunikan. Baris lain tidak menjadi bagian dari indeks dan berada di luar constraint tersebut. Ini adalah mekanisme keanggotaan partial index yang sama dengan yang digunakan untuk akses query, dengan penegakan keunikan ditambahkan pada subset yang diindeks.

Karena itu, partial index lebih dari sekadar salinan lebih kecil dari full index. Predikatnya menjadi bagian dari semantik indeks: predikat menentukan versi baris yang menjadi anggota, query yang dapat mengandalkan struktur tersebut, dan—untuk unique partial index—baris yang ikut dalam constraint. Menjaga predikat tetap selaras dengan kondisi query yang stabil membuat struktur yang lebih sempit ini berguna.

PostgreSQL Database Indeks Perencanaan Query