Untuk mendiagnosis pencarian PostgreSQL berulang, pisahkan pembuatan kandidat dari penggunaan kandidat. Periksa jumlah eksekusi pemindaian bagian dalam, pastikan apakah cache mencegah pekerjaan berulang, lalu bandingkan penggunaan ulang yang dikelompokkan, inlining CTE, atau tabel sementara berindeks.

Panduan ini ditujukan bagi pengembang yang bekerja di Mac dan perlu menjelaskan kueri artikel yang lambat: pilih artikel kandidat sekali, lalu cari ID artikel kandidat tertinggi dalam topik setiap artikel. Contoh ini menggunakan baris sintetis dari awal hingga akhir. Tujuannya adalah mengidentifikasi bagian yang mengulang pekerjaan dan memilih bentuk kueri yang mempertahankan hasilnya.

Siapkan kueri kandidat yang dapat direproduksi

Gunakan instalasi PostgreSQL lokal yang sudah ada dengan psql. Buka Terminal, jalankan psql dengan pengaturan koneksi yang biasa Anda gunakan, dan biarkan sesi yang sama tetap terbuka selama seluruh contoh SQL. PostgreSQL mendokumentasikan psql sebagai terminal interaktif untuk mengirimkan kueri dan melihat hasilnya. psql

Pertama, periksa versi server dengan SHOW server_version;. Dokumentasi terkini yang diperoleh untuk artikel ini menyebut PostgreSQL 18 sebagai versi terbaru. Contoh ini menggunakan sintaks PostgreSQL 12 atau lebih baru; ini adalah batas fitur, bukan rekomendasi untuk menggunakan rilis yang lebih lama. PostgreSQL 12 memperkenalkan inlining CTE serta kontrol MATERIALIZED dan NOT MATERIALIZED. Kueri WITH, Rilis 12

Jalankan penyiapan ini sekali dalam sesi baru. Penyiapan ini membuat 20.000 artikel sintetis dan indeks untuk pencarian berdasarkan topik dan ID. Predikat kandidat yang digunakan di bawah memilih 2.000 artikel pertama.

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;

Tabel sementara ini secara bawaan bertahan selama sesi. Jalankan ANALYZE secara eksplisit setelah memuat data: proses autovacuum PostgreSQL tidak menganalisis tabel sementara. CREATE TABLE

Sekarang ambil data dasar. Subkueri lateral menghitung ID kandidat tertinggi untuk topik artikel saat ini. Di sini, “ID tertinggi” hanyalah tugas yang ditetapkan; ini tidak menyiratkan tanggal publikasi.

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;

MATERIALIZED yang eksplisit memperjelas batas penggunaan ulang dalam eksperimen ini. CTE yang dimaterialisasi dapat dibuat sekali sementara konsumennya membaca baris tersimpan itu berulang kali. Dokumentasi PostgreSQL menjelaskan bahwa CTE bermaterialisasi yang dirujuk beberapa kali kehilangan manfaat indeks tabel yang mendasarinya saat melakukan pencarian pada hasil antara tersebut. Materialisasi Ekspresi Tabel Umum

Contoh SQL telah diperiksa, tetapi tidak dijalankan pada server PostgreSQL untuk artikel ini. Perhitungan jumlah baris sintetis diperiksa secara terpisah; waktu eksekusi maupun rencana kueri yang ditangkap tidak disajikan sebagai hasil pengukuran.

Baca rencana untuk menemukan pekerjaan berulang

Mulailah dari cabang yang menghasilkan kandidat, lalu telusuri setiap konsumennya. Di bawah nested loop, identifikasi masukan luar dan pencarian bagian dalam. PostgreSQL melaporkan loops sebagai jumlah eksekusi suatu simpul; jumlah baris aktual dan waktu simpul merupakan rata-rata per eksekusi. Versi subkueri skalar berkorelasi mungkin menampilkan pekerjaan berulang di bawah SubPlan, jadi diagnosis harus mengikuti simpul yang dieksekusi dan tidak mensyaratkan label tertentu. Menggunakan EXPLAIN

Catat empat pengamatan dari rencana kueri aktual Anda:

  1. Berapa banyak baris yang masuk ke pencarian luar?
  2. Berapa kali pemindaian bagian dalam yang mendasari dijalankan?
  3. Apakah pemindaian itu membaca kandidat lalu membuang sebagian besar baris melalui filter?
  4. Apakah ada simpul Memoize di antara baris luar dan pekerjaan bagian dalam yang mahal?

Pisahkan setiap tingkat. Agregat mungkin mengembalikan satu nilai maksimum per eksekusi, sementara simpul anaknya memindai banyak baris untuk menghitungnya. Karena itu, produsen kandidat yang hanya berjalan sekali tidak membuktikan bahwa penggunaan kandidat itu murah.

Untuk contoh persis ini, 2.000 kandidat mencakup 50 topik, dengan 40 artikel per topik. Perhitungan berikut menggambarkan pola pekerjaan yang mungkin terjadi, bukan keluaran rencana atau waktu jalan yang dijamin:

Pola penggunaan Perkiraan pemeriksaan baris kandidat
Pemindaian penuh kandidat untuk setiap artikel luar 2.000 × 2.000 = 4.000.000
Pemindaian penuh sekali per topik, dengan semua pencarian berikutnya disimpan di cache 50 × 2.000 = 100.000
Kelompokkan kandidat sekali berdasarkan topik 2.000 baris masuk ke langkah pengelompokan, ditambah pekerjaan join berikutnya

Perbedaan ini penting pada PostgreSQL 14 dan versi yang lebih baru, yang memperkenalkan memoization hasil nested loop bagian dalam. Dokumentasi terkini menjelaskan Memoize sebagai mekanisme yang menyimpan hasil pemindaian berparameter dan memungkinkan pemindaian yang mendasarinya dilewati saat terjadi cache hit; entri juga dapat dikeluarkan dari cache. Periksa kunci cache serta hit, miss, pengeluaran, dan luapan yang dilaporkan sebelum menganggap ribuan baris luar sebagai ribuan pemindaian penuh. Rilis 14, Perencanaan Kueri

Bandingkan juga perkiraan baris dengan baris aktual pada ketidakcocokan substansial paling awal. PostgreSQL menyarankan untuk memeriksa perkiraan ini dan mengingatkan bahwa rencana tabel kecil belum tentu berlaku pada masukan yang lebih besar. Hindari menjumlahkan waktu induk dan anak: keduanya menggambarkan pekerjaan eksekusi yang saling tumpang tindih. Menggunakan EXPLAIN

Pilih penulisan ulang yang sesuai dengan pekerjaan berulang

Pertanyaan yang berguna adalah seberapa banyak pekerjaan yang dibutuhkan setiap konsumen dari kumpulan kandidat. Gunakan panduan keputusan ini untuk memilih eksperimen pertama:

Kebutuhan yang diamati Eksperimen pertama Syarat yang harus dipertahankan
Setiap kandidat membutuhkan maksimum per topik yang sama Kelompokkan sekali, lalu lakukan join Satu baris ringkasan per topik
Setiap konsumen membutuhkan sebagian kecil sumber yang dapat diindeks Coba NOT MATERIALIZED Pertahankan predikat kandidat lengkap
Beberapa pernyataan memerlukan relasi kandidat berindeks secara eksplisit Buat tabel sementara berindeks Gunakan sesi yang sama dan sertakan biaya penyiapan
Parameter berulang sudah menghasilkan banyak cache hit Bandingkan total waktu eksekusi sebelum mengubah bentuk Pastikan pemindaian yang mendasarinya memang mahal

Hitung hasil per topik sekali

Untuk tugas ini, ringkasan berkelompok menyatakan penggunaan ulang secara langsung. Jalankan perintah berikut setelah penyiapan, dalam sesi yang sama:

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);

Penulisan ulang ini menghasilkan satu nilai maksimum untuk setiap topik dan menyertakannya pada setiap artikel kandidat. PostgreSQL mendefinisikan max sebagai nilai masukan non-null maksimum. Kolom topik dan ID pada contoh ini tidak berisi null, sehingga kesetaraannya jelas: setiap kandidat termasuk dalam kelompok yang memuat dirinya sendiri. Fungsi Agregat

Kueri ini akan menghasilkan 2.000 baris saat dijalankan tanpa EXPLAIN. Sebagai contoh, artikel 1 termasuk dalam topik 2 dan memperoleh highest_id = 1951; topik 1 memperoleh highest_id = 2000. Hasil ini merupakan konsekuensi deterministik dari pembuat data contoh.

Periksa makna hasil sebelum melakukan generalisasi. Mengelompokkan berdasarkan kolom tambahan dapat menghasilkan beberapa baris ringkasan per topik dan menggandakan keluaran join. Jika ID topik boleh bernilai null, semantik kesetaraan juga perlu ditinjau kembali. Jika tugasnya mengharuskan judul artikel dengan ID tertinggi, menggabungkan nilai maksimum kembali ke baris kandidat merupakan langkah tambahan; mengambil nilai maksimum judul secara terpisah akan menjawab pertanyaan yang berbeda.

Penulisan ulang ini memungkinkan rencana yang berbeda, tetapi tidak mewajibkan hash join atau menjamin percepatan. Bandingkan pekerjaan pengelompokan dan join yang terukur dengan jumlah eksekusi bagian dalam aktual pada data dasar.

Uji apakah indeks sumber dapat menggantikan pemindaian perantara

Untuk CTE yang memenuhi syarat, ganti AS MATERIALIZED dalam kueri data dasar lengkap dengan AS NOT MATERIALIZED, lalu jalankan lagi. Ini memungkinkan optimasi bersama dengan relasi sumber, yang berpotensi menukar pemindaian kandidat tersimpan dengan pencarian pada sumber. Cara ini juga dapat mengulang perhitungan kandidat. Materialisasi Ekspresi Tabel Umum

Indeks (topic_id, id) pada contoh ini cocok dengan kesetaraan pada kolom topik pertama dan pembatasan pada kolom ID. Karena itu, indeks ini layak diuji; verifikasi apakah rencana aktual menggunakannya dan seberapa banyak data yang dipindai. PostgreSQL mendokumentasikan bahwa kesetaraan pada kolom pertama dan ketidaksamaan pada kolom berikutnya merupakan kondisi yang membatasi pemindaian B-tree multikolom. Indeks Multikolom

Pertahankan id <= 2000 di dalam definisi kandidat. Mencari nilai maksimum pada seluruh 20.000 baris sumber akan mengubah jawaban. Pertahankan juga batas yang didokumentasikan: NOT MATERIALIZED diabaikan untuk CTE rekursif dan CTE yang memiliki efek samping. Untuk CTE yang tidak memenuhi syarat, gunakan penulisan ulang relasional yang didukung atau simpan hasilnya secara eksplisit. SELECT

Simpan kandidat dalam relasi berindeks saat penggunaan ulang mencakup beberapa pernyataan

CTE hanya berlaku untuk satu pernyataan. Jika beberapa pernyataan memerlukan baris kandidat yang sama beserta indeks eksplisit, tabel sementara menyediakan pilihan masa berlaku dan pengindeksan yang berbeda. Jalankan alternatif ini setelah penyiapan awal:

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;

Indeks tabel sementara juga bersifat sementara, dan relasinya hanya berlaku untuk sesi tersebut. Sertakan pembuatan tabel, pembuatan indeks, dan analisis saat menilai apakah penyimpanan bertahap membantu keseluruhan tugas. Jangan hanya membandingkan pencarian akhirnya dengan seluruh operasi tanpa penyimpanan bertahap. CREATE TABLE

Rekam eksperimen yang sama dengan node-postgres di Mac Anda

Untuk memeriksa jalur kueri aplikasi, gunakan rilis Node.js yang saat ini didukung dan instal paket yang didokumentasikan dengan npm install pg. Simpan program lengkap di bawah ini sebagai diagnose.mjs dalam proyek tersebut, lalu jalankan node diagnose.mjs. Program ini membuat masukan sintetisnya sendiri, sehingga tidak bergantung pada sesi psql sebelumnya. node-postgres

Program ini menggunakan lingkungan koneksi PostgreSQL yang sudah ada. node-postgres mendokumentasikan variabel lingkungan koneksi yang sama dengan yang digunakan oleh psql, termasuk PGHOST, PGPORT, dan PGDATABASE. Menghubungkan

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();
}

Konfigurasi kueri menggunakan kolom text dan values yang didokumentasikan. Menambahkan name membuat pernyataan yang disiapkan pada setiap koneksi; ini tidak menyimpan baris kandidat di cache atau menghapus eksekusi bagian dalam yang berulang. Kueri

Jika aplikasi yang sudah ada menggunakan pool, jalankan pembuatan dan penggunaan tabel sementara pada satu klien yang sudah dipinjam dari pool, lalu lepaskan di finally. Pemanggilan pool.query yang terpisah dapat menggunakan klien yang berbeda. Demikian pula, node-postgres mengharuskan semua pernyataan dalam transaksi menggunakan instans klien yang sama. Pooling, Transaksi

Untuk pernyataan bernama yang perilakunya berubah sesuai nilai parameter, selidiki perencanaan secara terpisah. PostgreSQL dapat memilih rencana generik atau rencana khusus parameter; cara pemeriksaan yang didokumentasikan adalah EXPLAIN EXECUTE untuk pernyataan yang sudah disiapkan. Karena itu, kueri tanpa nama yang baru dijelaskan dapat berbeda dari eksekusi pernyataan yang sudah disiapkan dan telah berjalan. PREPARE

Pastikan apakah pekerjaan berulang benar-benar berkurang

Gunakan predikat kandidat, kolom yang dipilih, dan nilai masukan yang sama untuk setiap perbandingan. Jalankan satu putaran awal, lalu ulangi putaran yang sebanding untuk mengamati variasi cache. Catat versi server, jumlah kandidat, jumlah eksekusi pemindaian bagian dalam, perilaku cache, dan total waktu eksekusi. PostgreSQL 17 meningkatkan perencanaan CTE dengan menggunakan statistik kolom dan urutan pengurutan, jadi catat versinya saat membandingkan hasil dari mesin yang berbeda. Rilis 17

Untuk putaran diagnosis kedua, EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) mempertahankan jumlah baris dan waktu eksekusi pernyataan sekaligus menghindari pengukuran waktu per simpul. Total buffer pada simpul induk mencakup aktivitas anak, jadi jangan menjumlahkan seluruh pohon. EXPLAIN ANALYZE mengeksekusi kueri, tetapi tidak mengukur pengiriman baris hasilnya ke Node.js. EXPLAIN

Perlakukan pengaturan perencana sebagai eksperimen. Menonaktifkan enable_nestloop mengurangi kecenderungan penggunaan nested loop, tetapi tidak dapat menghilangkannya sepenuhnya; enable_material juga tidak dapat meniadakan semua materialisasi. Kedua pengaturan itu tidak membuktikan bahwa penggunaan kandidat menjadi efisien. Perencanaan Kueri

Akhiri dengan bentuk kueri yang mempertahankan baris yang dibutuhkan dan mengurangi pekerjaan terukur untuk keseluruhan tugas. Untuk maksimum per topik ini, penggunaan ulang yang dikelompokkan adalah eksperimen pertama yang paling jelas. Pertahankan pencarian berindeks jika terbukti murah, dan gunakan penyimpanan eksplisit jika biaya penyiapannya sepadan untuk konsumen berulang.

Sumber