The Cartographer’s Compass: Navigating the PostgreSQL Query Planner

PostgreSQL, dünyada en çok tercih edilen açık kaynaklı ilişkisel veri tabanı yönetim sistemlerinden biridir. Sağlamlığı, esnekliği ve geniş özellik setine rağmen, zaman zaman yavaşlayan sorgularla karşılaşmak geliştiricilerin ve veri tabanı yöneticilerinin kabusu olabilir. Peki, bir sorgunun performansı neden düşer? Bu sorunun cevabı genellikle veri tabanının kalbinde yatan sorgu planlayıcıda saklıdır. Bu makale, PostgreSQL sorgu planlayıcısının haritasını çıkarmak, pusulasını anlamak ve karmaşık veri labirentlerinde en hızlı yolu bulmak için size rehberlik edecek. Sorgu performansını optimize etmenin inceliklerini keşfetmeye hazır olun.

Performans düşüşleri sadece kullanıcı deneyimini olumsuz etkilemekle kalmaz, aynı zamanda sunucu kaynaklarının aşırı tüketimine, maliyet artışlarına ve hatta sistem kararlılığı sorunlarına yol açabilir. Genellikle, yavaşlayan bir sorgu, veri tabanının veriye erişmek için en verimli yolu seçemediği anlamına gelir. Bu, yanlış indeksleme, kötü yazılmış sorgular, eski istatistikler veya veri tabanı yapılandırmasındaki eksiklikler gibi çeşitli faktörlerden kaynaklanabilir. Bir veri tabanı yöneticisi olarak veya sadece uygulamanızın daha hızlı çalışmasını isteyen bir geliştirici olarak, bu “kara kutunun” nasıl çalıştığını anlamak kritik öneme sahiptir. Bu kılavuz, PostgreSQL sorgu planlayıcısının iç işleyişine derinlemesine bir bakış sunarak, performans darboğazlarını tespit etme ve giderme becerilerinizi geliştirmeyi amaçlamaktadır. Böylece, daha hızlı ve daha verimli uygulamalar geliştirebilirsiniz.

Sorgu planlayıcı, bir SQL sorgusu geldiğinde, bu sorguyu çalıştırmanın olası tüm yollarını (erişim yöntemleri, birleşim sıralamaları vb.) değerlendiren ve aralarından en düşük maliyetli olanı seçen bir yazılım modülüdür. Bu süreç karmaşık algoritmalar ve istatistiksel verilere dayanır. Ancak, bu mekanizma her zaman mükemmel çalışmayabilir. Büyük veri kümeleri, karmaşık sorgular veya yetersiz veri tabanı istatistikleri, planlayıcının “yanlış” kararlar vermesine neden olabilir. Bu durumlar, sorguların beklentilerin çok altında bir performans sergilemesine yol açar. Örneğin, milyonlarca satırlık bir tabloda belirli bir kaydı arayan bir sorgu, uygun bir indeks olmadan tüm tabloyu taramak zorunda kalabilir; bu da kabul edilemez bir gecikmeyle sonuçlanır. İşte bu noktada, sorgu planlayıcının çıktısını doğru bir şekilde okuyabilmek ve yorumlayabilmek paha biçilmez bir yetenek haline gelir. Bu makalede, bu becerileri adım adım geliştireceğiz.

Hedefimiz, sadece sorunları tespit etmek değil, aynı zamanda onları çözmek için pratik stratejiler geliştirmektir. İndeksleme tekniklerinden, JOIN operasyonlarının inceliklerine, alt sorgu optimizasyonlarından, veri tabanı yapılandırma ayarlarının etkilerine kadar geniş bir yelpazeyi ele alacağız. Her konuyu gerçek dünya senaryoları ve kod örnekleriyle destekleyerek, öğrendiklerinizi hemen uygulayabileceğiniz somut bilgiler sunacağız. Bu sayede, “PostgreSQL sorgu planlayıcı” kavramı sizin için artık bir muamma olmaktan çıkacak, aksine veri tabanlarınızın performansını artırmak için güçlü bir araç haline gelecektir. Şimdi, bu heyecan verici keşif yolculuğuna başlayalım ve PostgreSQL’in derinliklerine inelim.

PostgreSQL Sorgu Planlayıcı Nedir ve Nasıl Çalışır?

PostgreSQL sorgu planlayıcı, her SQL sorgusu çalıştığında devreye giren ve sorgunun en verimli şekilde nasıl yürütüleceğine karar veren kritik bir bileşendir. Bir sorgu planlayıcıyı, bir şehri keşfetmek isteyen bir turiste en iyi rotayı sunan bir haritacıya benzetebiliriz. Haritacı, varış noktasına ulaşmak için farklı yolları (otoban, yan yollar, kestirmeler) değerlendirir, trafik durumunu, yolun eğimini ve diğer faktörleri hesaba katarak en hızlı veya en ekonomik rotayı belirler. PostgreSQL planlayıcısı da benzer şekilde çalışır. Bir SELECT, UPDATE, DELETE veya INSERT sorgusu geldiğinde, bu sorguyu tamamlamak için birden fazla olası yürütme yolu vardır. Planlayıcı, bu yollar arasından en düşük maliyetli olanı seçmekle yükümlüdür.

Bu süreç genellikle birkaç ana adımdan oluşur. İlk olarak, sorgu ayrıştırılır (parsing) ve bir dahili temsiline dönüştürülür. Ardından, sorgu yeniden yazılır (rewriting) ve optimizasyon için basitleştirilir (örneğin, görünüm genişletmeleri, alt sorgu birleştirmeleri). Bu aşamadan sonra asıl planlama süreci başlar. Planlayıcı, veritabanı şemasını, mevcut indeksleri, tablolardaki veri dağılımını gösteren istatistikleri ve diğer yapılandırma parametrelerini kullanarak olası tüm yürütme planlarını oluşturur. Her bir plan için bir “maliyet” tahmini yapar. Bu maliyet, genellikle CPU süresi, disk I/O’su ve ağ trafiği gibi kaynak kullanımını temsil eden bir sayısal değerdir. Planlayıcı, bu maliyetleri toplayarak ve bir karşılaştırma yaparak en düşük maliyetli planı seçer. Seçilen bu plan, sorgu yürütücüsüne (executor) gönderilir ve sorgu bu plan doğrultusunda çalıştırılır.

Sorgu Planlayıcının Temel Görevleri Nelerdir?

Sorgu planlayıcının temel görevlerini anlamak, performans sorunlarını gidermede ilk adımdır. Öncelikle, Erişim Yöntemlerini Seçmek: Bir tabloya erişmek için farklı yöntemler vardır. Örneğin, tüm tabloyu tarayabilir (Sequential Scan), bir indeks kullanarak belirli satırlara doğrudan erişebilir (Index Scan) veya bir indeksin sadece belirli bir bölümünü tarayabilir (Index Only Scan). Planlayıcı, WHERE koşullarını ve tablonun büyüklüğünü dikkate alarak en uygun erişim yöntemini belirler.

İkincil olarak, JOIN Sıralamasını ve Yöntemlerini Belirlemek: Birden fazla tablonun birleştirildiği (JOIN) sorgularda, tabloların hangi sırayla birleştirileceği ve hangi JOIN algoritmasının kullanılacağı (Nested Loop, Hash Join, Merge Join) performans üzerinde büyük etkiye sahiptir. Planlayıcı, tabloların büyüklüklerini, birleştirme koşullarını ve indekslerin varlığını göz önünde bulundurarak en iyi birleştirme stratejisini seçer. Üçüncü olarak, Sıralama ve Gruplandırma Stratejilerini Optimize Etmek: ORDER BY ve GROUP BY gibi işlemler de maliyetlidir. Planlayıcı, bu işlemleri gerçekleştirmek için indeks kullanıp kullanamayacağını veya bellekte bir sıralama/gruplama işlemi yapması gerekip gerekmediğini değerlendirir. Dördüncü olarak, Alt Sorguları ve CTE’leri Yönetmek: Karmaşık sorgulardaki alt sorguların (CTE’ler dahil) nasıl işleneceği de planlayıcının görevidir. Bazı durumlarda alt sorgular ana sorguyla birleştirilebilir (örneğin, IN/EXISTS yerine JOIN kullanmak gibi), bu da performansı önemli ölçüde artırabilir.

Maliyet Tahmini ve İstatistiklerin Rolü

Sorgu planlayıcının “en iyi” planı seçebilmesinin temelinde, her bir potansiyel yürütme planı için yaptığı doğru maliyet tahmini yatar. Bu tahminler, büyük ölçüde veri tabanındaki güncel istatistiklere dayanır. PostgreSQL, tablolar ve indeksler hakkında bilgi toplayarak (örneğin, satır sayısı, sütunlardaki benzersiz değer sayısı, veri dağılımı, boş değer yüzdesi) bu istatistikleri tutar.

ANALYZE

komutu veya otomatik vacuum işlemi (autovacuum) bu istatistikleri güncel tutmaktan sorumludur.

Eğer istatistikler güncel değilse veya yanlışsa, planlayıcı veri dağılımı hakkında yanlış varsayımlarda bulunabilir. Örneğin, bir sütunda aslında çok az farklı değer varken, planlayıcı bu sütunda çok fazla benzersiz değer olduğunu düşünebilir. Bu durum, yanlış bir indeksin kullanılmamasına veya pahalı bir tarama yönteminin seçilmesine yol açabilir. Bu nedenle, özellikle yoğun güncellemelerden sonra veya büyük veri yüklemelerinden sonra istatistikleri güncellemek (örneğin,

ANALYZE TABLE_NAME;

komutuyla) büyük önem taşır. Doğru istatistikler, planlayıcının doğru kararlar almasını sağlayarak sorgu performansını doğrudan etkiler. Bu karmaşık sistem, her bir parçanın uyumlu çalışmasını gerektirir ve bu yüzden sorgu planlayıcının pusulasını doğru okumak, veri tabanı performans optimizasyonunun vazgeçilmez bir parçasıdır.

autovacuum_analyze_scale_factor

autovacuum_analyze_threshold

EXPLAIN Komutu ile Planları Keşfetmek: Bir Yol Haritası

Sorgu planlayıcının ne düşündüğünü anlamak, performans sorunlarını teşhis etmenin anahtarıdır. İşte burada

EXPLAIN

komutu devreye girer.

EXPLAIN

, herhangi bir SQL sorgusunun (SELECT, INSERT, UPDATE, DELETE) nasıl yürütüleceğine dair bir planı, sorguyu fiilen çalıştırmadan gösterir. Bu komut, veri tabanının iç işleyişine bir pencere açar ve planlayıcının hangi erişim yöntemlerini, JOIN stratejilerini ve sıralama algoritmalarını kullanmayı düşündüğünü gösterir.

EXPLAIN

çıktısı hiyerarşik bir ağaç yapısındadır ve her bir düğüm (node) sorgunun bir parçasını temsil eden bir operasyondur. Her düğümle birlikte o operasyonun tahmini maliyeti (cost), tahmini satır sayısı (rows) ve tahmini satır genişliği (width) gibi bilgiler de sunulur. Bu değerler, planlayıcının o operasyon için ne kadar kaynak tüketeceğini öngördüğünü gösterir.

Çıktıyı doğru okumak, başlangıçta biraz kafa karıştırıcı olabilir, çünkü her satır bir operasyonu ve onun özelliklerini ifade eder. Genellikle, en içteki düğümler (en sağdaki girintili satırlar) en önce yürütülen operasyonlardır. Dış düğümler ise bu iç operasyonların sonuçlarını işler. Örneğin, bir

Index Scan

bir veya daha fazla satırı bulurken, bir

Nested Loop Join

bu sonuçları başka bir tabloyla birleştirebilir. Her bir operasyonun maliyeti,

cost=start..total

şeklinde gösterilir.

start

maliyeti, operasyonun ilk satırını döndürmeden önceki maliyeti,

total

ise tüm satırları döndürmek için gereken toplam maliyeti ifade eder. Genellikle, en yüksek toplam maliyete sahip düğüm, sorgunun en pahalı kısmı veya darboğazı olabilir. Ancak bu sadece bir tahmindir; gerçek maliyetler farklılık gösterebilir. İşte bu noktada

EXPLAIN ANALYZE

devreye girer.

Temel EXPLAIN Çıktısını Anlamak

Basit bir

EXPLAIN

sorgusu ile başlayalım. Aşağıdaki örneği kullanarak

EXPLAIN

çıktısını nasıl yorumlayacağımızı görelim. Bir

urunler

tablonuz olduğunu ve bu tabloda

fiyat

sütununa göre arama yaptığınızı varsayalım:

      EXPLAIN SELECT * FROM urunler WHERE fiyat > 1000;
    

Çıktı şöyle bir şeye benzeyebilir:

      Seq Scan on urunler  (cost=0.00..1234.56 rows=500 width=150)
        Filter: (fiyat > 1000)
    

Bu çıktı bize şunları anlatıyor:

• Seq Scan on urunler

  : PostgreSQL,

  urunler

  tablosunu baştan sona tarıyor (Sequential Scan). Bu genellikle tablonun büyük olduğu veya uygun bir indeksin olmadığı durumlarda meydana gelir.

• cost=0.00..1234.56

  : Sorgunun tahmini maliyeti. İlk sayı (0.00) ilk satırı döndürmenin maliyetini, ikinci sayı (1234.56) ise tüm satırları döndürmenin toplam maliyetini temsil eder.

• rows=500

  : Sorgunun bu adımdan tahmini olarak 500 satır döndüreceği anlamına gelir.

• width=150

  : Her bir satırın tahmini ortalama genişliği (byte cinsinden).

• Filter: (fiyat > 1000)

  :

  fiyat > 1000

  koşulunun

  Seq Scan

  sonrasında uygulandığını gösterir. Yani tüm tablo taranıyor ve sonra filtreleniyor.

Bu örnekte, eğer

urunler

tablosu çok büyükse ve

fiyat

sütununda sıkça arama yapılıyorsa, bir

Sequential Scan

kötü performans anlamına gelebilir. Bu, muhtemelen bir indeksin gerekli olduğu anlamına gelir. Şayet bir indeks olsaydı, çıktı büyük ihtimalle

Index Scan

veya

Bitmap Heap Scan

gibi bir şey gösterecekti.

EXPLAIN ANALYZE: Gerçek Performansı Ölçmek

Yukarıda bahsettiğimiz gibi,

EXPLAIN

sadece tahmini değerler sunar. Gerçek performansın nasıl olduğunu görmek için

EXPLAIN ANALYZE

kullanırız. Bu komut, sorguyu fiilen çalıştırır, ancak sonuçlarını döndürmek yerine planı ve her bir operasyonun gerçek çalışma süresini (execution time) ve döndürülen gerçek satır sayısını (actual rows) gösterir. Bu, planlayıcının tahminlerinin ne kadar doğru olduğunu görmemizi sağlar.

      EXPLAIN ANALYZE SELECT * FROM urunler WHERE fiyat > 1000;
    

Çıktı şöyle bir şeye benzeyebilir:

      Seq Scan on urunler  (cost=0.00..1234.56 rows=500 width=150) (actual time=0.010..15.234 rows=480 loops=1)
        Filter: (fiyat > 1000)
        Rows Removed by Filter: 9520
      Planning Time: 0.123 ms
      Execution Time: 15.345 ms
    

Burada eklenen önemli bilgiler şunlardır:

• actual time=0.010..15.234

  : Operasyonun gerçek çalışma süresi. İlk sayı, ilk satırı döndürmek için geçen süreyi (milisaniye cinsinden), ikinci sayı ise tüm satırları döndürmek için geçen toplam süreyi gösterir.

• rows=480

  : Operasyon tarafından gerçekten döndürülen satır sayısı. Planlayıcının tahmini 500 iken, gerçekte 480 satır döndürülmüş. Bu büyük bir fark değil, ancak bazen bu değerler arasında devasa farklar olabilir, bu da istatistiklerin güncel olmadığını veya planlayıcının yanlış tahminler yaptığını gösterir.

• loops=1

  : Bu operasyonun kaç kez tekrarlandığını gösterir (örneğin,

  Nested Loop Join

  gibi durumlarda önemli olabilir).

• Rows Removed by Filter: 9520

  : Filtreleme sonucunda kaç satırın elendiğini gösterir. Bu, filtrenin etkinliğini değerlendirmek için faydalıdır.

• Planning Time: 0.123 ms

  : Sorgu planlayıcının planı oluşturmak için harcadığı gerçek süre.

• Execution Time: 15.345 ms

  : Sorgunun planlayıcı tarafından seçilen plan doğrultusunda toplamda ne kadar sürede yürütüldüğü.

Planlayıcının tahmini

rows

değeri ile

actual rows

değeri arasında büyük bir tutarsızlık varsa, bu genellikle istatistiklerin güncel olmadığını veya planlayıcının optimizasyon için yeterli bilgiye sahip olmadığını gösterir. Bu durumda, ilgili tablolar üzerinde

ANALYZE

çalıştırmak veya daha gelişmiş indeksleme stratejileri düşünmek gerekebilir.

EXPLAIN ANALYZE

ile elde edilen bu detaylı bilgiler, sorgunuzun darboğazlarını kesin olarak tespit etmenize ve performans iyileştirmeleri için doğru adımları atmanıza olanak tanır.

Performans Engellerini Aşmak: İndeksler ve JOIN Stratejileri

PostgreSQL sorgularının performansını artırmak için en etkili yollardan ikisi, doğru indekslemeyi uygulamak ve JOIN stratejilerini optimize etmektir. Tıpkı bir kütüphanede bir kitabı bulmak için dizinlerin (indekslerin) kullanılması gibi, veri tabanı indeksleri de belirli verilere çok daha hızlı erişim sağlar. Ancak her indeksin bir maliyeti vardır ve yanlış indeksleme bazen performansı düşürebilir. Benzer şekilde, birden fazla tablonun birleştirildiği (JOIN) sorgular, seçilen JOIN algoritmasına ve birleştirme sırasına bağlı olarak radikal performans farklılıkları gösterebilir. Bu bölümde, bu kritik alanları derinlemesine inceleyecek ve sorgularınızın hızlanması için pratik yaklaşımlar sunacağız.

İndeksler, disk üzerindeki veriye fiziksel bir sıralama getirmeden mantıksal bir sıralama sağlayarak arama işlemlerini hızlandırır. Ancak bir tabloya yeni veri eklendiğinde, güncellendiğinde veya silindiğinde indekslerin de güncellenmesi gerekir. Bu, yazma (INSERT/UPDATE/DELETE) operasyonları için ek bir yük demektir. Bu nedenle, her sütuna indeks eklemek mantıklı değildir. İndekslemeyi planlarken, sorgularınızın hangi sütunları

WHERE

koşullarında,

ORDER BY

veya

GROUP BY

ifadelerinde kullandığını dikkatlice analiz etmelisiniz. Ayrıca, indekslerin boyutu da önemlidir. Büyük indeksler daha fazla disk alanı kaplar ve bellekte daha fazla yer tüketir, bu da önbellekleme (caching) performansını etkileyebilir. Doğru indeks seçimi, genellikle sorguların en sık ve en maliyetli kısımlarını hedef almayı gerektirir.

Hangi İndeksi Ne Zaman Kullanmalıyım?

PostgreSQL çeşitli indeks türlerini destekler ve her birinin belirli kullanım senaryoları vardır:

• B-Tree İndeksler: En yaygın indeks türüdür. Eşitlik (

  =

  ), aralık (

  , >=

  ),

  IS NULL

  ,

  IS NOT NULL

  ve

  LIKE 'prefix%'

  gibi operatörlerle yapılan aramalar için idealdir.

  ORDER BY

  ve

  GROUP BY

  işlemleri için de sıralı erişim sağladığından faydalıdır.

          CREATE INDEX idx_urunler_fiyat ON urunler (fiyat);
        

=

          CREATE INDEX idx_dokumanlar_tags ON dokumanlar USING GIN (tags);
        

WHERE

(bolge_id, tarih)

WHERE bolge_id = 5 AND tarih > '2023-01-01'

JOIN Tipleri ve Sorgu Planlayıcı Üzerindeki Etkileri

PostgreSQL sorgu planlayıcısı, birden fazla tabloyu birleştirmek için üç ana JOIN algoritması kullanır:

1. Nested Loop Join (İç İçe Döngü Birleşimi): En basit JOIN türüdür. Dış tablodaki her satır için, iç tablonun tamamını veya indeksli kısmını tarar. Küçük dış tablolar ve indeksli iç tablolar için etkilidir. Eğer iç tabloda JOIN koşulu üzerinde bir indeks varsa çok hızlı olabilir. Planlayıcı, bir tablonun çok küçük olduğunu düşündüğünde veya JOIN koşulu indekslenmiş olduğunda bunu tercih edebilir.
2. Hash Join (Hash Birleşimi): Bir tablonun (genellikle daha küçük olanın) JOIN sütunları üzerinde bir hash tablosu oluşturur. Ardından, diğer tablonun satırlarını bu hash tablosunda arar. Büyük, indekslenmemiş tabloları birleştirmek için çok etkilidir. Bellek (work_mem) kullanımına ihtiyaç duyar; eğer hash tablosu belleğe sığmazsa, diskte geçici dosyalar kullanabilir, bu da performansı düşürür.
3. Merge Join (Birleştirme Birleşimi): Her iki tabloyu da JOIN anahtarı üzerinde sıralar (veya zaten sıralanmış indeksleri kullanır) ve sonra iki sıralı listeyi birleştirir. Her iki tablonun da JOIN koşulu üzerinde sıralanmış olması gerektiğinden, bu birleştirme türü genellikle tabloların önce sıralanmasını gerektirir, bu da ek maliyet getirebilir. Ancak büyük tablolarda, eğer her iki taraf da önceden sıralanmışsa (örneğin, bir

   ORDER BY

   işlemi zaten yapılmışsa), çok verimli olabilir.

Planlayıcı, hangi JOIN yöntemini seçeceğine, tabloların tahmini büyüklükleri, JOIN koşullarında indekslerin varlığı ve

work_mem

gibi yapılandırma parametrelerini göz önünde bulundurarak karar verir. Örneğin, iki büyük tabloyu indeks kullanmadan birleştiriyorsanız, planlayıcı büyük olasılıkla

Hash Join

kullanacaktır. Eğer küçük bir tabloyu büyük, indeksli bir tabloyla birleştiriyorsanız,

Nested Loop Join

tercih edebilir. Bu seçimler,

EXPLAIN ANALYZE

çıktısında açıkça görünür. Yanlış JOIN yönteminin seçilmesi, sorgunun çok uzun sürmesine neden olabilir. Bu durumda, indeks eklemek, istatistikleri güncellemek veya hatta sorguyu yeniden yazarak planlayıcıya yardımcı olmak gerekebilir.

Örneğin, bir

Hash Join

planında, eğer

work_mem

ayarı yetersizse, planlayıcı geçici disk dosyaları kullanmak zorunda kalacak ve bu da

"Disk: xxxxkB"

gibi bir bilgiyle

EXPLAIN ANALYZE

çıktısında belirecektir. Bu,

work_mem

ayarını artırmanız gerektiğinin bir işaretidir. Doğru indeksleri kullanmak ve JOIN stratejilerini anlamak, veri tabanı performans optimizasyonunda atacağınız en büyük ve en etkili adımlardan bazılarıdır.

Gerçek Dünya Senaryoları: Vaka Analizleri ile Optimizasyon

Teorik bilgileri pekiştirmenin en iyi yolu, onları gerçek dünya problemlerine uygulamaktır. Bu bölümde, karşılaşabileceğiniz yaygın performans sorunlarını ve bunları PostgreSQL sorgu planlayıcısını kullanarak nasıl çözebileceğinizi gösteren iki vaka analizi sunacağız. Bu senaryolar, sadece sorunları tespit etmenize yardımcı olmakla kalmayacak, aynı zamanda etkili çözüm stratejileri geliştirme becerilerinizi de artıracaktır. Her bir vaka analizi, sorunun tespiti,

EXPLAIN ANALYZE

ile planın incelenmesi ve ardından uygulanan optimizasyon adımlarını içerecektir.

Yavaş Bir Rapor Sorgusunu Hızlandırma Vaka Analizi

Senaryo: Bir e-ticaret şirketinde çalışıyorsunuz. Pazarlama departmanı, son 6 aydaki en çok satan ürünleri, her ürünün toplam satış miktarını ve ortalama satış fiyatını gösteren günlük bir rapor istiyor. Ancak bu raporun üretimi 2 dakikayı aşıyor ve bu da departmanın iş akışını yavaşlatıyor.

Mevcut Sorgu:

      SELECT
          p.urun_adi,
          SUM(oi.miktar) AS toplam_satis_miktari,
          AVG(oi.fiyat) AS ortalama_satis_fiyati
      FROM
          urunler p
      JOIN
          siparis_kalemleri oi ON p.urun_id = oi.urun_id
      JOIN
          siparisler o ON oi.siparis_id = o.siparis_id
      WHERE
          o.siparis_tarihi >= NOW() - INTERVAL '6 months'
      GROUP BY
          p.urun_adi
      ORDER BY
          toplam_satis_miktari DESC
      LIMIT 10;
    

Sorun Tespiti ve

EXPLAIN ANALYZE

Çıktısı:

Sorguyu

EXPLAIN ANALYZE

ile çalıştırdığınızda, çıktı aşağıdaki gibi bir şeye benziyor olabilir (basitleştirilmiş):

      Sort  (cost=X..Y rows=Z width=W) (actual time=11500.000..11600.000 rows=10 loops=1)
        Sort Key: (sum(oi.miktar)) DESC
        ->  GroupAggregate  (cost=A..B rows=C width=D) (actual time=11000.000..11400.000 rows=5000 loops=1)
              Group Key: p.urun_adi
              ->  Hash Join  (cost=E..F rows=G width=H) (actual time=1000.000..10500.000 rows=1000000 loops=1)
                    Hash Cond: (oi.urun_id = p.urun_id)
                    ->  Hash Join  (cost=I..J rows=K width=L) (actual time=100.000..9000.000 rows=2000000 loops=1)
                          Hash Cond: (oi.siparis_id = o.siparis_id)
                          ->  Seq Scan on siparis_kalemleri oi  (cost=M..N rows=P width=Q) (actual time=10.000..500.000 rows=5000000 loops=1)
                          ->  Hash
                                ->  Seq Scan on siparisler o  (cost=R..S rows=T width=U) (actual time=1.000..200.000 rows=2500000 loops=1)
                                      Filter: (siparis_tarihi >= (now() - '6 mons'::interval))
    

Çıktıyı incelerken şunları fark ettiniz:

1. Seq Scan on siparis_kalemleri oi

   :

   siparis_kalemleri

   tablosu tamamen taranıyor. Bu tablonun milyonlarca satırı var ve bu tarama çok pahalı.

2. Hash Join

   işlemleri,

   actual time

   değerlerine bakıldığında toplam sürenin büyük bir kısmını alıyor. Özellikle

   urunler

   ve

   siparis_kalemleri

   arasındaki join maliyetli.

3. GroupAggregate

   ve

   Sort

   de önemli süreler alıyor.

4. Filter: (siparis_tarihi >= (now() - '6 mons'::interval))

   koşulu

   siparisler

   tablosu üzerinde

   Seq Scan

   sonrası uygulanıyor.

Optimizasyon Adımları:

1. İndeksleme: En büyük sorunlardan biri

   siparisler.siparis_tarihi

   ve

   urun_id

   /

   siparis_id

   sütunları üzerinde indeks eksikliği.

   
                 CREATE INDEX idx_siparisler_tarih ON siparisler (siparis_tarihi);
                 CREATE INDEX idx_siparis_kalemleri_urun_id ON siparis_kalemleri (urun_id);
                 CREATE INDEX idx_siparis_kalemleri_siparis_id ON siparis_kalemleri (siparis_id);
               

2. Birleşik İndeks:

   siparis_kalemleri

   tablosunda

   urun_id

   ve

   siparis_id

   birlikte kullanıldığı için birleşik bir indeks de faydalı olabilir:

   
                 CREATE INDEX idx_siparis_kalemleri_urun_siparis ON siparis_kalemleri (urun_id, siparis_id);
               

3. Gerekli Sütunları Seçme:

   SELECT *

   yerine sadece gerekli sütunları seçmek, I/O yükünü azaltır, ancak bu sorguda zaten toplayıcı fonksiyonlar kullanıldığı için etkisi daha az olacaktır.

Sonuç: İndeksleri ekledikten sonra sorguyu tekrar

EXPLAIN ANALYZE

ile çalıştırdığınızda, planın

Index Scan

veya

Bitmap Index Scan

gibi daha verimli erişim yöntemlerini kullandığını ve toplam yürütme süresinin saniyelerin altına düştüğünü göreceksiniz. Özellikle

siparis_tarihi

indeksi, filtrelenmiş siparişleri çok daha hızlı bulmayı sağlayacaktır, bu da downstream JOIN ve GROUP BY işlemlerinin kapsamını daraltacaktır. Bu, raporun 2 dakikadan birkaç saniyeye inmesini sağlayabilir.

Yoğun Yazma İşlemlerinde Performansı Koruma

Senaryo: Sürekli olarak yeni kayıtların eklendiği bir

log_kayitlari

tablonuz var. Bu tabloda milyonlarca satır bulunuyor ve ara sıra bu kayıtlarda arama yapmanız gerekiyor. Ancak

INSERT

işlemleri giderek yavaşlıyor ve veri tabanı kilitlenmeleri yaşamaya başlıyorsunuz.

Mevcut Durum:

log_kayitlari

tablosunda

id

(PK),

mesaj

,

zaman_damgasi

sütunları var.

zaman_damgasi

üzerinde bir indeks bulunuyor.

      CREATE TABLE log_kayitlari (
          id BIGSERIAL PRIMARY KEY,
          mesaj TEXT,
          zaman_damgasi TIMESTAMP DEFAULT NOW()
      );
      CREATE INDEX idx_log_zaman_damgasi ON log_kayitlari (zaman_damgasi);
    

Sorun Tespiti: Yoğun

INSERT

işlemleri sırasında

idx_log_zaman_damgasi

indeksi her yeni kayıt eklendiğinde güncellenmek zorundadır. Milyonlarca satırlık bir tabloda bu indeksin boyutu çok büyük olabilir ve güncelleme maliyeti artar. Ayrıca, indeksin parçalanması (fragmentation) da performans düşüşüne neden olabilir.

autovacuum

yeterince hızlı çalışmıyor olabilir ve bu da ölü satırların (dead tuples) birikmesine ve indeksin daha da büyümesine yol açar.

Optimizasyon Adımları:

1. İndeks Türü ve Amacı Üzerine Düşünme: Eğer

   zaman_damgasi

   üzerinde aralık aramaları yapılıyorsa (örneğin, belirli bir zaman aralığındaki loglar), B-Tree uygun olabilir. Ancak, bu tabloya sürekli ekleme yapıldığı için, veri fiziksel olarak zaman sırasına göre depolanır. Bu durumda, BRIN (Block Range Index) indeksi daha verimli olabilir. BRIN indeksler, büyük tablolarda sıralı veri üzerinde çok daha az yer kaplayarak iyi performans sunabilir.

   
                 DROP INDEX idx_log_zaman_damgasi;
                 CREATE INDEX idx_log_zaman_damgasi ON log_kayitlari USING BRIN (zaman_damgasi);
               

   Uzman İpucu: BRIN indeksler, verinin eklenme sırasına göre fiziksel olarak sıralı olduğu tablolarda (örneğin, log tabloları, zaman serisi verileri) B-Tree indekslerden çok daha az disk alanı kaplar ve daha hızlı güncelleme maliyetine sahiptir. Ancak, verinin rastgele dağıldığı tablolarda performansı iyi değildir.
2. Partitioning (Bölümleme): Zaman damgasına göre bölümleme, büyük bir tabloyu daha küçük, yönetilebilir parçalara ayırır. Her ay veya hafta için ayrı bir bölüm oluşturulabilir. Bu,

   INSERT

   işlemlerini ilgili bölüme yönlendirerek indeksleme yükünü dağıtır ve

   DELETE

   veya

   TRUNCATE

   (eski verileri silmek için) işlemlerini çok daha hızlı hale getirir.

   
                 -- Ana tabloyu oluştur
                 CREATE TABLE log_kayitlari (
                     id BIGSERIAL,
                     mesaj TEXT,
                     zaman_damgasi TIMESTAMP DEFAULT NOW()
                 ) PARTITION BY RANGE (zaman_damgasi);
   
                 -- Partitionlar oluştur (örneğin aylık)
                 CREATE TABLE log_kayitlari_2023_01 PARTITION OF log_kayitlari
                     FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
                 CREATE TABLE log_kayitlari_2023_02 PARTITION OF log_kayitlari
                     FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
                 -- Her partition kendi indekslerine sahip olabilir.
                 CREATE INDEX idx_log_2023_01_zaman_damgasi ON log_kayitlari_2023_01 (zaman_damgasi);
               

   Bölümleme ile bir

   INSERT

   işlemi sadece ilgili bölümün indeksini günceller, bu da genel yazma performansını artırır. Ayrıca, sorgular

   WHERE zaman_damgasi BETWEEN X AND Y

   koşulu içerdiğinde, sorgu planlayıcı sadece ilgili bölümleri tarar (partition pruning), bu da okuma performansını önemli ölçüde hızlandırır.

Sonuç: BRIN indeksleme ve tablo bölümleme kombinasyonu, yoğun yazma işlemlerinin performansını önemli ölçüde iyileştirirken, eski loglarda arama yapma yeteneğini de koruyacaktır.

INSERT

işlemleri daha hızlı tamamlanacak, indeks bakımı daha kolay olacak ve veri tabanı kilitlenmeleri azalacaktır. Bu yaklaşımlar, özellikle büyüyen veri tabanlarında sürdürülebilir performansı sağlamak için kritik öneme sahiptir.

Sonuç: Sorgu Planlayıcıyı Yönetmek Bir Sanattır

PostgreSQL sorgu planlayıcısı, veri tabanınızın performansını doğrudan etkileyen karmaşık ve zeki bir mekanizmadır. Bu makalede, planlayıcının temel işleyişinden,

EXPLAIN

ve

EXPLAIN ANALYZE

komutlarıyla nasıl etkileşim kurulacağına, indekslerin ve JOIN stratejilerinin önemine kadar birçok konuyu ele aldık. Gerçek dünya senaryolarıyla, bu bilgileri pratik sorunlara nasıl uygulayacağınızı gösterdik. Unutmayın ki, mükemmel bir sorgu planı her zaman mümkün olmasa da, planlayıcının "pusulasını" doğru okuyarak ve onun "düşünce süreçlerini" anlayarak, veri tabanı performansınızı önemli ölçüde iyileştirebilirsiniz. Bu süreç sürekli öğrenmeyi ve denemeyi gerektiren bir sanattır; her veri tabanının ve her iş yükünün kendine özgü dinamikleri vardır.

Performans optimizasyonu, tek seferlik bir görev değil, sürekli bir gözlem ve ayarlama sürecidir. Veri tabanınız büyüdükçe, iş yükünüz değiştikçe ve uygulamanız evrildikçe, sorgu planları da değişebilir ve yeni darboğazlar ortaya çıkabilir. Bu nedenle, düzenli olarak en kritik sorgularınızı

EXPLAIN ANALYZE

ile incelemek, istatistiklerinizi güncel tutmak ve indeksleme stratejilerinizi gözden geçirmek hayati önem taşır. Ayrıca,

work_mem

,

shared_buffers

gibi PostgreSQL yapılandırma parametrelerinin de sorgu planlayıcının kararlarını ve dolayısıyla sorgu performansını etkilediğini akılda tutmak önemlidir. Bu parametreler, veri tabanınızın belleği ve disk G/Ç'sini nasıl kullanacağını belirler. Her zaman iş yükünüze en uygun ayarları bulmak için testler yapmaktan çekinmeyin.

Son olarak, unutmayın ki bazen en iyi optimizasyon, sorguyu farklı bir yaklaşımla yeniden yazmaktan geçer. Alt sorguları CTE'lere dönüştürmek, gereksiz JOIN'lerden kaçınmak, karmaşık filtreleri basitleştirmek veya veri modelinizi gözden geçirmek, planlayıcının daha verimli bir yol bulmasına yardımcı olabilir. Veri tabanı yöneticisi veya geliştirici olarak, bu araçları ve teknikleri bilmek, sadece daha hızlı uygulamalar oluşturmanızı sağlamakla kalmaz, aynı zamanda veri tabanınızın uzun ömürlü ve performanslı kalmasını da garanti eder. Sorgu planlayıcısı, karmaşık bir labirentteki rehberinizdir; onu anlamak, kaybolmadan hedefinize ulaşmanızı sağlar. Şimdi edindiğiniz bu bilgilerle, kendi veri tabanı performans yolculuğunuza güvenle devam edebilirsiniz!

Sıkça Sorulan Sorular

1. Sorgu planlayıcı neden bazen yavaş bir plan seçer?

Sorgu planlayıcı, en düşük "tahmini" maliyete sahip planı seçer. Ancak bu tahminler, veri tabanı istatistiklerinin güncelliğine ve doğruluğuna dayanır. Eğer istatistikler eski veya yanlışsa (örneğin, bir tablonun satır sayısı hakkında yanlış bilgi), planlayıcı yanlış varsayımlarda bulunabilir ve suboptimal bir plan seçebilir. Ayrıca, karmaşık sorgular veya çok düşük

work_mem

gibi sistem kaynak sınırlamaları da planlayıcının yanlış kararlar almasına neden olabilir.

2.

EXPLAIN

ve

EXPLAIN ANALYZE

arasındaki fark nedir?

EXPLAIN

sadece sorguyu fiilen çalıştırmadan, planlayıcının hangi planı seçeceğini ve her operasyonun tahmini maliyetini (cost), satır sayısını (rows) ve satır genişliğini (width) gösterir.

EXPLAIN ANALYZE

ise sorguyu gerçekten çalıştırır ve tahmini değerlerin yanı sıra her operasyonun gerçek çalışma süresini (actual time) ve gerçekte döndürülen satır sayısını (actual rows) gösterir.

EXPLAIN ANALYZE

, planlayıcının tahminlerinin ne kadar doğru olduğunu görmemizi ve gerçek performans darboğazlarını tespit etmemizi sağlar.

3. Hangi durumlarda indeksler performansı düşürebilir?

İndeksler genellikle okuma (SELECT) operasyonlarını hızlandırsa da, yazma (INSERT, UPDATE, DELETE) operasyonlarının maliyetini artırır. Bir tabloya yeni bir kayıt eklendiğinde veya mevcut bir kayıt güncellendiğinde, ilgili indekslerin de güncellenmesi gerekir. Çok fazla indeks, çok büyük indeksler veya az kullanılan indeksler, yazma performansını olumsuz etkileyebilir. Ayrıca, yüksek kardinaliteli (çok fazla benzersiz değer içeren) olmayan sütunlara (örneğin, cinsiyet gibi sadece iki değere sahip sütunlar) indeks eklemek genellikle fayda sağlamaz ve sadece disk alanı tüketir.

4. PostgreSQL sorgu optimizasyonu için en önemli üç ipucu nedir?

1. EXPLAIN ANALYZE

   kullanın:

   Sorgularınızın gerçek performansını ve darboğazlarını anlamak için bu komutu düzenli olarak kullanın.
2. Doğru indekslemeyi uygulayın: Sorgularınızın

   WHERE

   koşullarında,

   ORDER BY

   ve

   GROUP BY

   ifadelerinde kullanılan sütunlara stratejik olarak indeks ekleyin. Yanlış veya gereksiz indekslerden kaçının.

3. İstatistikleri güncel tutun:

   ANALYZE

   komutunu kullanarak veya otomatik vacuum/analyze ayarlarını optimize ederek veri tabanı istatistiklerinin her zaman güncel olduğundan emin olun. Eskimiş istatistikler, planlayıcının yanlış planlar seçmesine neden olabilir.

5. Sorgu planlayıcının JOIN stratejilerini nasıl etkilerim?

Sorgu planlayıcı, tabloların büyüklüğü, JOIN koşullarında indekslerin varlığı ve

work_mem

gibi yapılandırma parametrelerine göre JOIN stratejisini (Nested Loop, Hash Join, Merge Join) seçer. Bu seçimi etkilemek için: indeksler ekleyerek (özellikle JOIN anahtarları üzerinde),

work_mem

parametresini artırarak (Hash Join'ler için önemlidir) veya sorguyu yeniden yazarak (örneğin, alt sorgular yerine CTE'ler veya farklı bir JOIN sırası deneyerek) planlayıcıya ipuçları verebilirsiniz. Ancak, genellikle planlayıcının varsayılan kararları iyi çalışır; müdahale sadece

EXPLAIN ANALYZE

çıktısında bir performans darboğazı gördüğünüzde düşünülmelidir.