15 Kasım 2026 PazarRSS

DevelopmentYazılım

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

  • EXPLAIN planı tahmin eder, EXPLAIN ANALYZE sorguyu 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 ... LIMIT iç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_statements eklentisini 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 ANALYZE sorguyu 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ı

  1. Execution Time gerçekten yüksek mi?
  2. Süreyi hangi düğüm tüketiyor? (actual time × loops)
  3. Tahmini ve gerçek rows arasında 10 kattan büyük fark var mı? Varsa planlayıcı yanlış bilgiyle karar veriyor.
  4. Buffers: read yü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_statements açı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 CONCURRENTLY ile oluşturuldu
  • Kullanılmayan büyük indeksler gözden geçirildi
  • Diske taşan sıralamalar için work_mem oturum 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ı

Benzer Yazılar