Menu

CREATE INDEX PostgreSQL: Jenis Index dan Contohnya

CREATE INDEX secara default membuat index B-tree supaya PostgreSQL bisa menemukan baris tanpa membaca seluruh tabel. Jenis lain (GIN, GiST, BRIN, hash) mencakup JSONB, array, pencarian full-text, rentang, dan tabel yang sangat besar.

Halaman ini dilengkapi editor yang bisa dijalankan. Edit, jalankan, dan lihat output-nya seketika.

CREATE INDEX name ON table (column); membangun index yang memungkinkan PostgreSQL menemukan baris yang cocok tanpa membaca seluruh tabel. Jenis default-nya B-tree, yang melayani =, <, >, BETWEEN, IN, IS NULL, dan ORDER BY. Pilih jenis lain dengan USING: gin, gist, spgist, brin, atau hash.

Baris pertama rencananya berbunyi Index Scan using users_email_idx on users: PostgreSQL mencari nilainya di index dan hanya membaca baris yang cocok. Hapus baris CREATE INDEX lalu jalankan lagi untuk melihat Seq Scan on users, yang membaca seluruh 10.000 baris.

Di tabel berisi beberapa puluh baris, planner sering memilih sequential scan meskipun ada index: membaca beberapa page lebih murah. EXPLAIN ANALYZE menjelaskan cara membaca rencana ini.

Memilih jenis index

JenisPakai untuk
B-tree (default)kesamaan, rentang, pengurutan, LIKE 'prefix%' (dengan collation C atau text_pattern_ops)
GINnilai yang terdiri dari banyak bagian: jsonb (@>, ?), array (@>, &&), tsvector full-text
GiSTgeometri, rentang (&&, tumpang tindih), full-text, nearest-neighbor ORDER BY ... <->
SP-GiSTdata yang terbagi menjadi wilayah yang tidak tumpang tindih: titik, alamat IP, awalan teks
BRINtabel yang sangat besar yang nilainya mengikuti urutan fisik, seperti created_at yang hanya ditambah
Hashkesamaan saja; B-tree melakukan hal yang sama dan lebih, jadi jarang dipilih

Index GIN pada kolom jsonb membuat query containment memakai index:

Rencananya menampilkan Bitmap Index Scan pada index GIN, yang mengalirkan hasil ke Bitmap Heap Scan yang membaca baris yang cocok. Halaman JSONB membahas operatornya.

Index pada beberapa kolom

Index B-tree multi-kolom mengurutkan berdasarkan kolom pertama, lalu berdasarkan kolom kedua di dalam setiap nilai kolom pertama. Index ini melayani query yang memfilter kolom terdepan, atau kolom terdepan dan kolom-kolom berikutnya. Filter pada kolom kedua saja hampir tidak terbantu, jadi taruh kolom yang selalu kamu filter dengan = di urutan pertama.

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;

INCLUDE (PostgreSQL 11+) menyimpan kolom tambahan di index tanpa mengurutkannya: CREATE INDEX ON orders (customer_id) INCLUDE (total);. Query yang hanya membaca kolom-kolom itu lalu bisa dijawab dari index saja, yaitu Index Only Scan.

Unique dan partial index: menegakkan aturan pada sebagian baris

CREATE UNIQUE INDEX menolak nilai duplikat. Partial index menambahkan WHERE dan hanya meng-index baris yang cocok. Index ini lebih kecil, dan bisa membuat aturan keunikan hanya berlaku pada sebagian baris:

Kedua baris diterima karena hanya satu yang tidak dihapus. Baris aktif ketiga dengan email yang sama gagal dengan duplicate key value violates unique constraint "accounts_email_live". Query hanya memakai partial index saat WHERE-nya menyiratkan kondisi index itu, di sini WHERE NOT deleted.

Index pada ekspresi

Index bisa menyimpan hasil sebuah ekspresi. Query harus mengulang ekspresi yang sama untuk memakainya, dan index pada email tidak membantu WHERE lower(email) = ...:

Rencananya memakai users_email_lower_idx karena WHERE mengulang lower(email) persis. Ekspresinya harus immutable, jadi now() atau fungsi yang bergantung pada sebuah pengaturan akan ditolak.

Melihat, membangun, dan menghapus index di database yang sedang berjalan

pg_indexes menampilkan setiap index beserta pernyataan untuk membuatnya ulang:

Primary key dan constraint UNIQUE membuat index-nya sendiri, users_pkey dan users_email_key. Di psql, \d users menampilkan daftar yang sama di bawah kolom tabel.

Di tabel yang terus menerima penulisan, bangun dan hapus index dengan CONCURRENTLY supaya penulisan tidak terblokir. Bentuk ini tidak bisa berjalan di dalam blok transaksi. Pembangunan concurrent yang gagal meninggalkan index INVALID, yang harus kamu hapus lalu buat lagi:

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx;  -- rebuild a bloated index

Pertanyaan yang sering diajukan

Apakah PostgreSQL membuat index secara otomatis?

Hanya untuk constraint PRIMARY KEY, UNIQUE, dan exclusion. Kolom foreign key tidak mendapat index, jadi tambahkan sendiri saat kamu melakukan join pada kolom itu atau menghapus baris dari tabel yang dirujuk.

Kenapa PostgreSQL tidak memakai index-ku?

Alasan umumnya: tabelnya kecil, jadi membaca seluruhnya lebih murah; query mengembalikan sebagian besar baris; WHERE membungkus kolom dengan fungsi atau cast yang tidak ada di index; atau statistiknya sudah usang (jalankan ANALYZE). EXPLAIN menunjukkan rencana mana yang dipilih.

Berapa banyak index yang terlalu banyak?

Tidak ada angka pasti. Setiap index mempercepat sebagian pembacaan dan memperlambat setiap INSERT dan sebagian besar UPDATE pada tabel, serta memakan ruang disk. pg_stat_user_indexes.idx_scan menunjukkan seberapa sering setiap index dipakai, jadi kamu bisa menghapus index yang tetap di nol.

Apakah CREATE INDEX mengunci tabel?

CREATE INDEX biasa memblokir INSERT, UPDATE, dan DELETE pada tabel sampai selesai, meskipun pembacaan tetap berjalan. CREATE INDEX CONCURRENTLY mengizinkan penulisan selama index dibangun, memakan waktu lebih lama, dan tidak bisa berjalan di dalam blok transaksi.

Ilustrasi bahasa pemrograman di Coddy

Belajar coding dengan Coddy

Mulai