Bits of .NET

📊 Index Types = Query Speed

Doğru index hayat kurtarır. Index types ile sorgu performansını optimize edin.

🎯 Index Türleri

# 1. B-Tree Index (Default)
CREATE INDEX idx_users_email ON users(email);

# 2. Unique Index
CREATE UNIQUE INDEX idx_users_username ON users(username);

# 3. Composite Index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

# 4. Partial Index
CREATE INDEX idx_orders_active ON orders(order_date)
WHERE status = 'active';

# 5. Descending Index
CREATE INDEX idx_orders_date_desc ON orders(order_date DESC);

# 6. Covering Index
CREATE INDEX idx_orders_covering ON orders(customer_id) 
INCLUDE (total, order_date);

# 7. Hash Index (PostgreSQL)
CREATE INDEX idx_users_email_hash ON users USING HASH(email);

# 8. GIN Index (Full-Text Search)
CREATE INDEX idx_posts_content ON posts USING GIN(to_tsvector('english', content));

# 9. Index Usage Analysis
SELECT 
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

✅ Index Optimization İpuçları

• ✓ WHERE clause’daki kolonlar
• ✓ JOIN kolonları
• ✓ ORDER BY kolonları
• ✓ Sık kullanılan sorgular
• ✓ Kullanılmayan index’leri temizle