PostgreSQL planlarını, tekrarlanan çalıştırma sürelerini kök düğümdeki tampon sayılarıyla eşleştirerek karşılaştırın. Bu Mac Terminal kılavuzu, bir dizinin veritabanı iş yükünü azaltıp azaltmadığını sınamak için sentetik makale satırları kullanır ve tek bir çalıştırmayı yanıltıcı kılabilecek önbellek etkilerini hesaba katar.

Birlikte neleri ölçmelisiniz?

Göreviniz, dizin eklemeden önceki ve sonraki aynı makale listesi sorgusunu karşılaştırmaktır. Seçilen sütunları, kategoriyi, sıralamayı, limiti ve verileri değiştirmeyin. Hem geçen çalıştırma süresini hem de sonucu üretmek için gereken tampon etkinliğini kaydedin.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON) kullanın. ANALYZE seçeneği sorguyu çalıştırır; TIMING OFF, tüm ifade için çalıştırma süresini korurken tek tek düğümlerin saat ölçümlerini devre dışı bırakır. Makine tarafından okunabilir çıktı, tekrarlanan ölçümlerin toplanmasını kolaylaştırır. PostgreSQL: EXPLAIN

Bu ölçümleri birlikte değerlendirin:

Ölçüm Ne anlatır?
Execution Time Planlama hariç sunucu çalıştırma süresi
Planning Time Planı oluşturmak ve iyileştirmek için harcanan süre
Shared Hit Blocks PostgreSQL’in paylaşılan önbelleğinden karşılanan tampon erişimleri
Shared Read Blocks Paylaşılan tamponlara alınan bloklar
Paylaşılan isabetler artı okumalar Paylaşılan tampon erişim hacmini karşılaştırmak için kullanışlı bir ölçü
Temp Read Blocks ve Temp Written Blocks Sıralamanın belleği aşması gibi geçici çalışma dosyası etkinlikleri

Paylaşılan bloklar normal tablolara ve dizinlere aittir. Yerel bloklar geçici tablolara ve dizinlere aittir; geçici çalışma dosyası blokları ayrı bir kategoridir. Üst düğüm, alt düğümlerinin tampon etkinliğini de içerir. Bu nedenle çalıştırma toplamları için kök düğümü kullanın, işin nerede yapıldığını bulmak için alt düğümleri inceleyin. Tüm düğümlerin sayılarını asla toplamayın. PostgreSQL: EXPLAIN

Tampon sayıları, benzersiz olmayan erişimleri temsil eder: aynı bloğa yeniden erişilmesi tekrar sayılabilir. Ayrıca planlayıcı maliyetleri milisaniye değil, keyfî birimlerdir. Gerçek çalıştırma sürelerini gerçek tampon sayılarıyla karşılaştırın; planlayıcının seçimini anlamak için tahmini maliyetleri kullanın. PostgreSQL: EXPLAIN Kullanımı

Paylaşılan bir okuma, Mac’inizin depolama aygıtının fiziksel bir okuma yaptığına kanıt değildir. PostgreSQL’in G/Ç istatistikleri, depolamadan getirilen verileri işletim sisteminin sayfa önbelleğinde zaten bulunan verilerden ayırt edemez. Ayrı depolama kanıtınız yoksa ölçümü paylaşılan tampon okumaları olarak tanımlayın. PostgreSQL: Birikimli İstatistik Sistemi

Mac deneyini hazırlayın ve sürüm farklarını kontrol edin

Terminal’de çalışır durumda yerel bir PostgreSQL veritabanı ve npm kullanılabilir durumdayken Node.js ile başlayın. Bu kılavuz PostgreSQL 18’i hedefler ve Node.js 22 veya 24 kullanır. node-postgres belgeleri bu Node.js sürümlerini uyumlu olarak listeler ve npm install pg komutuyla kurulumu açıklar. node-postgres

Karşılaştırma komutu PostgreSQL 16 ve 17 belgelerinde de yer alır. PostgreSQL 18, EXPLAIN ANALYZE ile tampon çıktısını otomatik olarak içerir; BUFFERS seçeneğini açıkça belirtmek, bu sürümlerde de amacı netleştirir. Daha yeni isteğe bağlı ayarların eski sunucularda bulunduğunu varsaymayın. PostgreSQL 16: EXPLAIN, PostgreSQL 17: EXPLAIN, PostgreSQL 18 sürüm notları

Yeni bir çalışma dizininde sürücüyü yükleyin:

mkdir pg-plan-comparison
cd pg-plan-comparison
npm init -y
npm install pg

Her zamanki PostgreSQL bağlantınızın açabildiği mevcut bir yerel deneme veritabanını kullanın. Betik, psql ile aynı bağlantı ortam değişkenlerini okuyan new Client() kullanır. Örneğin, veritabanının adı plan_lab ise betiği PGDATABASE=plan_lab ile çalıştırın. Gerekirse mevcut PGHOST veya PGPORT ayarlarınızı ekleyin. node-postgres: Bağlanma

Program, bir işlem içinde 100.000 sentetik makale oluşturur, iki şema durumunu karşılaştırır ve işlemi geri alır. Taramanın paylaşılan tamponlar altında görünmesi için normal bir tablo kullanır. PostgreSQL, ROLLBACK komutunun işlemin güncellemelerini attığını belirtir. Gösterim tablosunun adı zaten varsa, tablo değiştirilmek yerine oluşturma işlemi başarısız olur. PostgreSQL: ROLLBACK

Baştan sona bir önce-sonra karşılaştırması çalıştırın

Aşağıdaki programın tamamını compare-plans.mjs olarak kaydedin. Program kendi girdisini oluşturur, her aşamada kayda alınmayan bir ısınma çalıştırması yapar, ölçülen yedi çalıştırmayı saklar ve her aşama için ölçülen ilk planı yazdırır. Satır oluşturucu, PostgreSQL’in belgelenmiş generate_series işlevini kullanır. PostgreSQL: Küme Döndüren İşlevler

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const query = `
  SELECT article_id, title, published_at
  FROM plan_compare_articles_demo
  WHERE category_id = $1
  ORDER BY published_at DESC, article_id DESC
  LIMIT $2
`;
const values = [7, 50];
const explain = `
  EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
  ${query}
`;
const median = values =>
  [...values].sort((a, b) => a - b)[Math.floor(values.length / 2)];

async function measure(label) {
  await client.query(explain, values);
  const runs = [];
  let firstPlan;
  for (let run = 1; run <= 7; run++) {
    const result = await client.query(explain, values);
    const document = result.rows[0]['QUERY PLAN'][0];
    const root = document.Plan;
    firstPlan ??= document;
    const hits = root['Shared Hit Blocks'] ?? 0;
    const reads = root['Shared Read Blocks'] ?? 0;
    runs.push({
      phase: label,
      run,
      execution_ms: document['Execution Time'],
      planning_ms: document['Planning Time'],
      shared_hit: hits,
      shared_read: reads,
      shared_accesses: hits + reads,
      temp_read: root['Temp Read Blocks'] ?? 0,
      temp_written: root['Temp Written Blocks'] ?? 0
    });
  }
  console.table(runs);
  console.log(label, 'median execution ms:',
    median(runs.map(run => run.execution_ms)));
  console.dir(firstPlan, { depth: null });
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows[0]);
  await client.query('BEGIN');
  await client.query(`
    CREATE TABLE plan_compare_articles_demo (
      article_id integer PRIMARY KEY,
      category_id integer NOT NULL,
      title text NOT NULL,
      published_at timestamp NOT NULL,
      body text NOT NULL
    )
  `);
  await client.query(`
    INSERT INTO plan_compare_articles_demo
      (article_id, category_id, title, published_at, body)
    SELECT g, g % 20, 'Article ' || g,
           timestamp '2025-01-01' + g * interval '1 minute',
           repeat('Synthetic article text. ', 12)
    FROM generate_series(1, 100000) AS series(g)
  `);
  await client.query('ANALYZE plan_compare_articles_demo');
  const before = await client.query(query, values);
  await measure('before index');

  await client.query(`
    CREATE INDEX plan_compare_articles_demo_list_idx
    ON plan_compare_articles_demo
      (category_id, published_at DESC, article_id DESC)
  `);
  const after = await client.query(query, values);
  if (JSON.stringify(before.rows) !== JSON.stringify(after.rows)) {
    throw new Error('Result mismatch between schema states');
  }
  await measure('after index');
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  try {
    await client.query('ROLLBACK');
  } finally {
    await client.end();
  }
}

Aynı dizinden, deneme veritabanınızın gerçek adını yazarak çalıştırın:

PGDATABASE=plan_lab node compare-plans.mjs

Program, SQL sorgularını ve parametre değerlerini client.query(text, values) aracılığıyla ayrı ayrı gönderir. Planı, döndürülen QUERY PLAN adlı sütundan okur; node-postgres sonuç sütunlarını nesne özelliklerine eşler ve PostgreSQL JSON verilerini JavaScript nesnelerine ayrıştırır. node-postgres: Sorgular, node-postgres: pg.Result, node-postgres: Veri Türleri

Tek başına kullanılan ANALYZE komutu, planlayıcı için tablo istatistiklerini toplar. Bu komut, EXPLAIN içindeki çalıştırma ölçümü yapan ANALYZE seçeneğinden farklıdır. Burada sentetik satırlar yüklendikten sonra istatistikleri toplamak, her iki aşamanın da tablo hakkında aynı başlangıç bilgisine sahip olmasını sağlar. PostgreSQL: ANALYZE

JavaScript’teki medyan ve tampon toplamı hesaplamaları, deterministik sentetik girdilerle kontrol edildi. Hazırlık sırasında araştırma ortamında PostgreSQL bulunmadığından veritabanı deneyinin tamamı çalıştırılmadı; makalede ölçülmüş kıyaslama sonuçları sunulmamaktadır.

İki planda neyin değişmesini beklemelisiniz?

Aday dizin, önce eşitlik filtresindeki kategoriyi, ardından istenen sıralama sütunlarını içerir. PostgreSQL belgelerine göre önde gelen B ağacı sütunlarındaki eşitlik koşulları, taranan dizin aralığını sınırlar. Bu nedenle bu dizin sorgu için mantıklı bir adaydır. PostgreSQL: Çok Sütunlu Dizinler

Dizin eklenmeden önce bir tarama ve sıralama, sonrasında ise limit düğümünün altında sıralı bir dizin taraması görmeyi bekleyin. Bunlar çıktınızda kontrol etmeniz gereken beklentilerdir; garanti edilen plan biçimleri değildir. Uygun bir B ağacı dizini sıralı satırları doğrudan sağlayabilir; sıralamanın limit ile birleştirilmesi, eşleşmelerin geri kalanını işleme gereğini ortadan kaldırabilir. PostgreSQL: Dizinler ve ORDER BY

Yazdırılan planlarda üç noktayı inceleyin:

  1. Kök düğümün her iki aşamada da 50 satır döndürdüğünü ve programın sonuçlarda uyuşmazlık bildirmediğini doğrulayın.
  2. Tarama ve sıralamadan sorumlu düğümleri bulun. Bu düğümlerdeki satır tahminlerini gerçek satırlarla karşılaştırın ve tampon etkinliğinin nerede oluştuğunu inceleyin.
  3. Kök düğümdeki paylaşılan erişimleri ve tekrarlanan çalıştırma sürelerini karşılaştırın. Yalnızca düğüm adlarının değişmesi, yararlı bir iyileşme olduğunu göstermez.

Bu, aşamalı ve sıcak önbellekle yapılan bir karşılaştırmadır. Satır ekleme, doğruluk sorguları, ısınma çalıştırmaları ve dizin oluşturma işlemlerinin tümü önbellek durumunu etkiler. Her iki aşamada da aynı Mac iş yükünü ve veritabanı yapılandırmasını kullanın. Olağan dışı yavaş bir çalıştırmanın görünür kalması için yedi ayrı gözlemi saklayın; yalnızca medyan değişkenliği gizleyebilir.

Daha güçlü bir takip çalışması için deneyi baştan tekrarlayın ve başka kategorileri ve limitleri sınayın. Daha büyük bir limiti denerseniz bunu ayrı bir iş yükü olarak etiketleyin. İlk karşılaştırma, gözlemlenen yerel koşullarda bu dizinin belirli 50 makalelik isteğe yardımcı olup olmadığını yanıtlar.

Daha az işi daha sıcak tamponlardan nasıl ayırt edersiniz?

Aşağıdaki sayılar alınmış bir çıktı değil, örnek girdilerdir. Okumaları, isabetleri ve süreyi neden birlikte değerlendirmeniz gerektiğini gösterirler:

Örnek çalıştırma Çalıştırma süresi Paylaşılan isabetler Paylaşılan okumalar İsabetler + okumalar
A, ilk gözlem 12 ms 900 100 1.000
A, tekrarlanan gözlem 10 ms 1.000 0 1.000
B, aday plan 3 ms 80 0 80

A’nın iki gözlemi arasında toplam paylaşılan erişimler 1.000’de kalırken okumalar ortadan kalkıyor. Bu, önbellek durumunun değişmesiyle tutarlıdır; paylaşılan tampon erişim hacminin azaldığını göstermez. B’de hem süre hem erişim sayısı daha düşüktür. Eşdeğer sonuçlarla bu örüntü tekrarlanırsa, B’nin bu görev için daha az paylaşılan tampon işi yaptığı yönündeki daha dar kapsamlı sonucu destekler.

Kendi sonuçlarınız farklıysa bu karar kılavuzunu kullanın:

Gözlemlenen örüntü Yorum ve sonraki adım
Daha kısa süre ve daha az paylaşılan erişim Test edilen iş yükü için iyileşme kanıtıdır; tutarlılığı kontrol etmek için tekrarlayın
Daha kısa süre, daha az okuma, benzer toplam erişim Değişimi önbellek durumu açıklayabilir; ısınmış tekrarları karşılaştırın
Daha az erişim ama benzer veya daha uzun süre Karar vermeden önce sıralamayı, ifadeleri ve süre değişkenliğini inceleyin
Hiç okuma yok ama çok sayıda isabet var Önbellekten yapılan çalıştırma da tampon erişimleri gerçekleştirir; etkinliğin yüksek olduğu düğümleri inceleyin
Geçici okuma veya yazmalar görünüyor Ayarları değiştirmeden önce bunlara yol açan işlem adımını inceleyin

Önbellek isabeti yüzdesini belirleyici ölçüte dönüştürmekten kaçının. Örnekte, ısınmış A ve B’nin paylaşılan tampon isabet oranı %100 olsa da erişim sayıları önemli ölçüde farklıdır. Ham sayıları ve sonuç boyutunu sürelerin yanında saklayın.

Bu ölçümler uygulama hızına nasıl karşılık gelir?

Sunucudaki Execution Time planlamayı içermez; normal EXPLAIN ANALYZE ayrıca sonuç aktarımını ve varsayılan olarak çıktı dönüşümünü de içermez. Araçlandırmanın kendisi ek yük getirir. Bu nedenle betik, makale listesinin gösterilmesi için geçen toplam süreyi değil, araçlandırılmış veritabanı çalıştırmasını karşılaştırır. PostgreSQL: EXPLAIN Kullanımı

PostgreSQL 17 veya 18’de çıktı dönüşümünü ayrıca incelemek için aynı açıklama seçeneklerine SERIALIZE TEXT ekleyin. PostgreSQL 16 bu seçeneği listelemez. Serileştirme yine de sorgu sonuç satırlarını aktarmaz; dolayısıyla ağ üzerinden teslim süresini ölçemez. Karşılaştırılan çalıştırmalarda serileştirme ayarlarını aynı tutun. PostgreSQL 17: EXPLAIN, PostgreSQL 16: EXPLAIN

Uygulama ölçümü için, bağlantı zaten kurulmuşken normal makale SELECT sorgusunu, sürücü çağrısının tamamlanmasını bekleyene kadar süre tutarak ölçün. Bunu ayrı bir istemci geçen süre ölçümü olarak kaydedin. Uygulamanın gerçek sorgu yapılandırmasını koruyun: node-postgres sorgusuna name eklemek, bağlantı başına hazırlanan ifade davranışını etkinleştirir ve karşılaştırma koşullarını değiştirir. node-postgres: Sorgular

Eşdeğer sonuçlar, tekrarlanan süreler ve tampon sayıları, sunmanız gereken makale isteği için tutarlı bir tablo ortaya koyuyorsa adayı seçin. Sonucun belirli ve tekrarlanabilir kalması için tüm planları, tek tek çalıştırmaları, sunucu sürümünü ve sorgu parametrelerini saklayın.

Kaynaklar