Preko zadane B-tree, PostgreSQL nudi nekoliko specijaliziranih vrsta indeksa — GIN, GiST, BRIN, Hash — svaka prikladna za različite podatke i obrasce upita, plus parcijalni i indeksi izraza za fleksibilnost. Odabir prave vrste indeksa može dramatično poboljšati performanse za specijalizirane upite.
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
B-tree je zadana vrsta i obavlja većinu slučajeva — jednakost, raspone i sortiranje na običnim stupcima.
-- 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 indeksi su neophodni za JSONB, nizu i pretraživanje punog teksta — upiti koji provjeravaju sadržavanje ili pripadnost.
-- 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
Parcijalni indeksi (indeksiraju samo relevantne redove — manji, brži) i indeksi izraza (indeksiraju računatu vrijednost — omogućavajući indeksirane upite temeljene na funkcijama) su moćni, fleksibilni alati.
Razumijevanje raznolikosti vrsta indeksa PostgreSQL-a je vrijedna znanja za optimizaciju performansi, jer je prava vrsta indeksa za dane podatke i obraz upita može dramatično ubrzati upite koje zadana B-tree loše obrađuje ili uopće ne obrađuje.
Dok B-tree (zadana) pokriva većinu slučajeva (jednakost, raspone, sortiranje), specijalizirane vrste otključavaju performanse za specijalizirane podatke: GIN je neophodan za JSONB, nizu i pretraživanje punog teksta (upiti sadržavanja/pripadnosti — čineći Postgresove moćne JSONB i značajke niza performantnim); GiST obrađuje geometrijske, rasponske i upite najbližeg susjeda (PostGIS geoprostorni, vrste raspona); i BRIN pruža sitne, učinkovite indekse za velike prirodno uređene tablice (poput vremenskih serija/samo-dodaj podatke — vrijedna opcija za ogromne tablice gdje bi puni B-tree bio skup).
Znanje koja vrsta indeksa odgovara kojem scenariju (GIN za JSONB/nizu/pretraživanje punog teksta, GiST za geometrijske/rasponske, BRIN za velike uređene podatke) je ključno za optimizaciju naprednih značajki PostgreSQL-a.
Jednako vrijedni su parcijalni indeksi (indeksiranje samo relevantnih redova — manji i učinkovitiji kada ispitujete podskup, kao aktivne korisnike) i indeksi izraza (indeksiranje računatih vrijednosti — omogućavajući brze upite temeljene na funkcijama kao što je pretraživanje e-pošte neovisno od veličine slova, što obični indeks ne može podržati).
Budući da izbor indeksa značajno utječe na performanse upita, i budući da su specijalizirane vrste indeksa PostgreSQL-a (posebno GIN za JSONB/nizu/pretraživanje punog teksta) ono što čini njegove napredne vrste podataka performantnima, razumijevanje vrsta indeksa i kada koristiti svaku — uključujući parcijalne i indekse izraza — je vrijedna, često relevantna znanja za optimizaciju baza podataka PostgreSQL-a, razlikuje razvijatelje koji mogu učiniti PostgreSQL brz za raznolike obrasce upita i ključan je aspekt iskorištavanja punih mogućnosti performansi baze podataka.