Memilih isi artikel boleh meniadakan imbasan indeks sahaja: PostgreSQL perlu mendapatkan lajur yang tiada dalam indeks. Panduan ini menunjukkan cara mendiagnosis perubahan itu pada Mac dan mengukur kesannya dalam Node.js. Keperluan imbasan indeks sahaja.

Mengapa perubahan lajur yang dipilih mengubah pelan?

Pertimbangkan jadual artikel dengan id, category dan body, serta indeks B-tree pada (category, id). Pertanyaan yang memilih ID mengikut kategori boleh mendapatkan nilai penapis dan hasilnya daripada indeks itu. Menambah body menjadikan indeks itu tidak mencukupi untuk imbasan indeks sahaja. Indeks PostgreSQL berasingan daripada storan utama jadual, yang dipanggil heap; imbasan indeks biasa turut mendapatkan data jadual. Imbasan Indeks Sahaja dan Indeks Liputan.

Perancang kini membandingkan cara untuk mendapatkan baris jadual yang diperlukan. Ia mungkin mengekalkan Index Scan, menggabungkan Bitmap Index Scan dengan Bitmap Heap Scan, atau memilih Seq Scan. Imbasan bitmap mengumpulkan akses heap mengikut lokasi fizikal. Apabila mendapatkan banyak baris yang tersebar, mengimbas jadual boleh jadi lebih murah berbanding mendapatkannya satu demi satu. Pelan bitmap masih menggunakan indeks walaupun nod imbasan teratasnya menyebut heap. Menggunakan EXPLAIN.

Periksa juga pelan asal dengan teliti. Index Only Scan boleh melaporkan Heap Fetches bukan sifar: PostgreSQL kadangkala perlu melawati baris jadual untuk menentukan keterlihatan. Mengelakkan lawatan ini bergantung pada peta keterlihatan jadual, serta lajur yang diperlukan berada dalam indeks. Semakan keterlihatan untuk imbasan indeks sahaja.

Hasilkan semula perbandingan pada Mac anda

Gunakan pangkalan data PostgreSQL tempatan yang sedang berjalan dan keluaran Node.js yang dipasang serta masih disokong, bersama npm. Contoh asas menggunakan ciri yang tersedia merentas PostgreSQL 14–18; manual PostgreSQL semasa yang dirujuk mengenal pasti dirinya sebagai versi 18. Diagnostik penserialan tambahan di bawah memerlukan PostgreSQL 17 atau lebih baharu. Dokumentasi node-postgres menerangkan keserasiannya dengan Node.js dan arahan pemasangan. Dokumentasi PostgreSQL, Pemasangan dan keserasian node-postgres.

Buka Terminal dan bekerja dalam folder baharu. Arahan ini mencipta projek dan memasang pemacu pangkalan data:

mkdir article-plan-lab
cd article-plan-lab
npm init -y
npm install pg

Program ini menggunakan tetapan sambungan tempatan sedia ada anda melalui PGHOST, PGPORT, PGDATABASE dan PGUSER. Konfigurasikannya untuk pangkalan data yang anda boleh sambungkan. node-postgres membaca pemboleh ubah persekitaran ini apabila membina Client tanpa pilihan sambungan yang dinyatakan secara eksplisit. Menyambung dengan pemboleh ubah persekitaran.

Simpan program lengkap berikut sebagai article-plan.cjs. Program ini mencipta baris sintetik dalam jadual sementara, membina indeks, mencetak kedua-dua pelan pelaksanaan, kemudian mengukur kedua-dua pertanyaan biasa. Kedua-dua perbandingan menggunakan sambungan dan nilai kategori yang sama. Konfigurasi pertanyaan membekalkan SQL melalui text dan parameter melalui values, mengikut API node-postgres yang didokumenkan. Konfigurasi pertanyaan.

const { Client } = require('pg');
const { performance } = require('node:perf_hooks');

async function main() {
  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_plan_demo (
      id integer PRIMARY KEY,
      category integer NOT NULL,
      body text NOT NULL
    )`);
    await client.query(`INSERT INTO article_plan_demo
      SELECT g, g % 10, repeat('Synthetic article paragraph. ', 40)
      FROM generate_series(1, 50000) AS s(g)`);
    await client.query(`CREATE INDEX article_plan_demo_category_id
      ON article_plan_demo (category, id)`);
    await client.query('VACUUM (ANALYZE) article_plan_demo');

    const queries = [
      ['IDs', 'SELECT id FROM article_plan_demo WHERE category = $1'],
      ['Bodies', 'SELECT id, body FROM article_plan_demo WHERE category = $1']
    ];

    for (const [label, text] of queries) {
      const plan = await client.query({
        text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ${text}`,
        values: [3]
      });
      console.log(label);
      console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));

      const start = performance.now();
      const result = await client.query({ text, values: [3] });
      const elapsed = performance.now() - start;
      const bodyBytes = result.rows.reduce((sum, row) =>
        sum + (row.body === undefined ? 0 : Buffer.byteLength(row.body)), 0);
      console.log({
        rows: result.rows.length,
        elapsedMs: elapsed.toFixed(2),
        bodyBytes
      });
    }
  } finally {
    await client.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

Jalankannya dari folder yang sama:

node article-plan.cjs

Persediaan ini menghantar VACUUM (ANALYZE) sebagai arahan tersendiri. Arahan itu menjalankan vacuum diikuti pengumpulan statistik dan tidak boleh dijalankan dalam blok transaksi. Pastikan ia di luar sebarang pembalut transaksi yang anda tambahkan pada contoh ini. VACUUM.

Sintaks JavaScript telah diperiksa, dan pengiraan deterministik berasingan mengesahkan jumlah baris serta jumlah bait isi bagi data ujian. PostgreSQL tidak tersedia dalam persekitaran penyelidikan, jadi pertanyaan pangkalan data ini telah diperiksa tetapi tidak dilaksanakan di sini. Tiada pelan atau masa jalan tertentu dikemukakan sebagai hasil pemerhatian.

Baca pelan mengikut urutan yang berguna

Mulakan dengan nama imbasan, kemudian bandingkan anggaran dengan baris sebenar. Seterusnya, periksa akses jadual dan aktiviti penimbal. Urutan ini membezakan perubahan pada kaedah akses yang tersedia daripada anggaran yang tidak tepat atau hasil yang mahal. VERBOSE turut mendedahkan lajur output setiap nod. Menggunakan EXPLAIN, Pilihan EXPLAIN.

Pemerhatian Tafsiran Semakan seterusnya
ID menggunakan Index Only Scan; isi menggunakan Index Scan Pertanyaan isi memerlukan data jadual Bandingkan penimbal dan masa pertanyaan biasa
Isi menggunakan Bitmap Heap Scan Periksa nod anaknya untuk penggunaan indeks Bandingkan blok heap dan baris yang dipulangkan
Isi menggunakan Seq Scan Perancang lebih memilih untuk mengimbas jadual Bandingkan anggaran dengan jumlah baris yang sepadan
ID menunjukkan Heap Fetches yang banyak Pelaksanaan indeks sahaja masih melawati heap Periksa sama ada ini mencerminkan perubahan jadual terkini
Anggaran jauh berbeza daripada jumlah baris sebenar Statistik mungkin tidak menggambarkan data dengan baik Kumpulkan statistik dan bandingkan semula

ANALYZE mengumpulkan statistik yang digunakan perancang untuk memilih pelan. Jika anda mengubah taburan kategori sintetik, kumpulkan statistik sekali lagi sebelum mentafsir perbandingan seterusnya. Persampelan bermakna anggaran mungkin sedikit berbeza antara larian. ANALYZE.

Anggap width sebagai anggaran bait output bagi setiap baris, bukan ukuran bait yang dipindahkan. Begitu juga, kos perancang menggunakan unit relatif, bukannya milisaat. Kejadian penimbal menunjukkan blok yang ditemui dalam cache PostgreSQL; ini tidak bermakna tiada kerja dilakukan. Anggaran pelan, Pelaporan penimbal.

Untuk data ujian ini, kedua-dua pertanyaan sepatutnya memulangkan 5,000 baris. Setiap isi sintetik mengandungi 1,160 bait ASCII, jadi pertanyaan isi memulangkan 5,800,000 bait isi, kira-kira 5.53 MiB, sebelum mengambil kira ID dan overhed protokol. Ini pengiraan berdasarkan input sampel, bukannya ukuran prestasi pangkalan data. Medan bodyBytes dalam program membolehkan anda mengesahkan perbezaan itu.

Pertanyaan biasa dijalankan selepas pengukuran pelannya, jadi masa berlalu mencerminkan urutan pelaksanaan yang sudah dipanaskan itu. Ulangi perbandingan dan selang-selikan urutannya sebelum membuat kesimpulan daripada perbezaan masa yang kecil. Satu larian berguna untuk diagnosis, tetapi tidak membuktikan nisbah prestasi yang boleh dipercayai.

Mengapa mendapatkan isi boleh mengambil masa lebih lama daripada yang ditunjukkan EXPLAIN?

Nilai teks yang besar mungkin dimampatkan atau disimpan di luar jadual utama melalui mekanisme TOAST PostgreSQL. Oleh itu, mendapatkan entri utama baris dan menghasilkan isi lengkapnya untuk klien boleh melibatkan jumlah kerja yang berbeza. Data ujian yang pendek dan berulang di atas bertujuan menunjukkan liputan lajur; ia tidak mewakili semua corak storan isi. Storan TOAST.

Secara lalai, EXPLAIN ANALYZE membuang hasil pertanyaan tanpa menukarkannya untuk dihantar. Pada PostgreSQL 17 dan lebih baharu, tambah SERIALIZE TEXT untuk menyiasat penukaran output, termasuk mendapatkan nilai luar baris apabila perlu. PostgreSQL 17 memperkenalkan pilihan ini. Tingkah laku penserialan EXPLAIN, Nota keluaran PostgreSQL 17.

Untuk mencubanya, gantikan baris templat EXPLAIN sedia ada dalam program dengan cebisan ini, kemudian jalankan semula keseluruhan program:

text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SERIALIZE TEXT) ${text}`,

Pada PostgreSQL 14–16, kekalkan baris asal dan gunakan pemasa pertanyaan biasa untuk mengukur penerimaan hasil. SERIALIZE masih tidak menghantar data hasil kepada klien, jadi ia tidak boleh mengukur penghantaran atau pemprosesan JavaScript. Had EXPLAIN.

node-postgres biasanya mengumpulkan baris hasil ke dalam objek JavaScript. Pilihan rowMode: 'array' mengubah perwakilan hasil; ia tidak mengeluarkan nilai isi yang dipilih daripada hasil SQL. Oleh itu, pilihan ini tidak dapat memulihkan liputan lajur yang hilang apabila body ditambahkan pada pertanyaan ini. Mod baris.

Pilih perubahan yang sepadan dengan tugasan artikel

Jika tugasan desktop hanya memerlukan senarai ID, kekalkan unjuran yang ringkas. Jika ia memerlukan teks artikel penuh, nilai masa berlalu sebenar dan saiz hasil pertanyaan isi. Matlamat diagnostik ialah mengenal pasti kerja yang tidak perlu sambil mengekalkan hasil yang diminta.

Untuk skrin yang hanya memaparkan 50 artikel pertama dalam sesuatu kategori, mengehadkan hasil yang diminta ialah pilihan yang bermakna. Dalam tatasusunan queries program, gantikan rentetan pertanyaan isi dengan SQL ini:

SELECT id, body
FROM article_plan_demo
WHERE category = $1
ORDER BY id
LIMIT 50

Ini sengaja mengubah tugasan daripada mendapatkan setiap artikel yang sepadan kepada mendapatkan satu halaman tersusun. Dengan kategori ditetapkan, indeks (category, id) sedia ada menyediakan laluan calon mengikut susunan ID. Indeks B-tree yang sepadan boleh menjadikan ORDER BY dengan LIMIT cekap kerana pelaksanaan boleh berhenti selepas mendapat baris yang mencukupi. Pelan yang dipilih masih perlu diperiksa. Indeks dan ORDER BY.

Jangan anggap INCLUDE (body) sebagai pembaikan automatik. Lajur yang disertakan menduplikasi data jadual, membesarkan indeks dan boleh menyebabkan sisipan gagal jika tuple indeks melebihi had saiznya. Lajur muatan kecil boleh menjadi calon yang berguna untuk eksperimen terukur. INCLUDE diperkenalkan dalam PostgreSQL 11; ia tidak tersedia pada versi terdahulu. Perhatian tentang indeks liputan, Nota keluaran PostgreSQL 11.

Akhiri penyiasatan dengan tiga penemuan konkrit: sama ada pemilihan isi menyingkirkan kelayakan indeks sahaja, sama ada imbasan baharu berprestasi dengan baik, dan berapa lama hasil penuh mengambil masa untuk sampai ke Node.js. Penemuan itu memberitahu anda sama ada perlu mengekalkan pelan, meminta lebih sedikit artikel atau menguji indeks berbeza untuk pertanyaan sebenar.

Sumber