Šalia numatytojo B-tree, PostgreSQL siūlo kelis specializuotus indekso tipus — GIN, GiST, BRIN, Hash — kiekvienas tinkamas skirtingiems duomenims ir užklausų šablonams, plius daliniai ir išraiškiniai indeksai dėl lankstumo. Tinkamo indekso tipo pasirinkimas gali dramatiškai pagerintu našumą specializuotoms užklausoms.
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
B-tree yra numatytasis ir tvarko daugumą atvejų — lygybę, diapazonus ir rūšiavimą pagal įprastas stulpelius.
-- 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 indeksai yra būtini JSONB, masyvams ir pilnatekstei paieškai — užklausoms, kurios tikrina priklausomybę ar narystę.
-- 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
Daliniai indeksai (indeksuoti tik svarbias eilutes — mažesni, greitesni) ir išraiškiniai indeksai (indeksuoti apskaičiuotą reikšmę — įgalinant indeksuotus funkcijų pagrįstus užklausas) yra galingi, lanksčiai naudojami įrankiai.
PostgreSQL indekso tipų įvairovės supratimas yra vertinga žinias našumo optimizavimui, nes tinkamas konkretaus duomenų ir užklausų šablono indekso tipas gali dramatiškai pagreitinti užklausas, kurias numatytasis B-tree tvarko blogai ar visai netvarkc.
Nors B-tree (numatytasis) tvarko daugumą atvejų (lygybė, diapazonai, rūšiavimas), specializuoti tipai atrakina našumą specializuotiems duomenims: GIN yra būtinas JSONB, masyvams ir pilnatekstei paieškai (priklausomybės/narystės užklausoms — darydamas PostgreSQL galingus JSONB ir masyvo funkcijas produktyvius); GiST tvarko geometrines, diapazonų ir artimiausio kaimyno užklausas (PostGIS erdviniai, diapazonų tipai); ir BRIN suteikia mažus, efektyvius indeksus didelėms natūraliai sutvarkytoms lentelėms (pavyzdžiui, laiko serijoms/tik pridedamiems duomenims — vertinga galimybė didžiulėms lentelėms, kur pilnas B-tree būtų brangus).
Žinoti, kuris indekso tipas tinka kuriam scenarijui (GIN JSONB/masyvams/pilnatekstei, GiST geometrijoje/diapazonams, BRIN didelių sutvarkytų duomenų), yra raktas optimizuoti PostgreSQL pažangias funkcijas.
Taip pat vertingi yra daliniai indeksai (indeksuojant tik svarbias eilutes — mažesni ir efektyvesni kai užklausia tik dalis, kaip aktyvūs naudotojai) ir išraiškiniai indeksai (indeksuojant apskaičiuotas reikšmes — įgalinant greitą funkcijų pagrįstus paieškos, kaip didžiosios raidės neatsižvelgiantį el. pašto paiešką, kurią paprastas indeksas negali palaikyti).
Kadangi indekso pasirinkimas reikšmingai veikia užklausų našumą, ir kadangi PostgreSQL specializuoti indekso tipai (ypač GIN JSONB/masyvams/pilnatekstei) yra tai, kas daro jo pažangius duomenų tipus produktyvius, supratimas indekso tipų ir kada kiekvieno naudoti — įskaitant dalinius ir išraiškinius indeksus — yra vertinga, dažnai aktuali žinias PostgreSQL duomenų bazių optimizavimui, išskiriant kūrėjus, kurie gali padaryti PostgreSQL greitą įvairiausiems užklausų šablonams ir svarbi aspektu pilno duomenų bazės našumo galimybių išnaudojimui.