Bandingkan rencana PostgreSQL dengan memasangkan waktu eksekusi berulang dengan jumlah buffer pada simpul akar. Panduan Terminal Mac ini menggunakan baris artikel sintetis untuk menguji apakah indeks mengurangi pekerjaan basis data, dengan memperhitungkan efek cache yang dapat membuat satu kali eksekusi menyesatkan.

Apa yang sebaiknya diukur bersama?

Tugas Anda adalah membandingkan kueri daftar artikel yang sama sebelum dan sesudah menambahkan indeks. Pertahankan kolom yang dipilih, kategori, pengurutan, batas, dan data agar tetap sama. Catat waktu eksekusi yang berlalu serta aktivitas buffer yang diperlukan untuk menghasilkan hasilnya.

Gunakan EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON). Opsi ANALYZE menjalankan kueri; TIMING OFF menonaktifkan pengukuran waktu untuk tiap simpul, tetapi tetap mencatat waktu eksekusi seluruh pernyataan. Keluaran yang dapat dibaca mesin memudahkan pengumpulan pengukuran berulang. PostgreSQL: EXPLAIN

Baca pengukuran ini secara bersamaan:

Pengukuran Artinya
Execution Time Durasi eksekusi di server, tidak termasuk perencanaan
Planning Time Waktu yang digunakan untuk membuat dan mengoptimalkan rencana
Shared Hit Blocks Akses buffer yang dipenuhi dari cache bersama PostgreSQL
Shared Read Blocks Blok yang dimasukkan ke buffer bersama
Shared hits ditambah reads Perbandingan yang berguna untuk volume akses buffer bersama
Temp Read Blocks dan Temp Written Blocks Aktivitas berkas kerja sementara, seperti pengurutan yang melampaui kapasitas memori

Blok bersama terkait dengan tabel dan indeks biasa. Blok lokal terkait dengan tabel dan indeks sementara; blok berkas kerja sementara merupakan kategori terpisah. Simpul induk mencakup aktivitas buffer anak-anaknya, jadi gunakan simpul akar untuk total eksekusi dan periksa simpul anak untuk menemukan lokasi pekerjaan. Jangan pernah menjumlahkan hitungan dari semua simpul. PostgreSQL: EXPLAIN

Jumlah buffer menunjukkan akses yang tidak unik: mengakses kembali suatu blok dapat dihitung lagi. Selain itu, biaya perencana adalah satuan arbitrer, bukan milidetik. Bandingkan waktu eksekusi aktual dengan jumlah buffer aktual; gunakan perkiraan biaya untuk memahami pilihan perencana. PostgreSQL: Using EXPLAIN

Pembacaan bersama tidak membuktikan bahwa perangkat penyimpanan Mac Anda melakukan pembacaan fisik. Statistik I/O PostgreSQL tidak dapat membedakan data yang diambil dari penyimpanan dengan data yang sudah tersedia di cache halaman sistem operasi. Sebut pengukuran tersebut sebagai pembacaan buffer bersama kecuali Anda memiliki bukti penyimpanan terpisah. PostgreSQL: The Cumulative Statistics System

Siapkan eksperimen Mac dan periksa batasan versi

Mulailah dengan basis data PostgreSQL lokal yang sedang berjalan dan Node.js dengan npm tersedia di Terminal. Panduan ini menargetkan PostgreSQL 18 dan menggunakan Node.js 22 atau 24. Dokumentasi node-postgres mencantumkan versi Node.js tersebut sebagai versi yang kompatibel dan menjelaskan pemasangan dengan npm install pg. node-postgres

Perintah perbandingan ini juga tersedia dalam dokumentasi PostgreSQL 16 dan 17. PostgreSQL 18 otomatis menyertakan keluaran buffer dengan EXPLAIN ANALYZE; menuliskan BUFFERS secara eksplisit memperjelas maksudnya di semua versi tersebut. Jangan berasumsi bahwa pengaturan opsional yang lebih baru tersedia di server lama. PostgreSQL 16: EXPLAIN, PostgreSQL 17: EXPLAIN, catatan rilis PostgreSQL 18

Di direktori kerja baru, pasang driver:

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

Gunakan basis data latihan lokal yang sudah ada dan dapat dibuka oleh koneksi PostgreSQL biasa Anda. Skrip menggunakan new Client(), yang membaca variabel lingkungan koneksi yang sama dengan psql. Misalnya, jika nama basis data tersebut plan_lab, jalankan skrip dengan PGDATABASE=plan_lab. Tambahkan pengaturan PGHOST atau PGPORT yang sudah Anda miliki jika diperlukan. node-postgres: Connecting

Program ini membuat 100.000 artikel sintetis di dalam transaksi, membandingkan dua kondisi skema, lalu membatalkan transaksi. Program menggunakan tabel biasa agar pemindaian tercatat di buffer bersama. PostgreSQL menjelaskan bahwa ROLLBACK membuang pembaruan transaksi. Jika nama tabel demonstrasi tersebut sudah ada, pembuatan tabel akan gagal alih-alih menggantinya. PostgreSQL: ROLLBACK

Jalankan perbandingan lengkap sebelum dan sesudah

Simpan program lengkap berikut sebagai compare-plans.mjs. Program menghasilkan inputnya sendiri, menjalankan satu pemanasan yang tidak dicatat untuk setiap tahap, menyimpan tujuh proses yang diukur, dan mencetak rencana pengukuran pertama untuk tiap tahap. Pembuat baris menggunakan fungsi generate_series yang terdokumentasi di PostgreSQL. PostgreSQL: Set Returning Functions

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

Jalankan dari direktori yang sama, dengan mengganti nama basis data latihan Anda yang sebenarnya:

PGDATABASE=plan_lab node compare-plans.mjs

Program mengirim SQL dan nilai parameter secara terpisah melalui client.query(text, values). Program membaca rencana dari kolom hasil bernama QUERY PLAN; node-postgres memetakan kolom hasil ke properti objek dan mengurai JSON PostgreSQL menjadi objek JavaScript. node-postgres: Queries, node-postgres: pg.Result, node-postgres: Data Types

Perintah mandiri ANALYZE mengumpulkan statistik tabel untuk perencana. Perintah ini berbeda dari opsi ANALYZE untuk mengukur eksekusi di dalam EXPLAIN. Dalam contoh ini, mengumpulkan statistik setelah memuat baris sintetis memberi kedua tahap informasi awal yang sama tentang tabel. PostgreSQL: ANALYZE

Penghitungan median JavaScript dan total buffer telah diperiksa menggunakan input sintetis yang deterministik. Eksperimen basis data lengkap tidak dijalankan saat persiapan karena PostgreSQL tidak tersedia di lingkungan riset; artikel ini tidak menyertakan hasil benchmark terukur.

Apa yang seharusnya berubah pada kedua rencana?

Indeks yang diuji menempatkan kategori yang difilter dengan kesamaan di awal, lalu kolom pengurutan yang diminta. PostgreSQL menjelaskan bahwa kondisi kesamaan pada kolom awal B-tree membatasi rentang indeks yang dipindai. Karena itu, indeks ini merupakan kandidat yang masuk akal untuk kueri tersebut. PostgreSQL: Multicolumn Indexes

Sebelum pengindeksan, cari pemindaian dan pengurutan; setelahnya, cari pemindaian indeks terurut di bawah batas. Ini adalah perkiraan yang perlu diperiksa pada keluaran Anda, bukan bentuk rencana yang dijamin. Indeks B-tree yang cocok dapat langsung menyediakan baris terurut, dan pengurutan yang digabungkan dengan batas dapat menghindari pemrosesan baris cocok lainnya. PostgreSQL: Indexes and ORDER BY

Periksa tiga hal dalam rencana yang dicetak:

  1. Pastikan simpul akar mengembalikan 50 baris pada kedua tahap dan program tidak melaporkan ketidakcocokan hasil.
  2. Temukan simpul yang bertanggung jawab atas pemindaian dan pengurutan. Periksa perkiraan jumlah baris dibandingkan jumlah aktual, lalu amati lokasi aktivitas buffer.
  3. Bandingkan akses bersama pada simpul akar dan waktu eksekusi berulang. Perubahan nama simpul saja tidak membuktikan adanya peningkatan yang berguna.

Ini adalah perbandingan bertahap dengan cache hangat. Penyisipan baris, kueri pemeriksaan kebenaran, pemanasan, dan pembuatan indeks semuanya memengaruhi kondisi cache. Pertahankan beban kerja Mac dan konfigurasi basis data yang sama pada kedua tahap. Simpan ketujuh pengamatan individual agar proses yang luar biasa lambat tetap terlihat; median saja dapat menyamarkan variasi.

Untuk tindak lanjut yang lebih kuat, ulangi seluruh eksperimen dan uji kategori serta batas lainnya. Jika Anda menguji batas yang lebih besar, tandai sebagai beban kerja terpisah. Perbandingan awal menjawab apakah indeks ini membantu permintaan 50 artikel tertentu dalam kondisi lokal yang diamati.

Bagaimana membedakan pekerjaan yang berkurang dari buffer yang lebih hangat?

Angka berikut adalah input ilustratif, bukan keluaran yang direkam. Angka ini menunjukkan alasan pembacaan, hit, dan waktu perlu dilihat bersamaan:

Proses ilustratif Eksekusi Hit bersama Pembacaan bersama Hit + pembacaan
A, pengamatan awal 12 ms 900 100 1.000
A, pengamatan berulang 10 ms 1.000 0 1.000
B, rencana kandidat 3 ms 80 0 80

Di antara dua pengamatan A, pembacaan menghilang sementara total akses bersama tetap 1.000. Hal ini konsisten dengan perubahan kondisi cache; ini tidak menunjukkan berkurangnya volume akses buffer bersama. B menggabungkan durasi yang lebih singkat dengan lebih sedikit akses. Jika pola tersebut berulang dengan hasil yang setara, hal itu mendukung kesimpulan yang lebih terbatas bahwa B melakukan lebih sedikit pekerjaan buffer bersama untuk tugas ini.

Gunakan panduan keputusan ini jika hasil Anda berbeda:

Pola yang diamati Interpretasi dan tindakan selanjutnya
Waktu lebih singkat dan akses bersama lebih sedikit Bukti peningkatan untuk beban kerja yang diuji; ulangi untuk memeriksa konsistensi
Waktu lebih singkat, pembacaan lebih sedikit, total akses serupa Kondisi cache mungkin menjelaskan perubahan; bandingkan pengulangan dengan cache hangat
Akses lebih sedikit tetapi waktu serupa atau lebih lama Periksa pengurutan, ekspresi, dan variasi waktu sebelum memilih
Nol pembacaan tetapi banyak hit Eksekusi dengan cache tetap melakukan akses buffer; periksa simpul dengan aktivitas tinggi
Muncul pembacaan atau penulisan sementara Periksa operasi kerja yang menyebabkannya sebelum mengubah pengaturan

Hindari menjadikan persentase hit cache sebagai metrik penentu. Dalam ilustrasi tersebut, A dan B yang sudah hangat sama-sama memiliki tingkat hit buffer bersama 100%, tetapi jumlah aksesnya berbeda jauh. Sertakan jumlah mentah dan ukuran hasil di samping waktu.

Bagaimana pengukuran ini berkaitan dengan kecepatan aplikasi?

Execution Time server tidak mencakup perencanaan, sementara EXPLAIN ANALYZE biasa juga tidak mencakup pengiriman hasil dan, secara bawaan, konversi keluaran. Instrumentasi itu sendiri menambah overhead. Karena itu, skrip membandingkan eksekusi basis data yang diinstrumentasi, bukan mengukur keseluruhan waktu untuk menampilkan daftar artikel. PostgreSQL: Using EXPLAIN

Untuk menyelidiki konversi keluaran secara terpisah di PostgreSQL 17 atau 18, tambahkan SERIALIZE TEXT ke opsi penjelasan yang sama. PostgreSQL 16 tidak mencantumkan opsi tersebut. Serialisasi tetap tidak mengirimkan baris hasil kueri, jadi opsi ini tidak dapat mengukur pengiriman melalui jaringan. Pertahankan pengaturan serialisasi yang sama pada proses yang dibandingkan. PostgreSQL 17: EXPLAIN, PostgreSQL 16: EXPLAIN

Untuk pengukuran aplikasi, hitung waktu dari kueri artikel biasa SELECT hingga pemanggilan driver yang ditunggu selesai, dengan koneksi yang sudah terbentuk. Catat sebagai pengukuran waktu berlalu di sisi klien yang terpisah. Pertahankan konfigurasi kueri aplikasi yang sebenarnya: menambahkan name pada kueri node-postgres mengaktifkan perilaku pernyataan yang disiapkan per koneksi dan mengubah kondisi perbandingan. node-postgres: Queries

Pilih kandidat saat hasil yang setara, waktu berulang, dan jumlah buffer menunjukkan pola yang konsisten untuk permintaan artikel yang perlu Anda layani. Simpan rencana lengkap, proses individual, versi server, dan parameter kueri agar kesimpulannya tetap spesifik dan dapat direproduksi.

Sumber