Oltre il B-tree predefinito, PostgreSQL offre diversi tipi di indici specializzati — GIN, GiST, BRIN, Hash — ognuno adatto a diversi dati e modelli di query, più indici parziali e su espressioni per flessibilità. Scegliere il tipo di indice giusto può migliorare drammaticamente le prestazioni per query specializzate.
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
B-tree è il predefinito e gestisce la maggior parte dei casi — uguaglianza, intervalli e ordinamento su colonne ordinarie.
-- 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
Gli indici GIN sono essenziali per JSONB, array e ricerca full-text — query che controllano contenimento o appartenenza.
-- 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
Gli indici parziali (indicizzare solo righe rilevanti — più piccoli e veloci) e gli indici su espressioni (indicizzare un valore calcolato — abilitare query indicizzate basate su funzioni) sono strumenti potenti e flessibili.
Comprendere la varietà di tipi di indici di PostgreSQL è una conoscenza preziosa per l'ottimizzazione delle prestazioni, perché il tipo di indice giusto per un dato e un modello di query specifici può accelerare drammaticamente le query che il B-tree predefinito gestisce male o per nulla.
Mentre il B-tree (il predefinito) copre la maggior parte dei casi (uguaglianza, intervalli, ordinamento), i tipi specializzati sbloccano prestazioni per dati specializzati: GIN è essenziale per JSONB, array e ricerca full-text (query di contenimento/appartenenza — rendendo performanti le potenti funzionalità JSONB e array di Postgres); GiST gestisce query geometriche, di intervallo e di nearest-neighbor (PostGIS geospaziale, tipi di intervallo); e BRIN fornisce indici minuscoli ed efficienti per grandi tabelle ordinate naturalmente (come dati time-series/append-only — un'opzione preziosa per tabelle enormi dove un B-tree completo sarebbe costoso).
Sapere quale tipo di indice si adatta a quale scenario (GIN per JSONB/array/full-text, GiST per geometrico/intervallo, BRIN per dati grandi ordinati) è fondamentale per ottimizzare le funzionalità avanzate di Postgres.
Equalmente preziosi sono gli indici parziali (indicizzare solo righe rilevanti — più piccoli ed efficienti quando si effettuano query su un sottoinsieme, come utenti attivi) e gli indici su espressioni (indicizzare valori calcolati — abilitando ricerche veloci basate su funzioni come ricerca email case-insensitive, che un indice semplice non può supportare).
Poiché la scelta dell'indice influenza significativamente le prestazioni delle query, e poiché i tipi di indice specializzati di Postgres (specialmente GIN per JSONB/array/full-text) sono ciò che rende performanti i suoi tipi di dati avanzati, comprendere i tipi di indici e quando usare ciascuno — inclusi gli indici parziali e su espressioni — è una conoscenza preziosa e frequentemente rilevante per ottimizzare i database PostgreSQL, distinguendo gli sviluppatori che possono rendere Postgres veloce per diversi modelli di query e un aspetto fondamentale dello sfruttamento delle piene capacità di prestazioni del database.