Selain B-tree default, PostgreSQL menawarkan beberapa tipe indeks khusus — GIN, GiST, BRIN, Hash — masing-masing cocok untuk pola data dan query yang berbeda, ditambah indeks partial dan expression untuk fleksibilitas. Memilih tipe indeks yang tepat dapat secara dramatis meningkatkan performa untuk query khusus.
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
B-tree adalah default dan menangani mayoritas kasus — equality, ranges, dan sorting pada kolom biasa.
-- GIN (Generalized Inverted Index) — for values CONTAINING multiple items
CREATE INDEX idx_attrs ON products USING GIN (attributes); -- JSONB
CREATE INDEX idx_tags ON posts USING GIN (tags); -- arrays
CREATE INDEX idx_search ON docs USING GIN (to_tsvector('english', body)); -- full-text
-- great for: JSONB containment, array membership, full-text search
GIN indexes sangat penting untuk JSONB, array, dan full-text search — query yang memeriksa containment atau membership.
-- GiST — geometric, range, nearest-neighbor (e.g. PostGIS, range types)
CREATE INDEX idx_geo ON places USING GiST (location);
-- BRIN (Block Range INdex) — tiny index for LARGE tables with naturally-ordered data
CREATE INDEX idx_time ON logs USING BRIN (created_at); -- great for huge time-series
-- tiny size, efficient for large append-only/ordered data
-- Hash — equality only (=); rarely needed (B-tree handles equality too)
CREATE INDEX idx_hash ON t USING HASH (col);
-- PARTIAL index — index only SOME rows (smaller, targeted)
CREATE INDEX idx_active ON users(email) WHERE active = true; -- only active users
-- EXPRESSION index — index a computed value
CREATE INDEX idx_lower_email ON users(LOWER(email)); -- for case-insensitive lookups
SELECT * FROM users WHERE LOWER(email) = '[email protected]'; -- uses the index
Partial indexes (indeks hanya baris relevan — lebih kecil, lebih cepat) dan expression indexes (indeks nilai yang dihitung — mengaktifkan query berbasis fungsi dengan indeks) adalah tools yang powerful dan fleksibel.
Memahami variasi tipe indeks PostgreSQL adalah pengetahuan berharga untuk optimasi performa, karena tipe indeks yang tepat untuk pola data dan query tertentu dapat secara dramatis mempercepat query yang ditangani B-tree default dengan buruk atau sama sekali tidak ditangani.
Meskipun B-tree (default) mencakup sebagian besar kasus (equality, ranges, sorting), tipe khusus membuka performa untuk data khusus: GIN sangat penting untuk JSONB, array, dan full-text search (query containment/membership — membuat fitur JSONB dan array powerful Postgres menjadi performan); GiST menangani query geometric, range, dan nearest-neighbor (PostGIS geospatial, range types); dan BRIN menyediakan indeks tiny dan efisien untuk tabel besar yang berurutan secara alami (seperti data time-series/append-only — pilihan berharga untuk tabel sangat besar di mana B-tree penuh akan mahal).
Mengetahui tipe indeks mana yang cocok untuk skenario mana (GIN untuk JSONB/array/full-text, GiST untuk geometric/range, BRIN untuk data besar yang terurut) adalah kunci untuk mengoptimalkan fitur advanced Postgres.
Sama berharganya adalah partial indexes (indeks hanya baris relevan — lebih kecil dan efisien ketika Anda query subset, seperti user aktif) dan expression indexes (indeks nilai yang dihitung — mengaktifkan fast function-based lookups seperti case-insensitive email search, yang plain index tidak dapat dukung).
Karena pilihan indeks secara signifikan mempengaruhi performa query, dan karena tipe indeks khusus Postgres (terutama GIN untuk JSONB/array/full-text) adalah yang membuat tipe data advancednya performan, memahami tipe indeks dan kapan menggunakan masing-masing — termasuk partial dan expression indexes — adalah pengetahuan berharga dan frequently-relevant untuk mengoptimalkan database PostgreSQL, membedakan developer yang dapat membuat Postgres cepat untuk diverse query patterns dan aspek kunci dari memanfaatkan full performance capabilities database.