Tekrarlanan PostgreSQL sorgularını teşhis etmek için adayların oluşturulmasını adayların tüketilmesinden ayırın. İç taramanın yürütme sayısını okuyun, önbelleğe almanın tekrarlanan işi önleyip önlemediğini kontrol edin, ardından gruplandırılmış yeniden kullanımı, CTE satır içi genişletmesini veya dizinli bir geçici tabloyu karşılaştırın.

Bu anlatım, yavaş bir makale sorgusunu açıklaması gereken Mac kullanan bir geliştirici içindir: aday makaleleri bir kez seçin, ardından her makalenin konusu içindeki en yüksek aday makale kimliğini bulun. Baştan sona sentetik satırlar kullanılır. Amaç, işin nerede tekrarlandığını belirlemek ve sonucu koruyan bir sorgu biçimi seçmektir.

Tekrarlanabilir bir aday sorgusu hazırlama

psql kullanılabilir olan mevcut bir yerel PostgreSQL kurulumunu kullanın. Terminal'i açın, olağan bağlantı ayarlarınızla psql komutunu çalıştırın ve SQL örnekleri boyunca aynı oturumu açık tutun. PostgreSQL, psql aracını sorgu göndermek ve sonuçları görüntülemek için kullanılan etkileşimli terminali olarak belgeler. psql

Önce SHOW server_version; ile sunucu sürümünü kontrol edin. Bu makale için alınan güncel belgeler, PostgreSQL 18'i güncel sürüm olarak tanımlıyor. Örneklerde PostgreSQL 12 veya sonraki sürümlerin sözdizimi kullanılıyor; bu, özellik sınırıdır, eski bir sürüm kullanma önerisi değildir. PostgreSQL 12, CTE satır içi genişletmesini ve MATERIALIZED ile NOT MATERIALIZED denetimlerini kullanıma sundu. WITH Sorguları, Sürüm 12

Bu kurulumu yeni bir oturumda bir kez çalıştırın. Konulara ve kimliklere göre aramalar için bir dizinle birlikte 20.000 sentetik makale oluşturur. Aşağıda kullanılan aday koşulu ilk 2.000 makaleyi seçer.

CREATE TEMP TABLE article_demo (
    id integer PRIMARY KEY,
    topic_id integer NOT NULL,
    title text NOT NULL
);

INSERT INTO article_demo (id, topic_id, title)
SELECT g, 1 + (g % 50), 'Article ' || g
FROM generate_series(1, 20000) AS s(g);

CREATE INDEX article_demo_topic_id_idx
    ON article_demo (topic_id, id);

ANALYZE article_demo;

Bu geçici tablolar varsayılan olarak oturum boyunca kalır. Yükleme sonrasında ANALYZE komutunu açıkça çalıştırın: PostgreSQL'in autovacuum süreci geçici tabloları analiz etmez. CREATE TABLE

Şimdi temel ölçümü alın. Lateral alt sorgu, geçerli makalenin konusu için en yüksek aday kimliğini hesaplar. Burada “en yüksek kimlik” yalnızca tanımlanan görevdir; yayın tarihini ifade etmez.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Açıkça belirtilen MATERIALIZED, deneydeki yeniden kullanım sınırını netleştirir. Materyalize edilmiş bir CTE bir kez oluşturulabilirken tüketicileri depolanan satırları tekrar tekrar okuyabilir. PostgreSQL belgeleri, materyalize edilmiş ve birden çok kez başvurulan bir CTE'nin ara sonuçta arama yaparken temel tablonun dizinlerinden yararlanma avantajını nasıl kaybettiğini gösterir. Ortak Tablo İfadesi Materyalleştirmesi

Bu makaledeki SQL örnekleri incelendi ancak bir PostgreSQL sunucusunda çalıştırılmadı. Sentetik satır sayılarının hesabı ayrıca kontrol edildi; ölçüm olarak herhangi bir yürütme süresi veya kaydedilmiş plan sunulmuyor.

Tekrarlanan işi bulmak için planı okuma

Adayları üreten daldan başlayın, ardından her tüketiciyi izleyin. İç içe döngü altında dış girdiyi ve iç aramayı belirleyin. PostgreSQL, bir düğümün yürütme sayısını loops olarak raporlar; gerçek satır sayıları ve düğüm süreleri, yürütme başına ortalamalardır. İlişkili bir skaler alt sorgu sürümü bunun yerine tekrarlanan işi SubPlan altında gösterebilir. Bu nedenle teşhis, belirli bir etiketi zorunlu kılmak yerine yürütülen düğümleri izlemelidir. EXPLAIN Kullanımı

Gerçek planınızdan şu dört gözlemi kaydedin:

  1. Dış aramaya kaç satır giriyor?
  2. Alttaki iç tarama kaç kez yürütülüyor?
  3. Bu tarama adayları okuyup ardından çoğunu bir filtreyle eliyor mu?
  4. Dış satırlarla maliyetli iç iş arasında bir Memoize düğümü var mı?

Düzeyleri birbirinden ayrı değerlendirin. Bir toplama işlemi, çocuk düğümü hesaplama için çok sayıda satır tararken her yürütmede tek bir maksimum döndürebilir. Bu nedenle adayları üreten işlemin bir kez çalışması, aday tüketiminin düşük maliyetli olduğunu göstermez.

Bu örnekte 2.000 aday 50 konuya yayılır; her konuda 40 makale vardır. Aşağıdaki hesaplama olası iş örüntülerini açıklar; plan çıktısı veya çalışma süresi garantisi vermez:

Tüketim örüntüsü Örnek aday satırı inceleme sayısı
Her dış makale için aday kümesinin tamamını tarama 2.000 × 2.000 = 4.000.000
Tüm sonraki aramalar önbelleğe alınarak her konu için bir kez tam tarama 50 × 2.000 = 100.000
Adayları konuya göre bir kez gruplandırma Gruplandırma adımına giren 2.000 satır ve sonraki birleştirme işi

Bu ayrım, iç içe döngü sonuçlarının önbelleğe alınmasını kullanıma sunan PostgreSQL 14 ve sonraki sürümlerde önemlidir. Güncel belgeler Memoize düğümünü, parametreli tarama sonuçlarını önbelleğe alan ve önbellek isabetinde alttaki taramanın atlanmasına olanak tanıyan bir düğüm olarak açıklar; girdiler bellekten çıkarılabilir de. Binlerce dış satırı binlerce tam tarama olarak değerlendirmeden önce önbellek anahtarını ve raporlanan isabetleri, kaçırmaları, bellekten çıkarmaları ve taşmaları inceleyin. Sürüm 14, Sorgu Planlama

Ayrıca en erken belirgin uyuşmazlıktaki tahmini satır sayısıyla gerçek satır sayısını karşılaştırın. PostgreSQL bu tahminlerin kontrol edilmesini önerir ve küçük oyuncak tabloların planlarının daha büyük girdilere mutlaka genellenemeyeceği konusunda uyarır. Üst ve alt düğüm sürelerini toplamayın: bunlar örtüşen yürütme işlerini tanımlar. EXPLAIN Kullanımı

Tekrarlanan göreve uygun bir yeniden yazım seçme

Önemli olan, her tüketicinin aday kümesinden ne kadar işe ihtiyaç duyduğudur. İlk denemeyi seçmek için bu karar tablosunu kullanın:

Gözlenen gereksinim İlk deneme Korunması gereken koşul
Her adayın konu başına aynı maksimuma ihtiyacı var Bir kez gruplandırın, sonra birleştirin Her konu için tek özet satırı
Her tüketicinin kaynağın dizinlenmiş küçük bir bölümüne ihtiyacı var NOT MATERIALIZED deneyin Aday koşulunun tamamını koruyun
Birden fazla deyimin açık dizinlere sahip aynı aday ilişkisine ihtiyacı var Dizinli bir geçici tablo oluşturun Aynı oturumu kullanın ve kurulum maliyetini hesaba katın
Tekrarlanan parametreler zaten çok sayıda önbellek isabeti sağlıyor Biçimi değiştirmeden önce toplam yürütme süresini karşılaştırın Alttaki taramaların gerçekten maliyetli olduğunu doğrulayın

Konu başına sonucu bir kez hesaplama

Bu görev için gruplandırılmış bir özet, yeniden kullanımı doğrudan ifade eder. Kurulumdan sonra aynı oturumda aşağıdakini çalıştırın:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
), topic_max AS (
    SELECT topic_id, max(id) AS highest_id
    FROM candidate
    GROUP BY topic_id
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
JOIN topic_max AS m USING (topic_id);

Bu yeniden yazım her konu için bir maksimum üretir ve bunu her aday makaleye ekler. PostgreSQL, max işlevini boş olmayan girdi değerlerinin maksimumu olarak tanımlar. Örnekte konu ve kimlik sütunları boş olmadığından eşdeğerlik basittir: her aday, kendisini içeren bir gruba aittir. Toplama İşlevleri

Sorguyu EXPLAIN olmadan çalıştırdığınızda 2.000 sonuç satırı bekleyin. Örneğin, 1. makale 2. konuya aittir ve highest_id = 1951 değerini alır; 1. konu ise highest_id = 2000 değerini alır. Bunlar örnek oluşturucunun belirlenimci sonuçlarıdır.

Genelleme yapmadan önce sonuçların anlamını kontrol edin. Fazladan sütunlara göre gruplandırma, konu başına birden fazla özet satırı oluşturup birleştirme çıktısını çoğaltabilir. Boş konu kimliklerine izin verilmesi de eşitlik semantiğinin yeniden değerlendirilmesini gerektirir. Görev en yüksek kimlikli makalenin başlığını gerektiriyorsa maksimumu aday satırlarla yeniden birleştirmek ek bir adımdır; başlığın ayrı bir maksimumunu almak farklı bir soruyu yanıtlar.

Bu yeniden yazım farklı bir plana olanak tanır ancak karma birleştirmeyi zorunlu kılmaz veya hızlanmayı garanti etmez. Ölçülen gruplandırma ve birleştirme işini, temel ölçümdeki gerçek iç yürütmelerle karşılaştırın.

Kaynak dizinlerinin ara taramaların yerini alıp alamayacağını sınama

Uygun bir CTE için tam temel sorgudaki AS MATERIALIZED ifadesini AS NOT MATERIALIZED ile değiştirip sorguyu yeniden çalıştırın. Bu, kaynak ilişkisiyle birlikte optimizasyona olanak tanır ve depolanmış aday taramalarını olası kaynak aramalarıyla değiştirebilir. Aday hesaplamasını çoğaltması da mümkündür. Ortak Tablo İfadesi Materyalleştirmesi

Örnekteki (topic_id, id) dizini, baştaki konu sütununda eşitlik ve kimlik sütununda kısıtlama ile eşleşir. Bu nedenle denemek için makul bir dizindir; gerçek planın bunu kullanıp kullanmadığını ve ne kadar tarama yaptığını doğrulayın. PostgreSQL, çok sütunlu bir B-ağacı taramasını sınırlayan koşullar olarak baştaki sütunlarda eşitliği ve sonraki sütunda eşitsizliği belgeler. Çok Sütunlu Dizinler

id <= 2000 ifadesini aday tanımının içinde koruyun. 20.000 kaynak satırın tümünde maksimumu aramak sonucu değiştirir. Belgelenen sınırı da koruyun: NOT MATERIALIZED, özyinelemeli CTE'lerde ve yan etkisiz olmayan CTE'lerde yok sayılır. Uygun olmayan bir CTE için desteklenen ilişkisel bir yeniden yazım kullanın veya sonucunu açıkça geçici olarak hazırlayın. SELECT

Yeniden kullanım birden fazla deyime yayılıyorsa dizinli aday ilişkisini hazırlama

Bir CTE tek bir deyime aittir. Birden fazla deyimin aynı aday satırlarına ve açık dizinlere ihtiyacı varsa geçici tablo farklı bir yaşam süresi ve dizinleme seçeneği sunar. İlk kurulumdan sonra şu alternatifi çalıştırın:

CREATE TEMP TABLE candidate_demo AS
SELECT id, topic_id, title
FROM article_demo
WHERE id <= 2000;

CREATE INDEX candidate_demo_topic_id_idx
    ON candidate_demo (topic_id, id);

ANALYZE candidate_demo;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.id, c.title, m.highest_id
FROM candidate_demo AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate_demo AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Geçici tablo dizinleri de geçicidir ve ilişki oturum kapsamındadır. Hazırlamanın fayda sağlayıp sağlamadığını değerlendirirken oluşturma, dizin oluşturma ve analiz maliyetlerini hesaba katın. Yalnızca son aramayı, hazırlık yapılmamış işlemin tümüyle karşılaştırmayın. CREATE TABLE

Mac'inizde node-postgres ile aynı deneyi kaydetme

Uygulama sorgu yolunu incelemek için şu anda desteklenen bir Node.js sürümünü kullanın ve belgelenen paketi npm install pg ile yükleyin. Aşağıdaki tam programı projede diagnose.mjs olarak kaydedip node diagnose.mjs komutuyla çalıştırın. Program kendi sentetik girdisini oluşturur; dolayısıyla önceki psql oturumuna bağlı değildir. node-postgres

Program mevcut PostgreSQL bağlantı ortamınızı kullanır. node-postgres, psql tarafından kullanılanlarla aynı bağlantı ortamı değişkenlerini belgeler; bunların arasında PGHOST, PGPORT ve PGDATABASE bulunur. Bağlanma

import pg from 'pg';
const { Client } = pg;
const client = new Client();

try {
  await client.connect();
  const version = await client.query('SHOW server_version');
  console.log(version.rows[0]);

  await client.query(`
    CREATE TEMP TABLE article_demo (
      id integer PRIMARY KEY,
      topic_id integer NOT NULL,
      title text NOT NULL
    );
    INSERT INTO article_demo (id, topic_id, title)
    SELECT g, 1 + (g % 50), 'Article ' || g
    FROM generate_series(1, 20000) AS s(g);
    CREATE INDEX article_demo_topic_id_idx
      ON article_demo (topic_id, id);
    ANALYZE article_demo;
  `);

  const sql = `
    WITH candidate AS MATERIALIZED (
      SELECT id, topic_id, title
      FROM article_demo
      WHERE id <= $1::integer
    )
    SELECT c.id, c.title, m.highest_id
    FROM candidate AS c
    CROSS JOIN LATERAL (
      SELECT max(x.id) AS highest_id
      FROM candidate AS x
      WHERE x.topic_id = c.topic_id
    ) AS m
  `;

  const result = await client.query({
    text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) ${sql}`,
    values: [2000]
  });

  for (const row of result.rows) {
    console.log(row['QUERY PLAN']);
  }
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await client.end();
}

Sorgu yapılandırması, belgelenmiş text ve values alanlarını kullanır. name eklemek her bağlantıda bir hazırlanmış deyim oluşturur; aday satırlarını önbelleğe almaz veya tekrarlanan iç yürütmeyi ortadan kaldırmaz. Sorgular

Mevcut uygulama bir havuz kullanıyorsa geçici tabloyu oluşturma ve kullanma işlemlerini aynı ödünç alınmış istemcide tutup finally içinde serbest bırakın. Ayrı pool.query çağrıları farklı istemcileri kullanabilir. Benzer şekilde node-postgres, bir işlemdeki tüm deyimlerin aynı istemci örneğini kullanmasını gerektirir. Havuzlama, İşlemler

Parametre değerlerine göre davranışı değişen adlandırılmış deyimler için planlamayı ayrıca inceleyin. PostgreSQL genel veya parametreye özgü özel planlar seçebilir; belgelenmiş inceleme yöntemi, hazırlanmış deyim için EXPLAIN EXECUTE komutudur. Bu nedenle yeni açıklanan adsız bir sorgu, uzun süredir kullanılan hazırlanmış bir yürütmeden farklı olabilir. PREPARE

Tekrarlanan işin gerçekten iyileşip iyileşmediğine karar verme

Her karşılaştırmada aynı aday koşulunu, seçilen sütunları ve girdi değerlerini kullanın. Önbellek değişimini gözlemlemek için önce bir geçiş çalıştırın, ardından karşılaştırılabilir geçişleri tekrarlayın. Sunucu sürümünü, aday sayısını, iç tarama yürütmelerini, önbellek davranışını ve toplam yürütme süresini kaydedin. PostgreSQL 17, sütun istatistiklerini ve sıralama düzenini kullanarak CTE planlamasını iyileştirdi; bu nedenle farklı makinelerden gelen sonuçları karşılaştırırken sürümü de kaydedin. Sürüm 17

İkinci bir tanılama geçişinde EXPLAIN (ANALYZE, BUFFERS, TIMING OFF), düğüm başına saat ölçümlerinden kaçınırken satır sayılarını ve deyim yürütme süresini korur. Üst düğümlerdeki arabellek toplamları alt düğümlerin etkinliğini de içerir; bu nedenle tüm ağacı toplamayın. EXPLAIN ANALYZE sorguyu yürütür ancak sonuç satırlarının Node.js'e aktarılma süresini ölçmez. EXPLAIN

Planlayıcı anahtarlarını deney olarak değerlendirin. enable_nestloop değerini kapatmak iç içe döngüleri caydırır ancak bunları tamamen ortadan kaldıramaz; enable_material de tüm materyalleştirmeleri engelleyemez. Bu anahtarlardan hiçbiri aday tüketiminin verimli hale geldiğini göstermez. Sorgu Planlama

Sonuçta gerekli satırları koruyan ve görevin tümü için ölçülen işi azaltan sorgu biçimini seçin. Konu başına maksimum görevi için gruplandırılmış yeniden kullanım en açık ilk denemedir. Uygun oldukları kanıtlanırsa dizinli aramaları koruyun; açık hazırlığı ise ek kurulum maliyeti tekrarlanan tüketiciler boyunca karşılığını veriyorsa kullanın.

Kaynaklar