EXPLAIN ANALYZE, PostgreSQL’in bir sorguyu gerçekten çalıştırıp hangi yolla çalıştırdığını ve her adımda ne kadar zaman harcadığını gösteren komuttur. Yavaş bir sorgunun neden yavaş olduğunu tahmin etmek yerine ölçmenin en doğrudan yolu budur. Çıktı ilk bakışta karmaşık görünse de birkaç temel kavramı bildiğinizde, sorunun nerede olduğunu çoğu zaman birkaç dakikada bulabilirsiniz.
Bu yazıda bir planı nasıl okuduğumu, hangi satırlara önce baktığımı ve en sık karşılaştığım yanlış okumaları anlatacağım. Örnekler PostgreSQL 16 ve 17 üzerinden ama mantık eski sürümlerde de aynı.
EXPLAIN ile EXPLAIN ANALYZE arasındaki fark nedir?
Düz EXPLAIN sorguyu çalıştırmaz. Planlayıcının seçtiği planı ve tahmin ettiği maliyetleri gösterir. Hızlıdır ve production’da güvenle çalıştırılabilir, ama sadece bir tahmindir.
EXPLAIN ANALYZE ise sorguyu gerçekten çalıştırır. Her plan düğümü için gerçek süreyi, gerçek satır sayısını ve kaç kez çalıştığını ekler. Tahmin ile gerçeğin yan yana durması, bu komutu değerli yapan şeydir.
Bunun önemli bir sonucu var: EXPLAIN ANALYZE bir UPDATE veya DELETE sorgusunu da gerçekten çalıştırır. Yazma sorgularını incelerken transaction içinde çalıştırıp geri almak gerekir:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'X' WHERE ...;
ROLLBACK;
Ben neredeyse her zaman BUFFERS seçeneğini de ekliyorum. Sorgunun kaç sayfa okuduğunu ve bunların bellekten mi diskten mi geldiğini gösteriyor. Süre makineye ve o anki yüke göre değişir, okunan sayfa sayısı ise çok daha tutarlı bir ölçüdür.
Plan ağacı nasıl okunur?
Bir plan, iç içe geçmiş düğümlerden oluşan bir ağaçtır. Her düğüm bir işlemdir: tablo taramak, index’ten okumak, iki sonucu birleştirmek, sıralamak.
Limit (cost=0.43..12.87 rows=20 width=64) (actual time=0.041..0.212 rows=20 loops=1)
-> Index Scan using idx_orders_customer_created on orders
(cost=0.43..1854.20 rows=2980 width=64)
(actual time=0.039..0.204 rows=20 loops=1)
Index Cond: (customer_id = 42)
Planning Time: 0.180 ms
Execution Time: 0.240 ms
Okuma yönü içeriden dışarıya doğrudur. En içteki düğüm önce çalışır ve sonucunu bir üstteki düğüme verir. Yukarıdaki örnekte index taraması satırları üretir, Limit ilk 20 satırı aldıktan sonra durur.
Her düğümde iki parantez grubu var. İlki planlayıcının tahmini, ikincisi gerçek ölçüm.
| Alan | Anlamı |
|---|---|
| cost=0.43..12.87 | Başlangıç ve toplam tahmini maliyet, birimsiz |
| rows=20 (ilk grup) | Planlayıcının beklediği satır sayısı |
| actual time=0.041..0.212 | İlk satıra ve son satıra kadar geçen gerçek süre, ms |
| rows=20 (ikinci grup) | Gerçekte üretilen satır sayısı |
| loops=1 | Düğümün kaç kez çalıştığı |
cost değeri milisaniye değildir. Planlayıcının farklı planları karşılaştırmak için kullandığı göreli bir sayıdır. İki farklı sorgunun maliyetini birbiriyle karşılaştırmak anlamlı değildir.
loops neden önemli?
actual time ve rows değerleri bir döngü başınadır. Bir düğüm 5.000 kez çalıştıysa, gerçek toplam süre gösterilen sürenin 5.000 katıdır. İç içe döngü (nested loop) birleşimlerinde bu detay atlandığında, en pahalı düğüm masum görünebilir.
0.05 ms süren ama 40.000 kez çalışan bir index taraması, toplamda 2 saniye eder. Planın en yavaş kısmı odur, ama satırdaki rakam bunu ilk bakışta göstermez.
Önce hangi satırlara bakmalı?
Uzun bir planda kaybolmamak için kendime sırayla şu soruları soruyorum.
Zaman nerede harcanıyor? Her düğümün actual time değerinin bitiş kısmına bakıp, alt düğümlerin süresini çıkarınca o düğümün kendi süresi bulunur. Toplam sürenin büyük kısmı genellikle bir veya iki düğümde toplanır. Optimizasyonu oraya yapmak gerekir.
Tahmin ile gerçek ne kadar farklı? rows=12 tahmin edilip rows=184000 gerçekleştiyse, planlayıcı yanlış bilgiyle karar vermiş demektir. Yanlış tahmin, yanlış join yöntemi ve yanlış sıralama stratejisi seçilmesinin en yaygın sebebidir.
Gereksiz satır okunuyor mu? Rows Removed by Filter değeri yüksekse, sorgu ihtiyacından çok daha fazla satır okuyup sonra çoğunu atıyor demektir. Bu genellikle eksik veya yanlış sıralanmış bir index işaretidir.
Bellek yetiyor mu? Sort ve hash düğümlerinde external merge veya disk kullanımı görüyorsanız, işlem work_mem sınırına sığmamış ve diske taşmıştır.
Seq Scan her zaman kötü müdür?
Hayır. Bu, en sık gördüğüm yanlış okumalardan biri.
Küçük bir tabloda veya tablonun büyük kısmını döndüren bir sorguda sıralı tarama, index taramasından daha hızlıdır. Index üzerinden her satıra ayrı ayrı gitmek, tabloyu baştan sona okumaktan daha fazla rastgele disk erişimi gerektirir.
Seq Scan’in problem olduğu durum şudur: büyük bir tabloda çok az satır döndüren bir sorgu. 10 milyon satırlık bir tablodan 20 satır almak için bütün tabloyu okumak, neredeyse her zaman eksik bir index’e işaret eder.
Hangi index’in doğru olduğu ise ayrı bir konu. Çok kolonlu sorgularda kolon sırasının neden belirleyici olduğunu composite index’te kolon sırası yazısında ayrıntılı anlatmıştım.
Satır tahmini neden yanlış çıkar?
Planlayıcı kararlarını tablo istatistiklerine göre verir. Bu istatistikler ANALYZE komutuyla, genellikle autovacuum tarafından otomatik olarak toplanır.
Tahminin bozulmasının en yaygın sebepleri:
- İstatistikler güncel değil. Büyük bir veri yüklemesinden sonra
ANALYZEçalışmadıysa planlayıcı eski dağılıma göre karar verir. - Kolonlar arasında ilişki var.
city = 'İstanbul' AND district = 'Kadıköy'koşulunda planlayıcı iki koşulu bağımsız sayar ve sonucu çok düşük tahmin eder.CREATE STATISTICSile çok kolonlu istatistik tanımlamak bu durumu düzeltir. - Fonksiyon veya ifade üzerine koşul var.
lower(email) = ...gibi ifadelerde ifade index’i yoksa planlayıcının dağılım bilgisi zayıftır. - Çok çarpık veri dağılımı var. Bir müşterinin siparişleri tablonun yarısını oluşturuyorsa, ortalamaya göre yapılan tahmin o müşteri için tamamen yanlıştır.
Tahmin hatası küçük bir detay gibi görünür ama planın geri kalanını belirler. Planlayıcı 10 satır bekliyorsa nested loop seçer. Gerçekte 200.000 satır gelirse, iç döngü 200.000 kez çalışır.
Join yöntemleri ne anlatır?
PostgreSQL iki tabloyu üç farklı yolla birleştirebilir. Hangisinin seçildiği, tahmini satır sayısına bağlıdır.
| Yöntem | Ne zaman iyi | Dikkat edilecek |
|---|---|---|
| Nested Loop | Dış tarafta az satır, iç tarafta index varsa | Dış taraf beklenenden büyükse çok yavaşlar |
| Hash Join | Orta ve büyük tablolar, eşitlik koşulu | Hash tablosu work_mem’e sığmazsa diske taşar |
| Merge Join | İki taraf da birleşim kolonuna göre sıralıysa | Sıralama gerekiyorsa ek maliyet |
ORM kullanılan projelerde sık gördüğüm bir durum, tek bir sorgu yerine yüzlerce küçük sorgunun çalışmasıdır. Bu durumda EXPLAIN her sorguyu ayrı ayrı hızlı gösterir, problem ise sorgu sayısındadır. Bu konuyu ORM’lerde N+1 sorgu problemi yazısında anlatmıştım.
BUFFERS çıktısı neden süreden daha güvenilir?
BUFFERS seçeneği her düğüme şuna benzer bir satır ekler:
Buffers: shared hit=1204 read=8811
hit, bellekteki shared buffer’lardan okunan sayfa sayısıdır. read, diskten veya işletim sistemi önbelleğinden okunan sayfa sayısıdır. Her sayfa 8 KB.
Aynı sorguyu iki kez çalıştırdığınızda ikincisi çok daha hızlı çıkabilir, çünkü sayfalar artık bellektedir. Süreye bakarak “sorunu çözdüm” demek kolaydır. Okunan sayfa sayısı ise önbellekten bağımsız olarak sorgunun ne kadar iş yaptığını gösterir. 9.000 sayfa okuyan bir sorguyu 50 sayfa okuyan hale getirdiyseniz, gerçekten bir şey düzelmiştir.
Yoğun güncellenen tablolarda okunan sayfa sayısı, satır sayısına göre beklenenden çok yüksekse tablo şişmiş olabilir. Ölü satırlar da okunur. Bu durumun sebeplerini PostgreSQL’de tablo şişmesi yazısında anlatmıştım.
Gerçek bir örnek: sıralama tuzağı
Bir sipariş listesi endpoint’i yavaştı. Sorgu basitti:
SELECT * FROM orders
WHERE status = 'PENDING'
ORDER BY created_at DESC
LIMIT 50;
Planda şu vardı:
Limit (actual time=1840.2..1840.3 rows=50 loops=1)
-> Sort (actual time=1840.2..1840.2 rows=50 loops=1)
Sort Method: top-N heapsort Memory: 41kB
-> Seq Scan on orders (actual time=0.02..1702.1 rows=184220 loops=1)
Filter: (status = 'PENDING')
Rows Removed by Filter: 9815780
Okuması kolay: 10 milyon satır okunmuş, 9,8 milyonu atılmış, kalan 184 bin satır sıralanmış ve ilk 50’si alınmış. Toplam sürenin neredeyse tamamı sıralı taramada.
(status, created_at DESC) üzerine bir composite index eklendikten sonra plan şuna dönüştü:
Limit (actual time=0.05..0.31 rows=50 loops=1)
-> Index Scan using idx_orders_status_created on orders
(actual time=0.05..0.30 rows=50 loops=1)
Sıralama adımı kayboldu, çünkü index satırları zaten istenen sırada veriyor. Sorgu 50 satır okuyup duruyor. 1,8 saniyeden 0,3 milisaniyeye.
Bu tür liste sorgularında sayfa ilerledikçe ayrı bir problem başlar. Büyük offset değerlerinin neden yavaşladığını offset ve keyset pagination yazısında ele almıştım.
Planı okurken sık yapılan hatalar
Yıllar içinde kendimin ve ekip arkadaşlarımın yaptığı birkaç hatayı not etmiştim:
- Geliştirme ortamındaki küçük veriyle alınan planı production’a genellemek. 1.000 satırlık tabloda planlayıcı bambaşka bir yol seçer.
costdeğerini milisaniye sanmak.loopsdeğerini görmeden düğüm süresini toplam süre sanmak.- Önbellek etkisini hesaba katmadan, ikinci çalıştırmanın hızına güvenmek.
- Parametreli sorgularda generic plan ile custom plan farkını unutmak. Uygulamanın gönderdiği prepared statement, elle yazılan sorgudan farklı bir plan kullanabilir.
Son madde özellikle sinsi. Uygulamada yavaş olan sorgu, psql’de elle çalıştırıldığında hızlı görünebilir. Böyle bir durumda auto_explain eklentisini kısa süreliğine açıp uygulamanın gerçekten kullandığı planı loglamak, tahmin yürütmekten çok daha hızlı sonuç verir.
Plan okumayı kolaylaştıran araçlar
Uzun planları metin olarak okumak yorucu olabilir. EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) çıktısını görselleştiren araçlar, en pahalı düğümleri renklerle işaretleyerek işi kolaylaştırıyor. explain.dalibo.com ve explain.depesz.com bu amaçla sık kullanılan iki araç.
Hangi sorguların incelenmeye değer olduğunu bulmak ise ayrı bir adım. Bütün sorguların toplam süresini ve çağrılma sayısını gösteren pg_stat_statements eklentisi, nereden başlanacağını belirlemek için en iyi başlangıç noktası.
PostgreSQL’in resmi dokümantasyonundaki EXPLAIN kullanımı bölümü, buradaki kavramların tamamını örneklerle anlatıyor. Plan okumayı ilk kez öğrenecek birine önerdiğim ilk kaynak bu.
Özet: EXPLAIN ANALYZE okurken kontrol listesi
ANALYZEveBUFFERSile çalıştır. Yazma sorgularında transaction içinde dene ve geri al.- Zamanın toplandığı düğümü bul.
loopsile çarpmayı unutma. - Tahmini ve gerçek satır sayısını karşılaştır. Büyük fark varsa önce istatistiklere bak.
Rows Removed by Filteryüksekse index’i sorguya göre yeniden düşün.- Ayrı bir
Sortdüğümü varsa, sıralamayı karşılayan bir index’in mümkün olup olmadığına bak. - Süre yerine okunan sayfa sayısıyla karşılaştırma yap.
Bir sorguyu hızlandırmak çoğu zaman yeni bir donanım veya daha büyük bir connection pool gerektirmez. Planlayıcının neden o yolu seçtiğini anlamak, çözümün yarısıdır.
