Ghid de Performanță și Indecși în PostgreSQL: B-Tree, GIN, GiST și BRIN în PostgreSQL

În PostgreSQL, performanța unei interogări este determinată în primul rând de capacitatea planificatorului de costuri de a accesa datele prin cel mai potrivit mecanism de indexare. Spre deosebire de alte sisteme de baze de date care se bazează aproape exclusiv pe arbori B-Tree, PostgreSQL dispune de motoare de indexare dedicate fiecărei clase de probleme: căutări pe documente JSONB, date spațiale, intervale temporale sau seturi masive de serii de timp.

-- Comanda standard blochează toate scrierile (INSERT, UPDATE, DELETE) pe tabel până la finalizare!
-- CREATE INDEX idx_comenzi_client ON comenzi(client_id); -- PERICOL DE DOWNTIME!

-- În producție, folosiți ÎNTOTDEAUNA clauza CONCURRENTLY:
CREATE INDEX CONCURRENTLY idx_comenzi_client ON comenzi(client_id);

-- Cum funcționează CONCURRENTLY:
-- 1. Nu obține blocaj exclusiv pe tabel (permite citiri și scrieri paralele).
-- 2. Efectuează două scanări complete de tabel și așteaptă finalizarea tranzacțiilor existente.
-- Notă: Durează mai mult timp și nu poate rula în interiorul unui bloc explicit BEGIN...COMMIT.

Indecși Parțiali și Indecși pe Expresii

Una dintre cele mai mari economii de spațiu și I/O în PostgreSQL se obține prin Indecși Parțiali. Dacă un tabel are 10 milioane de comenzi din care doar 5.000 sunt în starea procesare, nu are sens să indexăm toate cele 10 milioane de rânduri:

-- Indexăm exclusiv rândurile de interes critic:
CREATE INDEX CONCURRENTLY idx_comenzi_active 
ON comenzi(created_at) 
WHERE status IN ('in_asteptare', 'procesare');

-- Dimensiune: ~500 KB în loc de 350 MB!
-- Interogările care conțin exact condiția din WHERE vor folosi instantaneu indexul parțial:
SELECT * FROM comenzi WHERE status = 'procesare' ORDER BY created_at ASC;

Alegerea Tipului de Index Potrivit

Analiza Planurilor de Execuție cu BUFFERS

Comanda clasică EXPLAIN ANALYZE măsoară timpii de execuție, dar opțiunea BUFFERS este cea care dezvăluie cauza rădăcină a problemelor: consumul de memorie și accesul la disc.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM utilizatori WHERE email = 'contact@dexblog.ro';

-- Exemplu rezultat generat:
-- Index Scan using idx_utilizatori_email on utilizatori  (cost=0.42..8.44 rows=1 width=128)
--                                                        (actual time=0.035..0.037 rows=1 loops=1)
--   Index Cond: ((email)::text = 'contact@dexblog.ro'::text)
--   Buffers: shared hit=4 read=0 dirtied=0
-- Planning Time: 0.112 ms
-- Execution Time: 0.058 ms

Indicatori esențiali din secțiunea Buffers:

• shared hit=N: Pagini de memorie (8 KB per pagină) găsite direct în memoria cache shared_buffers a PostgreSQL. Acces ultra-rapid la nivel de nanosecunde.
• shared read=N: Pagini care NU au fost găsite în cache și au necesitat o citire fizică din cache-ul sistemului de operare sau direct de pe SSD. Dacă această valoare este mare, creșteți shared_buffers sau optimizați indexul!
• Tipuri de scanare: Ierarhia de viteză este: Index Only Scan (datele sunt luate exclusiv din index) > Index Scan (index + salt în tabel) > Bitmap Index Scan (index + sortare pagini) > Seq Scan (scanare completă de tabel).

Detectarea Indecșilor Neutilizați (Index Bloat & Waste)

Fiecare index încetinește operațiunile de INSERT, UPDATE și DELETE. Rulați periodic această interogare pentru a identifica indecșii care ocupă spațiu pe disc dar nu au fost folosiți niciodată de optimizator:

SELECT 
    schemaname || '.' || relname AS tabel,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(indexrelid)) AS marime,
    idx_scan AS numar_scanari
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indisunique IS FALSE
ORDER BY pg_relation_size(indexrelid) DESC;