Bandingkan pelan PostgreSQL dengan memadankan masa pelaksanaan berulang dengan kiraan penimbal nod akar. Panduan Terminal Mac ini menggunakan baris artikel sintetik untuk menguji sama ada indeks mengurangkan kerja pangkalan data, sambil mengambil kira kesan cache yang boleh menyebabkan satu larian mengelirukan.

Apakah yang patut anda ukur bersama?

Tugas anda ialah membandingkan pertanyaan senarai artikel yang sama sebelum dan selepas menambah indeks. Kekalkan lajur terpilih, kategori, pengisihan, had dan data yang sama. Catat masa pelaksanaan berlalu serta aktiviti penimbal yang diperlukan untuk menghasilkan keputusan.

Gunakan EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON). Pilihan ANALYZE melaksanakan pertanyaan; TIMING OFF menyahdayakan pengukuran jam nod individu sambil mengekalkan masa pelaksanaan keseluruhan pernyataan. Output yang boleh dibaca mesin memudahkan pengumpulan ukuran berulang. PostgreSQL: EXPLAIN

Baca ukuran ini bersama-sama:

Ukuran Perkara yang ditunjukkan
Execution Time Tempoh pelaksanaan pelayan, tidak termasuk perancangan
Planning Time Masa yang digunakan untuk menjana dan mengoptimumkan pelan
Shared Hit Blocks Akses penimbal yang dipenuhi daripada cache kongsi PostgreSQL
Shared Read Blocks Blok yang dibawa masuk ke dalam penimbal kongsi
Hit kongsi ditambah bacaan Perbandingan berguna bagi jumlah akses penimbal kongsi
Temp Read Blocks dan Temp Written Blocks Aktiviti fail kerja sementara, seperti isihan yang melimpah ke storan apabila melebihi memori

Blok kongsi tergolong dalam jadual dan indeks biasa. Blok setempat tergolong dalam jadual dan indeks sementara; blok fail kerja sementara ialah kategori berasingan. Nod induk merangkumi aktiviti penimbal anak-anaknya, jadi gunakan nod akar untuk jumlah pelaksanaan dan periksa nod anak untuk mencari lokasi kerja dilakukan. Jangan sekali-kali menjumlahkan kiraan semua nod. PostgreSQL: EXPLAIN

Kiraan penimbal mewakili akses yang tidak unik: akses semula kepada blok boleh dikira sekali lagi. Selain itu, kos perancang ialah unit arbitrari, bukannya milisaat. Bandingkan masa pelaksanaan sebenar dengan kiraan penimbal sebenar; gunakan kos anggaran untuk memahami pilihan perancang. PostgreSQL: Menggunakan EXPLAIN

Bacaan kongsi tidak membuktikan bahawa peranti storan Mac anda melakukan bacaan fizikal. Statistik I/O PostgreSQL tidak dapat membezakan data yang diambil daripada storan dengan data yang sudah tersedia dalam cache halaman sistem pengendalian. Huraikan ukuran itu sebagai bacaan penimbal kongsi melainkan anda mempunyai bukti storan yang berasingan. PostgreSQL: Sistem Statistik Kumulatif

Sediakan eksperimen Mac dan semak sempadan versi

Mulakan dengan pangkalan data PostgreSQL tempatan yang sedang berjalan dan Node.js dengan npm tersedia dalam Terminal. Panduan ini menyasarkan PostgreSQL 18 dan menggunakan Node.js 22 atau 24. Dokumentasi node-postgres menyenaraikan versi Node.js tersebut sebagai serasi dan menerangkan pemasangan dengan npm install pg. node-postgres

Perintah perbandingan ini turut terdapat dalam dokumentasi PostgreSQL 16 dan 17. PostgreSQL 18 menyertakan output penimbal secara automatik dengan EXPLAIN ANALYZE; menyatakan BUFFERS secara jelas mengekalkan niat yang nyata merentas versi ini. Jangan anggap tetapan pilihan yang lebih baharu tersedia pada pelayan lama. PostgreSQL 16: EXPLAIN, PostgreSQL 17: EXPLAIN, Nota keluaran PostgreSQL 18

Dalam direktori kerja baharu, pasang pemacu:

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

Gunakan pangkalan data latihan tempatan sedia ada yang boleh dibuka oleh sambungan PostgreSQL biasa anda. Skrip ini menggunakan new Client(), yang membaca pemboleh ubah persekitaran sambungan yang sama seperti psql. Contohnya, jika nama pangkalan data itu ialah plan_lab, jalankan skrip dengan PGDATABASE=plan_lab. Tambahkan tetapan PGHOST atau PGPORT sedia ada anda jika perlu. node-postgres: Menyambung

Program ini mencipta 100,000 artikel sintetik dalam satu transaksi, membandingkan dua keadaan skema dan membuat rollback transaksi. Program ini menggunakan jadual biasa supaya imbasan muncul di bawah penimbal kongsi. PostgreSQL mendokumenkan bahawa ROLLBACK membuang kemas kini transaksi. Jika nama jadual demonstrasi itu sudah wujud, penciptaan akan gagal dan jadual tersebut tidak akan digantikan. PostgreSQL: ROLLBACK

Jalankan perbandingan lengkap sebelum dan selepas

Simpan program lengkap berikut sebagai compare-plans.mjs. Program ini menjana input sendiri, melakukan satu larian pemanasan yang tidak direkodkan bagi setiap fasa, menyimpan tujuh larian yang diukur dan mencetak pelan terukur pertama bagi setiap fasa. Penjana baris menggunakan fungsi generate_series yang didokumenkan PostgreSQL. PostgreSQL: Fungsi Pemulangan Set

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

Jalankannya dari direktori yang sama dengan menggantikan nama pangkalan data latihan anda yang sebenar:

PGDATABASE=plan_lab node compare-plans.mjs

Program ini menghantar SQL dan nilai parameter secara berasingan melalui client.query(text, values). Program membaca pelan daripada lajur yang dipulangkan bernama QUERY PLAN; node-postgres memetakan lajur hasil kepada sifat objek dan menghuraikan JSON PostgreSQL menjadi objek JavaScript. node-postgres: Pertanyaan, node-postgres: pg.Result, node-postgres: Jenis Data

Perintah kendiri ANALYZE mengumpulkan statistik jadual untuk perancang. Ia berbeza daripada pilihan ANALYZE yang mengukur pelaksanaan di dalam EXPLAIN. Dalam contoh ini, pengumpulan statistik selepas memuatkan baris sintetik memberikan kedua-dua fasa maklumat permulaan yang sama tentang jadual. PostgreSQL: ANALYZE

Pengiraan median dan jumlah penimbal JavaScript telah disemak dengan input sintetik yang deterministik. Eksperimen pangkalan data lengkap tidak dijalankan semasa penyediaan kerana PostgreSQL tidak tersedia dalam persekitaran penyelidikan; artikel ini tidak memberikan hasil penanda aras yang diukur.

Apakah yang patut berubah dalam kedua-dua pelan?

Indeks calon meletakkan kategori yang ditapis dengan kesamaan terlebih dahulu, diikuti lajur pengisihan yang diminta. PostgreSQL mendokumenkan bahawa syarat kesamaan pada lajur B-tree pendahulu mengehadkan julat indeks yang diimbas. Hal ini menjadikan indeks tersebut calon yang munasabah untuk pertanyaan ini. PostgreSQL: Indeks Berbilang Lajur

Jangkakan imbasan dan isihan sebelum pengindeksan, kemudian imbasan indeks tersusun di bawah had selepasnya. Ini ialah jangkaan untuk disemak dalam output anda, bukannya bentuk pelan yang dijamin. Indeks B-tree yang sepadan boleh membekalkan baris tersusun secara langsung, dan pengisihan yang digabungkan dengan had boleh mengelakkan pemprosesan padanan yang selebihnya. PostgreSQL: Indeks dan ORDER BY

Periksa tiga perkara dalam pelan yang dicetak:

  1. Pastikan akar mengembalikan 50 baris dalam kedua-dua fasa dan program melaporkan tiada ketidakpadanan hasil.
  2. Cari nod yang bertanggungjawab untuk imbasan dan pengisihan. Semak anggaran barisnya berbanding baris sebenar dan perhatikan lokasi aktiviti penimbal berlaku.
  3. Bandingkan akses kongsi pada akar dan masa pelaksanaan berulang. Perubahan nama nod semata-mata tidak membuktikan peningkatan yang berguna.

Ini ialah perbandingan berfasa dengan cache yang telah dipanaskan. Penyisipan baris, pertanyaan ketepatan, larian pemanasan dan penciptaan indeks semuanya mempengaruhi keadaan cache. Kekalkan beban kerja Mac dan konfigurasi pangkalan data yang sama sepanjang kedua-dua fasa. Simpan tujuh pemerhatian individu supaya larian yang luar biasa perlahan tetap kelihatan; median sahaja boleh menyembunyikan variasi.

Untuk susulan yang lebih kukuh, ulangi keseluruhan eksperimen dan uji kategori serta had lain. Jika anda menguji had yang lebih besar, labelkannya sebagai beban kerja berasingan. Perbandingan asal menjawab sama ada indeks ini membantu permintaan khusus bagi 50 artikel dalam keadaan tempatan yang diperhatikan.

Bagaimanakah anda membezakan kerja yang berkurang daripada penimbal yang lebih panas?

Nombor berikut ialah input ilustrasi, bukannya output yang direkodkan. Nombor ini menunjukkan sebab bacaan, hit dan masa patut dipertimbangkan bersama:

Larian ilustrasi Pelaksanaan Hit kongsi Bacaan kongsi Hit + bacaan
A, pemerhatian awal 12 ms 900 100 1,000
A, pemerhatian berulang 10 ms 1,000 0 1,000
B, pelan calon 3 ms 80 0 80

Antara dua pemerhatian A, bacaan hilang sementara jumlah akses kongsi kekal 1,000. Hal ini selaras dengan perubahan keadaan cache; ia tidak menunjukkan pengurangan jumlah akses penimbal kongsi. B menggabungkan tempoh yang lebih pendek dengan akses yang lebih sedikit. Jika corak itu berulang dengan hasil yang setara, hal ini menyokong kesimpulan yang lebih khusus bahawa B melakukan kurang kerja penimbal kongsi untuk tugas ini.

Gunakan panduan keputusan ini apabila hasil anda sendiri berbeza:

Corak yang diperhatikan Tafsiran dan tindakan seterusnya
Masa lebih singkat dan akses kongsi lebih sedikit Bukti peningkatan bagi beban kerja yang diuji; ulangi untuk menyemak konsistensi
Masa lebih singkat, bacaan lebih sedikit, jumlah akses serupa Keadaan cache mungkin menerangkan perubahan itu; bandingkan ulangan selepas pemanasan
Akses lebih sedikit tetapi masa serupa atau lebih lama Periksa pengisihan, ungkapan dan variasi masa sebelum membuat pilihan
Tiada bacaan tetapi banyak hit Pelaksanaan bercache masih melakukan akses penimbal; periksa nod dengan aktiviti tinggi
Bacaan atau penulisan sementara muncul Periksa operasi kerja yang menyebabkannya sebelum mengubah tetapan

Elakkan menjadikan peratusan hit cache sebagai metrik penentu. Dalam ilustrasi ini, A yang telah dipanaskan dan B kedua-duanya mempunyai kadar hit penimbal kongsi 100%, namun kiraan aksesnya berbeza dengan ketara. Kekalkan kiraan mentah dan saiz hasil bersebelahan dengan masa.

Bagaimanakah ukuran ini berkaitan dengan kelajuan aplikasi?

Execution Time pelayan tidak termasuk perancangan, manakala EXPLAIN ANALYZE biasa turut tidak merangkumi penghantaran hasil dan, secara lalai, penukaran output. Instrumentasi itu sendiri menambahkan overhed. Oleh itu, skrip ini membandingkan pelaksanaan pangkalan data yang diinstrumentasikan dan bukannya mengukur keseluruhan masa untuk memaparkan senarai artikel. PostgreSQL: Menggunakan EXPLAIN

Untuk menyiasat penukaran output secara berasingan pada PostgreSQL 17 atau 18, tambahkan SERIALIZE TEXT pada pilihan penjelasan yang sama. PostgreSQL 16 tidak menyenaraikan pilihan itu. Pensirian masih tidak menghantar baris hasil pertanyaan, jadi ia tidak dapat mengukur penghantaran rangkaian. Kekalkan tetapan pensirian yang sama merentas larian yang dibandingkan. PostgreSQL 17: EXPLAIN, PostgreSQL 16: EXPLAIN

Untuk ukuran aplikasi, catat masa panggilan biasa SELECT artikel sehingga panggilan pemacu yang dinantikan selesai, dengan sambungan sedia ada. Rekodkan itu sebagai ukuran masa berlalu klien yang berasingan. Kekalkan konfigurasi pertanyaan sebenar aplikasi: menambahkan name pertanyaan node-postgres membolehkan tingkah laku pernyataan yang disediakan bagi setiap sambungan dan mengubah syarat perbandingan. node-postgres: Pertanyaan

Pilih calon apabila hasil yang setara, masa berulang dan kiraan penimbal memberikan gambaran yang konsisten bagi permintaan artikel yang perlu anda layani. Simpan pelan penuh, larian individu, versi pelayan dan parameter pertanyaan supaya kesimpulannya kekal khusus dan boleh dihasilkan semula.

Sumber