PostgreSQL Sorgu Performansı Analizi

SQL iyileştirmesi, veritabanı performansı, güvenilirlik ve ölçeklenebilirlik sağlamak için kritik öneme sahiptir. Çoğu durumda, bir veritabanındaki performans sorunları zayıf performans gösteren SQL sorgularından kaynaklanmaktadır.

PostgreSQL'de, optimizasyonun ilk adımı en yavaş performans gösteren sorguları tespit etmektir. Bunu yapmak için, veritabanı tarafından çalıştırılan tüm SQL sorguları için yürütme istatistikleri sağlayan pg_stat_statements görünümünü kullanabilirsiniz.

Toplam yürütme süresine göre ilk 10 sorguyu bulmak için aşağıdaki SQL sorgusunu kullanabilirsiniz;

select query, calls, total_exec_time
from pg_stat_statements 
order by total_exec_timedesc
limit 10;

select query, calls, total_exec_time
from pg_stat_statements 
order by total_exec_timedesc
limit 10;

select query, calls, total_exec_time
from pg_stat_statements 
order by total_exec_timedesc
limit 10;

100'den fazla çalıştırılan sorgu sürelerinin ortalamasına göre ilk 10 sorguyu bulmak için aşağıdaki SQL sorgusunu kullanabilirsiniz;

select query, calls, (total_exec_time/calls)::integer as avg_time_ms 
from pg_stat_statements
where calls > 100 
order by avg_time_ms desc
limit 10;

select query, calls, (total_exec_time/calls)::integer as avg_time_ms 
from pg_stat_statements
where calls > 100 
order by avg_time_ms desc
limit 10;

select query, calls, (total_exec_time/calls)::integer as avg_time_ms 
from pg_stat_statements
where calls > 100 
order by avg_time_ms desc
limit 10;

Bir sonraki adım, en çok kaynak tüketen sorgular için yürütme planının analiz edilmesidir. Bunu yapmak için aşağıdaki gibi “explain” anahtar kelimesini kullanabilirsiniz;

PostgreSQL için basit bir sorgu yürütme planı

Basit bir sorgu yürütme planında, Seq Scan, Filter gibi operatörlerle karşılaşacaksınız. Örneğin, bir Seq Scan (ardışık tarama), PostgreSQL'in tüm payment_lab tablosunu taradığını ve ardından bir filtre uyguladığını gösterir — bu durumda, customer_id = 10. Bu, tüm satırların bellek önbelleğine okunması ve ardından filtre koşulunun değerlendirilmesi anlamına gelir.

Yürütme planının ilk satırında gösterilen maliyete dikkat etmek önemlidir. Genellikle:

• Yüksek maliyet, potansiyel olarak verimsiz bir sorguyu gösterir.

• Düşük maliyet ise daha iyi performans önerir.

Satırlar değeri, ne kadar satırın döneceğine dair planlayıcının tahminini gösterir — bu durumda, 23 satır beklenmektedir.

Performansı artırmak ve maliyeti azaltmak için customer_id sütununa bir endeks oluşturabilirsiniz. Bu, PostgreSQL'in tam bir tablo taraması yerine bir Index Scan gerçekleştirmesine olanak tanır, bu da sorguyu daha hızlı ve daha verimli hale getirir.

Endeks oluşturulduktan sonra sorgu maliyeti 290'dan 60'a azaltıldı ve yürütme planı buna göre değişti. Yeni planda, bir Filter işlemi, sorgunun artık indexi etkin bir şekilde kullandığını gösteren bir Index Condition (Index Cond) ile değiştirildi.

Orijinal planı analiz ederek Filter operatörlerini tespit edebilirsiniz. Bu filtreler genellikle indeksleme için adayları gösterir — özellikle WHERE cümleciklerinde sıkça kullanılan sütunlar üzerinde çalışıyorlarsa.

Genel olarak eksik indeksler için Sorgu Performansını Optimiz Etme Adımları:

1. Filtrelenen Sütunları Belirleme:

• Orijinal yürütme planını gözden geçirin.

• Filter işlemleri arayın ve ilgili sütunları not edin.

2. İndeks Oluşturma:

• Filtrelerde kullanılan bir veya daha fazla sütun üzerinde bir indeks oluşturun.

• Planı görmek için endeks oluşturduktan sonra EXPLAIN veya benzeri bir komut çalıştırın.

• Filter işleminden Index Cond'a bir kayma olup olmadığını kontrol edin ve yeni tahmini maliyeti not edin.

3. Çalışma Süresini Ölçme:

• İndeks oluşturmadan önce sorguyu çalıştırın ve çalışma süresini kaydedin.

• İndeks oluşturduktan sonra sorguyu çalıştırın ve yeni zamanı kaydedin.

4. Performansı Karşılaştırma:

• Orijinal ve yeni yürütme süreleri ve maliyetleri karşılaştırın.

• Her ikisinde de büyük bir azalma, indeksin sorgu performansını artırdığını önerir.