B-tree-oletuksen lisäksi PostgreSQL tarjoaa useita erikoistuneet indeksityyppejä — GIN, GiST, BRIN, Hash — joista kukin soveltuu eri tietoihin ja kyselymalleille, sekä osittaiset ja lauseke-indeksit joustavuudelle. Oikean indeksityypin valitseminen voi parantaa erikoistuneiden kyselyjen suorituskykyä dramaattisesti.
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
B-tree on oletusarvo ja käsittelee suurimman osan tapauksista — yhtäsuuruus, alueet ja lajittelu tavallisissa sarakkeissa.
-- 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-indeksit ovat välttämättömiä JSONB:lle, taulukoille ja täysiteksihaulle — kyselyille, jotka tarkistavat sisältymisen tai jäsenyuden.
-- 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
Osittaiset indeksit (indeksoi vain relevantit rivit — pienempi, nopeampi) ja lauseke-indeksit (indeksoi laskettu arvo — mahdollistaen indeksoidut funktiopohjaiset kyselyt) ovat voimakkaita, joustavia työkaluja.
PostgreSQL:n indeksityyppien moninaisuuden ymmärtäminen on arvokasta tietoa suorituskyvyn optimoinnille, koska oikea indeksityyppi tietylle datalle ja kyselymallille voi nopeuttaa kyselyitä dramaattisesti, joita oletusarvoinen B-tree käsittelee huonosti tai ei ollenkaan.
Vaikka B-tree (oletusarvo) kattaa useimmat tapaukset (yhtäsuuruus, alueet, lajittelu), erikoistuneet tyypit vapauttavat suorituskyvyn erikoistuneille tiedoille: GIN on välttämätön JSONB:lle, taulukoille ja täysiteksihaulle (sisältymis-/jäsenyyskyselyt — tekevät Postgresa:n tehokkaista JSONB:stä ja taulukkoominaisuuksista tehokkaita); GiST käsittelee geometrisia, alueita ja lähimmän naapurin kyselyitä (PostGIS-paikkatiedot, aluetyypit); ja BRIN tarjoaa pieniä, tehokkaita indeksejä suurille luonnollisesti järjestetyille taulukoille (kuten aikasarja/vain liitettävä data — arvokas vaihtoehto valtaville taulukoille, joissa koko B-tree olisi kallista).
Tietäminen, mikä indeksityyppi sopii mihin skenaarioon (GIN JSONB:lle/taulukoille/täystekstihaulle, GiST geometriselle/alueelle, BRIN suurelle järjestetylle datalle) on avain Postgresa:n kehittyneiden ominaisuuksien optimointiin.
Yhtä arvokkaat ovat osittaiset indeksit (indeksoidaan vain relevantit rivit — pienempi ja tehokkaampi, kun kysyt alijoukkoa, kuten aktiivisia käyttäjiä) ja lauseke-indeksit (indeksoidaan lasketut arvot — mahdollistaen nopeat funktiopohjaiset haut, kuten kirjainkokoon herkkä sähköpostihaku, mitä tavallinen indeksi ei voi tukea).
Koska indeksivalinta vaikuttaa merkittävästi kyselyn suorituskykyyn, ja koska Postgresa:n erikoistuneet indeksityypit (erityisesti GIN JSONB:lle/taulukoille/täystekstihaulle) ovat se, mikä tekee sen kehittyneistä tietotyypeistä tehokkaita, indeksityyppien ymmärtäminen ja kunkin käyttäminen — mukaan lukien osittaiset ja lauseke-indeksit — on arvokas, usein relevantti tieto PostgreSQL-tietokantojen optimointiin, erottaa kehittäjät, jotka voivat tehdä Postgresa nopeaksi erilaisille kyselymalleille ja keskeinen osa tietokannan täyden suorituskykykyvyn hyödyntämistä.