PostgreSQL EXPLAIN ANALYZE ve İndeks Rehberi
Aziz Özdemiroğlu

Uygulamanız yavaşladığında suçlu çoğu zaman tek bir sorgudur, ve o sorgunun neden yavaş olduğunu PostgreSQL size zaten söyler: EXPLAIN ANALYZE. Bu rehberde sorgu planlarını okumayı, en yaygın darboğazları tanımayı, doğru indeksi seçmeyi ve yavaş sorguları sistematik olarak bulmayı gerçek örneklerle anlatıyoruz.
Kısaca
EXPLAINplanı tahmin eder,EXPLAIN ANALYZEsorguyu gerçekten çalıştırır ve ölçer. Üretimde yazma sorgularını bir işlem (transaction) içinde çalıştırıp geri alın.- Plan okurken üç şeye bakın: en pahalı düğüm, tahmini ve gerçek satır sayısı farkı, Buffers (okunan sayfa sayısı).
- En yaygın sorunlar: indeks olmadan büyük tabloda
Seq Scan, yanlış satır tahmini,ORDER BY ... LIMITiçin uygun indeks eksikliği, fonksiyon uygulanan sütunlar. - Bileşik indekslerde sütun sırası önemli: eşitlik koşulları önce, aralık ve sıralama sonra.
- Yavaş sorguları bulmak için
pg_stat_statementseklentisini açın.
EXPLAIN ve EXPLAIN ANALYZE
-- Yalnızca plan (sorgu çalışmaz)
EXPLAIN SELECT * FROM posts WHERE status = 'PUBLISHED' ORDER BY published_at DESC LIMIT 10;
-- Gerçek çalıştırma + zaman + okunan sayfalar
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM posts WHERE status = 'PUBLISHED' ORDER BY published_at DESC LIMIT 10;
Dikkat:
EXPLAIN ANALYZEsorguyu gerçekten çalıştırır.UPDATE/DELETEölçerken:BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE posts SET title = title WHERE id = 42; ROLLBACK;
Bir planı okumak
Örnek çıktı:
Limit (cost=0.42..1.95 rows=10 width=1200) (actual time=0.031..0.058 rows=10 loops=1)
Buffers: shared hit=14
-> Index Scan Backward using posts_status_published_at_idx on posts
(cost=0.42..1123.80 rows=7370 width=1200) (actual time=0.030..0.055 rows=10 loops=1)
Index Cond: (status = 'PUBLISHED'::"PostStatus")
Buffers: shared hit=14
Planning Time: 0.210 ms
Execution Time: 0.081 ms
| Alan | Anlamı |
|---|---|
cost=başlangıç..toplam |
Planlayıcının birimsiz maliyet tahmini |
rows= (cost içinde) |
Tahmini satır sayısı |
actual time=.. |
Gerçek süre (ms), düğüm başına |
rows= (actual içinde) |
Gerçek satır sayısı |
loops= |
Düğümün kaç kez çalıştığı (süre ve satırları bununla çarpın) |
Buffers: shared hit/read |
Önbellekten (hit) ve diskten (read) okunan 8 KB'lık sayfalar |
Planlar içten dışa okunur: en içteki (en çok girintili) düğüm ilk çalışır.
Okuma sırası
Execution Timegerçekten yüksek mi?- Süreyi hangi düğüm tüketiyor? (
actual time×loops) - Tahmini ve gerçek
rowsarasında 10 kattan büyük fark var mı? Varsa planlayıcı yanlış bilgiyle karar veriyor. Buffers: readyüksek mi? Disk okuması ağırlıklı demektir.
Düğüm türleri
| Düğüm | Ne zaman iyi | Ne zaman sorun |
|---|---|---|
Seq Scan |
Küçük tablolar, satırların büyük kısmı okunuyorsa | Büyük tabloda az satır dönüyorsa |
Index Scan |
Seçici koşullar, sıralı okuma | Çok satır dönüyorsa (rastgele okuma pahalı) |
Index Only Scan |
Gereken tüm sütunlar indekste | Heap Fetches yüksekse (VACUUM gerekli) |
Bitmap Heap Scan |
Orta seçicilik, birden çok koşul | — |
Nested Loop |
İç taraf küçük ve indeksli | Dış taraf büyük, iç taraf indekssiz |
Hash Join |
Büyük ve sırasız birleştirmeler | Hash belleğe sığmıyorsa (Batches > 1) |
Sort |
— | Sort Method: external merge Disk → bellek yetersiz |
Yaygın sorunlar ve çözümleri
1. Büyük tabloda Seq Scan
EXPLAIN ANALYZE SELECT * FROM comments WHERE post_id = 1234;
-- Seq Scan on comments ... rows=12 ... Rows Removed by Filter: 480000
Rows Removed by Filter çok yüksekse indeks eksik:
CREATE INDEX CONCURRENTLY comments_post_id_idx ON comments (post_id);
Üretimde CONCURRENTLY kullanın; tabloyu yazmaya kilitlemez (ama işlem içinde çalıştırılamaz ve daha uzun sürer).
2. ORDER BY + LIMIT
"En yeni 10 yayınlanmış yazı" gibi sorgularda doğru bileşik indeks, sıralamayı tamamen ortadan kaldırır:
CREATE INDEX CONCURRENTLY posts_status_published_idx ON posts (status, published_at DESC);
Sütun sırası kuralı: önce eşitlik koşulları (status = ...), sonra aralık veya sıralama sütunu (published_at).
3. Fonksiyon uygulanmış sütun
-- İndeks kullanılamaz: sütuna fonksiyon uygulanmış
SELECT * FROM users WHERE lower(email) = '[email protected]';
-- Çözüm: ifade (expression) indeksi
CREATE INDEX CONCURRENTLY users_lower_email_idx ON users (lower(email));
4. Yanlış satır tahmini
Tahmin ile gerçek arasında büyük fark varsa:
ANALYZE posts; -- istatistikleri yenile
ALTER TABLE posts ALTER COLUMN status SET STATISTICS 500; -- dağılımı çarpık sütunlarda daha ayrıntılı istatistik
ANALYZE posts;
İlişkili sütunlarda (örneğin şehir ve posta kodu) çok sütunlu istatistik yardımcı olur:
CREATE STATISTICS adres_istatistik (dependencies) ON city, postal_code FROM addresses;
ANALYZE addresses;
5. Diske taşan sıralama
Sort Method: external merge Disk: 52000kB görüyorsanız, oturum için work_mem değerini artırmayı deneyin:
SET work_mem = '64MB'; -- yalnızca bu oturum; global değeri dikkatle değiştirin
work_mem, her sıralama ve hash işlemi başına ayrılır. Global değeri büyütmek, eşzamanlı sorgularda bellek tüketimini katlar.
Kısmi ve kapsayan indeksler
-- Kısmi indeks: yalnızca yayınlanmış yazılar (taslaklar indekse girmez, indeks küçülür)
CREATE INDEX CONCURRENTLY posts_published_only_idx ON posts (published_at DESC)
WHERE status = 'PUBLISHED';
-- Kapsayan indeks: Index Only Scan için ek sütunlar
CREATE INDEX CONCURRENTLY posts_slug_cover_idx ON posts (slug) INCLUDE (title, featured_image);
Yavaş sorguları bulmak: pg_stat_statements
postgresql.conf içinde shared_preload_libraries = 'pg_stat_statements' ayarlayıp sunucuyu yeniden başlatın, sonra:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT round(total_exec_time::numeric, 0) AS toplam_ms,
calls,
round(mean_exec_time::numeric, 2) AS ort_ms,
rows,
left(query, 120) AS sorgu
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;
Toplam süreye göre sıralamak önemli. Ortalama 5 ms süren ama günde bir milyon kez çalışan sorgu, ortalama 2 saniye süren ve günde bir kez çalışan sorgudan daha büyük yüktür.
Kullanılmayan indeksleri bulmak
Her indeks yazmaları yavaşlatır ve disk kaplar:
SELECT relname AS tablo, indexrelname AS indeks, idx_scan AS kullanim,
pg_size_pretty(pg_relation_size(indexrelid)) AS boyut
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
Silmeden önce: istatistiklerin ne zaman sıfırlandığını kontrol edin, replikalardaki kullanımı da hesaba katın ve benzersizlik (UNIQUE) kısıtlamasını destekleyen indeksleri silmeyin.
Kontrol listesi
-
pg_stat_statementsaçık, en pahalı 15 sorgu biliniyor - Yavaş sorgular
EXPLAIN (ANALYZE, BUFFERS)ile incelendi - Tahmini ve gerçek satır farkı büyükse
ANALYZE/ istatistik hedefi ayarlandı - Bileşik indekslerde eşitlik → aralık/sıralama sütun sırası uygulandı
- Yeni indeksler
CREATE INDEX CONCURRENTLYile oluşturuldu - Kullanılmayan büyük indeksler gözden geçirildi
- Diske taşan sıralamalar için
work_memoturum bazında ayarlandı
Sonuç
PostgreSQL performans sorunlarının çoğu sihir değil, okunabilir bir plandır. EXPLAIN ANALYZE çıktısında en pahalı düğümü bulun, tahmini ve gerçek satır sayılarını karşılaştırın, sonra doğru indeksi ekleyin. Bu döngüyü pg_stat_statements ile bulduğunuz en pahalı sorgulara uyguladığınızda, genellikle birkaç indeksle uygulamanızın hissedilen hızı belirgin şekilde artar.
Kaynaklar
#PostgreSQL#EXPLAIN ANALYZE#İndeks#Sorgu Optimizasyonu#pg_stat_statements#Veritabanı




