Untuk mendiagnosis carian PostgreSQL berulang, asingkan pembinaan calon daripada penggunaan calon. Periksa bilangan pelaksanaan imbasan dalaman, semak sama ada cache mengelakkan kerja berulang, kemudian bandingkan penggunaan semula berkumpulan, pengembangan sebaris CTE atau jadual sementara berindeks.

Panduan ini ditujukan kepada pembangun yang menggunakan Mac dan perlu menerangkan pertanyaan artikel yang perlahan: pilih calon artikel sekali, kemudian cari ID artikel calon tertinggi dalam topik setiap artikel. Contoh ini menggunakan baris sintetik sepanjang panduan. Matlamatnya ialah mengenal pasti bahagian yang mengulangi kerja dan memilih bentuk pertanyaan yang mengekalkan hasilnya.

Sediakan pertanyaan calon yang boleh diulang

Gunakan pemasangan PostgreSQL setempat sedia ada yang mempunyai psql. Buka Terminal, jalankan psql dengan tetapan sambungan biasa anda, dan kekalkan sesi yang sama sepanjang contoh SQL. PostgreSQL menerangkan psql sebagai terminal interaktifnya untuk menghantar pertanyaan dan melihat hasil. psql

Mula-mula, semak versi pelayan dengan SHOW server_version;. Dokumentasi semasa yang dirujuk untuk artikel ini mengenal pasti PostgreSQL 18 sebagai versi terkini. Contoh ini menggunakan sintaks PostgreSQL 12 atau lebih baharu; ini ialah sempadan ciri, bukan saranan untuk menggunakan keluaran yang lebih lama. PostgreSQL 12 memperkenalkan pengembangan sebaris CTE serta kawalan MATERIALIZED dan NOT MATERIALIZED. Pertanyaan WITH, Keluaran 12

Jalankan persediaan ini sekali dalam sesi baharu. Ia mencipta 20,000 artikel sintetik dan indeks untuk carian berdasarkan topik dan ID. Predikat calon 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;

Jadual sementara ini kekal sepanjang sesi secara lalai. Jalankan ANALYZE secara jelas selepas memuatkannya: proses autovacuum PostgreSQL tidak menganalisis jadual sementara. CREATE TABLE

Sekarang, ambil garis dasar. Subpertanyaan lateral mengira ID calon tertinggi bagi topik artikel semasa. Di sini, “ID tertinggi” sekadar tugasan yang ditakrifkan; ia tidak membawa maksud tarikh penerbitan.

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 dinyatakan secara jelas menjadikan sempadan penggunaan semula eksperimen ini jelas. CTE yang dimaterialkan boleh dibina sekali sementara penggunaannya membaca baris tersimpan itu berulang kali. Dokumentasi PostgreSQL menunjukkan bagaimana CTE yang dimaterialkan dan dirujuk berbilang kali kehilangan manfaat indeks jadual asas apabila mencari dalam hasil perantaraan tersebut. Pematerialan Common Table Expression

Contoh SQL telah diperiksa tetapi tidak dijalankan pada pelayan PostgreSQL untuk artikel ini. Pengiraan bilangan baris sintetik disemak secara berasingan; tiada masa pelaksanaan atau pelan yang dirakam dikemukakan sebagai ukuran.

Baca pelan untuk mencari kerja berulang

Mulakan pada cabang yang menghasilkan calon, kemudian ikuti setiap penggunaannya. Dalam gelung bersarang, kenal pasti input luar dan carian dalaman. PostgreSQL melaporkan loops sebagai bilangan pelaksanaan sesuatu nod; baris sebenar dan masa nod ialah purata bagi setiap pelaksanaan. Versi subpertanyaan skalar berkorelasi mungkin sebaliknya menunjukkan kerja berulang di bawah SubPlan, jadi diagnosis perlu mengikuti nod yang benar-benar dilaksanakan dan bukannya mensyaratkan label tertentu. Menggunakan EXPLAIN

Catat empat pemerhatian daripada pelan sebenar anda:

  1. Berapa banyak baris memasuki carian luar?
  2. Berapa kali imbasan dalaman yang mendasarinya dilaksanakan?
  3. Adakah imbasan itu membaca calon kemudian membuang kebanyakannya melalui penapis?
  4. Adakah terdapat nod Memoize antara baris luar dengan kerja dalaman yang mahal?

Pastikan setiap aras dibezakan. Agregat mungkin mengembalikan satu maksimum bagi setiap pelaksanaan, sedangkan nod anaknya mengimbas banyak baris untuk mengiranya. Oleh itu, pengeluar calon yang berjalan sekali tidak membuktikan bahawa penggunaan calon itu murah.

Bagi sampel khusus ini, 2,000 calon merangkumi 50 topik, dengan 40 artikel bagi setiap topik. Pengiraan berikut menggambarkan corak kerja yang mungkin, bukan output pelan atau masa jalan yang dijanjikan:

Corak penggunaan Anggaran pemeriksaan baris calon
Imbasan penuh calon bagi setiap artikel luar 2,000 × 2,000 = 4,000,000
Imbasan penuh sekali bagi setiap topik, dengan semua carian seterusnya dicache 50 × 2,000 = 100,000
Kumpulkan calon sekali mengikut topik 2,000 baris memasuki langkah pengumpulan, ditambah kerja cantuman seterusnya

Perbezaan ini penting pada PostgreSQL 14 dan lebih baharu, yang memperkenalkan pememoan hasil gelung bersarang dalaman. Dokumentasi semasa menerangkan Memoize sebagai nod yang mencache hasil imbasan berparameter dan membolehkan imbasan asas dilangkau apabila cache sepadan; entri juga boleh disingkirkan. Periksa kunci cache serta sebarang bilangan padanan, ketiadaan padanan, penyingkiran dan limpahan yang dilaporkan sebelum menganggap ribuan baris luar menyebabkan ribuan imbasan penuh. Keluaran 14, Perancangan Pertanyaan

Bandingkan juga anggaran baris dengan baris sebenar pada ketidakpadanan besar yang terawal. PostgreSQL menyarankan supaya anggaran ini diperiksa dan mengingatkan bahawa pelan jadual contoh tidak semestinya boleh digunakan untuk mengunjurkan input yang lebih besar. Elakkan menjumlahkan masa nod induk dan anak: masa itu merangkumi kerja pelaksanaan yang bertindih. Menggunakan EXPLAIN

Pilih penulisan semula yang sepadan dengan kerja berulang

Soalan yang berguna ialah berapa banyak kerja yang diperlukan oleh setiap pengguna daripada set calon. Gunakan panduan keputusan ini untuk memilih eksperimen pertama:

Keperluan yang diperhatikan Eksperimen pertama Syarat yang perlu dikekalkan
Setiap calon memerlukan maksimum per topik yang sama Kumpulkan sekali, kemudian cantumkan Satu baris ringkasan bagi setiap topik
Setiap pengguna memerlukan sebahagian kecil sumber yang berindeks Cuba NOT MATERIALIZED Kekalkan predikat calon penuh
Beberapa pernyataan memerlukan hubungan calon berindeks secara jelas Cipta jadual sementara berindeks Kekalkan sesi yang sama dan ambil kira kos persediaan
Parameter berulang sudah menghasilkan banyak padanan cache Bandingkan jumlah masa pelaksanaan sebelum mengubah bentuk Sahkan bahawa imbasan asas benar-benar mahal

Kira hasil per topik sekali

Bagi tugasan ini, ringkasan berkumpulan menyatakan penggunaan semula secara langsung. Jalankan yang berikut selepas persediaan 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 semula ini menghasilkan satu maksimum bagi setiap topik dan melampirkannya pada setiap artikel calon. PostgreSQL mentakrifkan max sebagai nilai maksimum input bukan null. Lajur topik dan ID sampel yang bukan null menjadikan kesetaraan ini mudah: setiap calon tergolong dalam kumpulan yang mengandungi dirinya sendiri. Fungsi Agregat

Jangkakan 2,000 baris hasil apabila menjalankan pertanyaan tanpa EXPLAIN. Sebagai contoh, artikel 1 tergolong dalam topik 2 dan menerima highest_id = 1951; topik 1 menerima highest_id = 2000. Semua ini ialah akibat yang boleh ditentukan daripada penjana sampel.

Semak makna hasil sebelum membuat generalisasi. Pengumpulan mengikut lajur tambahan boleh menghasilkan beberapa baris ringkasan bagi setiap topik dan menggandakan output cantuman. Membenarkan ID topik null juga memerlukan semakan semula semantik kesamaan. Jika tugasan memerlukan tajuk artikel ber-ID tertinggi, cantumkan maksimum itu kembali kepada baris calon sebagai langkah tambahan; mengambil maksimum tajuk secara berasingan akan menjawab soalan yang berbeza.

Penulisan semula ini membolehkan pelan yang berbeza, tetapi tidak mewajibkan cantuman cincangan atau menjamin peningkatan kelajuan. Bandingkan kerja pengumpulan dan cantuman yang diukur dengan pelaksanaan dalaman sebenar garis dasar.

Uji sama ada indeks sumber boleh menggantikan imbasan perantaraan

Bagi CTE yang layak, gantikan AS MATERIALIZED dalam pertanyaan garis dasar penuh dengan AS NOT MATERIALIZED, kemudian jalankan semula. Ini membolehkan pengoptimuman bersama dengan hubungan sumber, yang mungkin menukar imbasan calon tersimpan kepada carian sumber. Ia juga boleh menggandakan pengiraan calon. Pematerialan Common Table Expression

Indeks (topic_id, id) sampel sepadan dengan kesamaan pada lajur topik pertama dan sekatan pada lajur ID. Oleh itu, indeks ini munasabah untuk diuji; sahkan sama ada pelan sebenar menggunakannya dan berapa banyak yang diimbas. PostgreSQL mendokumenkan kesamaan pada lajur pertama dan ketaksamaan pada lajur seterusnya sebagai syarat yang mengehadkan imbasan B-tree berbilang lajur. Indeks Berbilang Lajur

Kekalkan id <= 2000 dalam takrif calon. Mencari maksimum merentas semua 20,000 baris sumber akan mengubah jawapannya. Kekalkan juga batasan yang didokumenkan: NOT MATERIALIZED diabaikan untuk CTE rekursif dan CTE yang mempunyai kesan sampingan. Bagi CTE yang tidak layak, gunakan penulisan semula hubungan yang disokong atau pentaskan hasilnya secara jelas. SELECT

Pentaskan hubungan calon berindeks apabila penggunaan semula merentasi pernyataan

CTE terpakai pada satu pernyataan. Jika beberapa pernyataan memerlukan baris calon yang sama serta indeks yang jelas, jadual sementara menyediakan pilihan tempoh hayat dan pengindeksan yang berbeza. Jalankan alternatif ini selepas persediaan asal:

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 jadual sementara juga bersifat sementara, dan hubungan itu terhad kepada sesi. Ambil kira penciptaan, pembinaan indeks dan analisis ketika menilai sama ada pementasan membantu keseluruhan tugasan. Jangan bandingkan carian akhirnya sahaja dengan keseluruhan operasi tanpa pementasan. CREATE TABLE

Tangkap eksperimen yang sama dengan node-postgres pada Mac anda

Untuk memeriksa laluan pertanyaan aplikasi, gunakan keluaran Node.js yang masih disokong dan pasang pakej yang didokumenkan dengan npm install pg. Simpan program lengkap di bawah sebagai diagnose.mjs dalam projek itu dan jalankan node diagnose.mjs. Program ini mencipta input sintetiknya sendiri, jadi ia tidak bergantung pada sesi psql yang terdahulu. node-postgres

Program ini menggunakan persekitaran sambungan PostgreSQL sedia ada anda. node-postgres mendokumenkan pemboleh ubah persekitaran sambungan yang sama seperti yang digunakan oleh psql, termasuk PGHOST, PGPORT dan PGDATABASE. Menyambung

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 pertanyaan menggunakan medan text dan values yang didokumenkan. Menambah name mencipta pernyataan yang disediakan pada setiap sambungan; ia tidak mencache baris calon atau menghapuskan pelaksanaan dalaman berulang. Pertanyaan

Jika aplikasi sedia ada menggunakan pool, pastikan penciptaan dan penggunaan jadual sementara berlaku pada klien yang sama yang telah diperoleh, kemudian lepaskannya dalam finally. Panggilan pool.query yang berasingan boleh menggunakan klien yang berbeza. Begitu juga, node-postgres menghendaki semua pernyataan dalam transaksi menggunakan tika klien yang sama. Pengumpulan, Transaksi

Bagi pernyataan bernama yang tingkah lakunya berubah mengikut nilai parameter, teliti perancangan secara berasingan. PostgreSQL boleh memilih pelan generik atau pelan tersuai khusus parameter; laluan pemeriksaan yang didokumenkan ialah EXPLAIN EXECUTE untuk pernyataan yang disediakan itu. Oleh itu, pertanyaan tanpa nama yang baru dijelaskan mungkin berbeza daripada pelaksanaan yang telah mantap dan disediakan. PREPARE

Tentukan sama ada kerja berulang benar-benar bertambah baik

Gunakan predikat calon, lajur terpilih dan nilai input yang sama bagi setiap perbandingan. Jalankan satu pusingan awal dan ulangi pusingan yang setanding untuk memerhatikan variasi cache. Catat versi pelayan, bilangan calon, pelaksanaan imbasan dalaman, tingkah laku cache dan jumlah masa pelaksanaan. PostgreSQL 17 menambah baik perancangan CTE menggunakan statistik lajur dan susunan isihan, jadi catat versi apabila membandingkan hasil daripada mesin yang berbeza. Keluaran 17

Untuk pusingan diagnostik kedua, EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) mengekalkan kiraan baris dan masa pelaksanaan pernyataan sambil mengelakkan pengukuran masa bagi setiap nod. Jumlah penimbal pada nod induk merangkumi aktiviti nod anak, jadi jangan jumlahkan seluruh pepohon. EXPLAIN ANALYZE melaksanakan pertanyaan tetapi tidak mengukur penghantaran baris hasilnya kepada Node.js. EXPLAIN

Anggap suis perancang sebagai eksperimen. Mematikan enable_nestloop mengurangkan kecenderungan penggunaan gelung bersarang tetapi tidak dapat menghapuskannya sepenuhnya; enable_material juga tidak dapat menyekat semua pematerialan. Kedua-dua suis itu tidak membuktikan bahawa penggunaan calon telah menjadi cekap. Perancangan Pertanyaan

Akhiri dengan bentuk pertanyaan yang mengekalkan baris yang diperlukan dan mengurangkan kerja yang diukur bagi keseluruhan tugasan. Bagi maksimum per topik ini, penggunaan semula berkumpulan ialah eksperimen pertama yang paling jelas. Kekalkan carian berindeks apabila terbukti murah, dan gunakan pementasan secara jelas apabila kos persediaan tambahannya berbaloi untuk pengguna berulang.

Sumber