Poleg privzetega B-tree, PostgreSQL ponuja več specializiranih vrst indeksov — GIN, GiST, BRIN, Hash — vsaka primerna za različne podatke in vzorce poizvedb, ter delne in indekse izrazov za fleksibilnost. Izbira prave vrste indeksa lahko dramatično izboljša zmogljivost za specializirane poizvedbe.
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
B-tree je privzeta vrsta in obravnava večino primerov — enakost, obsege in sortiranje na običajnih stolpcih.
-- 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 so bistveni za JSONB, polja in polnotekstno iskanje — poizvedbe, ki preverjajo vsebovanje ali 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
Delni indeksi (indeksirajo samo relevantne vrstice — manjši, hitrejši) in indeksi izrazov (indeksirajo izračunano vrednost — omogočajo indeksirane poizvedbe na osnovi funkcij) so zmogljiva in fleksibilna orodja.
Razumevanje različnih vrst indeksov PostgreSQL-a je dragoceno znanje za optimizacijo zmogljivosti, ker je prava vrsta indeksa za dane podatke in vzorec poizvedb lahko drastično pohitri poizvedbe, ki jih privzeti B-tree slabo obravnava ali jih sploh ne obravnava.
Čeprav B-tree (privzeta vrsta) pokriva večino primerov (enakost, obsege, sortiranje), specializirane vrste odklenejo zmogljivost za posebne podatke: GIN je bistven za JSONB, polja in polnotekstno iskanje (poizvedbe vsebovanja/pripadnosti — zmogljivi JSONB in funkcije polja PostgreSQL-a); GiST obravnava geometrijske, obsežne in poizvedbe najbližjega soseda (PostGIS geoprostorski, vrste obsegov); in BRIN zagotavlja majhne, učinkovite indekse za velika naravno razvrščena tabele (kot so časovne vrste/samo dodaj podatke — dragocena možnost za ogromne tabele, pri katerih bi bil polni B-tree dragi).
Znanje, katera vrsta indeksa se ujema s katerim scenarijem (GIN za JSONB/polja/polnotekstno iskanje, GiST za geometrijsko/obsežno, BRIN za velike urejene podatke), je ključno za optimizacijo naprednih funkcij PostgreSQL-a.
Enako dragocene so delni indeksi (indeksiranje samo relevantnih vrstic — manjši in učinkovitejši, kadar si poizvedi podmnožico, kot so aktivni uporabniki) in indeksi izrazov (indeksiranje izračunanih vrednosti — omogočanje hitrih iskanj na osnovi funkcij, kot je iskanje elektronske pošte brez razlikovanja malih in velikih črk, kar navaden indeks ne more podpirati).
Ker izbira indeksa bistveno vpliva na zmogljivost poizvedb, in ker so posebne vrste indeksov PostgreSQL-a (zlasti GIN za JSONB/polja/polnotekstno iskanje) tisto, kar njegove napredne vrste podatkov zmogljive, razumevanje vrst indeksov in kdaj uporabiti vsako — vključno s parcialnimi in indeksi izrazov — dragoceno in pogosto relevantno znanje za optimizacijo baz podatkov PostgreSQL, kar ločuje razvijalce, ki lahko naredijo PostgreSQL hiter za različne vzorce poizvedb in ključen vidik izkoriščanja polnih zmogljivosti baze podatkov.