PostgreSQL dizinleri: B-tree, GIN ve ne zaman yardımcı olurlar

Kısaca: Dizin, sorguları hızlandıran ama yazmalara ve depolamaya mal olan ek bir arama ağacıdır; sık süzgeçler, sıralamalar ve join’ler için bilerek oluşturursun.

Biraz daha ayrıntılı: Dizin olmadan PostgreSQL bir sorgu için tüm tabloyu okur (seq scan). Bir dizin, kitaptaki konu dizini gibi satırları hızla bulur.

Ayrıntılı

Dizin türleri

• B-tree (varsayılan): eşitlik, aralıklar (<, >, BETWEEN), sıralama (ORDER BY), önek LIKE 'abc%'.
• Hash: yalnızca eşitlik.
• GIN: çok değerli veriler (diziler, JSONB, tam metin tsvector).
• GiST/SP-GiST: coğrafi veriler, aralıklar, benzerlik.
• BRIN: çok büyük, zamana göre sıralı tablolar, minik.
• pgvector (HNSW/IVFFlat): vektör arama.

CREATE INDEX idx_user_email ON users (email);
CREATE UNIQUE INDEX uq_user_email ON users (lower(email));
CREATE INDEX idx_order_customer_date ON orders (customer_id, created DESC);
CREATE INDEX idx_active ON users (id) WHERE active;       -- kısmi
CREATE INDEX idx_tags ON articles USING gin (tags);

Dizin ne zaman değer?

• WHERE, JOIN (yabancı anahtarlar!), ORDER BY içindeki sütunlar.
• Seçici koşullar (az isabet).
• Bileşik dizinler: sıra önemlidir; (customer_id, created), WHERE customer_id = … ve … AND created … ile yardımcı olur, yalnızca created üzerindeki bir süzgeçle olmaz (sol önek kuralı).
• Minik tablolarda, az değerli sütunlarda (evet/hayır) tek başına veya tablo sürekli yoğun yazılıyorsa dizin yok.
• Maliyet: her dizin INSERT/UPDATE/DELETE’i yavaşlatır, depolama ve bakım kullanır.

EXPLAIN ile denetim

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42 ORDER BY created DESC LIMIT 10;

Büyük bir tabloda Seq Scan = şüphe; Index Scan/Index Only Scan/Bitmap Heap Scan = dizin kullanıldı. Planlayıcı istatistiklere (ANALYZE) göre seçer; çok isabet olduğunda bazen seq scan daha hızlıdır. pg_stat_user_indexes hiç kullanılmayan dizinleri gösterir (sil). CREATE INDEX CONCURRENTLY uzun bir kilit olmadan oluşturur.

Tipik hatalar

Sütunlar üzerindeki işlevler (WHERE lower(email) = …) normal bir dizini engeller (→ işlev dizini). Tür dönüşümleri, LIKE’ta öndeki %, farklı sütunlar arasında OR. Bir yabancı anahtarda eksik dizin join’leri ve silmeleri yavaşlatır. Çok fazla dizin. Sunucusuz veri tabanlarında hesaplama süresi de sayılır: daha az seq scan = daha az CPU saati.

Ayrıca bak: PostgreSQL, Sorgu planlayıcı ve EXPLAIN, EXPLAIN ile sorguları optimize etmek, N+1 sorunu, Vektör veri tabanları