PostgreSQL dapat menggunakan indeks untuk id < 11 dan pemindaian sekuensial untuk id > 11 karena kondisi pertama cocok dengan sangat sedikit baris, sedangkan kondisi kedua cocok dengan hampir seluruh tabel. Kedua operator mendukung indeks B-tree; perencana membandingkan perkiraan biayanya.

Panduan ini membantu pengembang yang bekerja di Mac mereproduksi pilihan tersebut dengan baris artikel sintetis, membaca rencana, dan memeriksa apakah perbedaan itu perlu ditangani. Panduan ini mengikuti dokumentasi PostgreSQL 18 terkini yang diperoleh dan dokumentasi node-postgres terkini. Periksa versi server Anda sebelum membandingkan detail rencana; contoh-contoh ini tidak menjamin rencana yang sama di berbagai versi atau mesin. PostgreSQL: Menggunakan EXPLAIN

Mengapa indeks yang sama dapat menghasilkan pilihan pemindaian yang berbeda?

Indeks B-tree mendukung perbandingan kurang dari maupun lebih dari. Mengubah arah perbandingan tidak membuat indeks menjadi tidak dapat digunakan. Yang berubah adalah rentang nilai—dan karena itu jumlah baris—yang diperkirakan akan diambil oleh perencana. PostgreSQL: Jenis Indeks

Bayangkan sebuah tabel ilustratif yang berisi tepat 100.000 baris artikel, dengan ID bilangan bulat berurutan dari 1 sampai 100.000:

Predikat Baris yang cocok Bagian dari tabel
id < 11 10 0,01%
id > 11 99.989 99,989%
id = 11 1 0,001%

Jumlah ini berasal dari input sintetis, bukan pengukuran basis data yang sudah ada. Kedua rentang tidak menyertakan ID 11. Indeks dapat menemukan sepuluh ID yang cocok pada rentang pertama dengan efisien. Untuk rentang kedua, indeks akan mengidentifikasi hampir setiap baris juga.

Pemindaian indeks PostgreSQL biasa mengambil entri dari indeks dan baris yang bersesuaian dari penyimpanan utama tabel, yang disebut heap. Baris heap itu dapat tersebar di banyak halaman. Ketika hampir setiap halaman diperlukan, membaca tabel secara langsung bisa lebih murah daripada menelusuri indeks dan mengambil baris yang dirujuknya. PostgreSQL: Pemindaian Hanya-Indeks dan Indeks Cakupan

Karena itu, angka 11 tidak memiliki makna khusus bagi perencana. Dengan data yang berbeda, kondisi lebih dari bisa menjadi rentang yang sempit. PostgreSQL memperkirakan proporsi baris yang cocok, yang dikenal sebagai selektivitas, menggunakan statistik tabel. Besarnya nilai batas saja tidak menentukan proporsi tersebut. PostgreSQL: Statistik yang Digunakan Perencana

Bagaimana cara mereproduksi perbandingan ini di Mac?

Anda memerlukan koneksi PostgreSQL yang sudah ada dan perintah psql yang tersedia di Terminal Mac Anda. Instalasi tidak termasuk dalam latihan ini. Buka koneksi basis data seperti biasa dengan psql; perintah ini menerima pengaturan koneksi dan menyediakan prompt SQL interaktif. Jalankan blok berikut dalam satu sesi baru. PostgreSQL: psql

Penyiapan ini membuat tabel sementara yang hanya berisi teks artikel sintetis. Tabel sementara akan hilang saat sesi berakhir. Kunci utama bilangan bulat biasa secara otomatis membuat indeks B-tree unik, jadi indeks kedua pada id tidak diperlukan. PostgreSQL: CREATE TABLE

SHOW server_version;

CREATE TEMP TABLE article_scan_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

INSERT INTO article_scan_demo (id, title, body)
SELECT
    g,
    'Article ' || g,
    repeat('Sample article text. ', 20)
FROM generate_series(1, 100000) AS s(g);

ANALYZE article_scan_demo;

SELECT
    count(*) AS total_rows,
    count(*) FILTER (WHERE id < 11) AS below_11,
    count(*) FILTER (WHERE id > 11) AS above_11
FROM article_scan_demo;

generate_series bilangan bulat PostgreSQL menyertakan kedua titik akhir dengan langkah bawaan sebesar satu. Karena itu, kueri hitung seharusnya menghasilkan total 100.000 baris, 10 di bawah 11, dan 99.989 di atas 11. PostgreSQL: Fungsi yang Mengembalikan Himpunan

Jalankan ANALYZE setelah memuat baris agar perencana memiliki statistik tentang tabel yang telah terisi. Perintah ini mengumpulkan statistik; fungsinya berbeda dari opsi pengukuran eksekusi pada EXPLAIN ANALYZE. PostgreSQL: ANALYZE

Selanjutnya, bandingkan kedua kueri baris lengkap berikut:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id < 11;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

Perintah-perintah ini menjalankan setiap kueri dan melaporkan rencana serta jumlah baris aktualnya, tanpa mengembalikan semua baris artikel yang dipilih. BUFFERS menambahkan aktivitas blok; TIMING OFF menonaktifkan pengukuran waktu pada setiap node. Ini berguna untuk mempelajari eksekusi, tetapi tidak mengukur waktu yang diperlukan untuk mengirim semua data artikel yang cocok ke aplikasi desktop. PostgreSQL: EXPLAIN

Dalam pengaturan biasa, harapkan rencana berbasis indeks untuk rentang sempit dan pemindaian sekuensial untuk rentang luas. Anggap itu sebagai perkiraan: PostgreSQL dapat memilih rencana bitmap atau rencana lain yang sesuai berdasarkan perkiraannya. SQL dalam contoh ini telah diperiksa, tetapi saat persiapan tidak tersedia server PostgreSQL untuk menjalankannya; perhitungan jumlah baris diperiksa secara terpisah.

Bagian rencana mana yang menjelaskan pilihan tersebut?

Mulailah dengan node pemindaian, rows perkiraannya, dan rows aktualnya. Untuk kueri pertama, cari Index Scan dengan Index Cond, atau Bitmap Index Scan di bawah Bitmap Heap Scan. Untuk kueri kedua, cari Seq Scan dengan Filter. Rencana bitmap tetap menggunakan indeks; rencana itu mengatur lokasi baris yang cocok sebelum mengambil halaman heap. PostgreSQL: Menggunakan EXPLAIN

Lalu periksa hal-hal berikut:

Pengamatan dalam contoh ini Hal yang perlu diperiksa
Perkiraan kurang lebih sesuai dengan 10 dan 99.989 baris aktual Perbedaan jumlah baris yang cocok menjelaskan perbedaan pilihan.
Perkiraan baris sangat berbeda dari jumlah aktual Segarkan statistik sebelum mengubah pengaturan perencana.
Kedua predikat menggunakan pemindaian sekuensial pada sampel yang jauh lebih kecil Ukuran tabel mungkin membuat pemindaian langsung lebih murah.
Muncul rencana bitmap Bandingkan perkiraan baris dan pekerjaan heap sebagai alternatif berbasis indeks lainnya.

Biaya perencana adalah perkiraan dalam satuan sembarang, bukan milidetik. Bandingkan biaya tersebut sebagai bagian dari model PostgreSQL, bukan sebagai durasi dengan mengartikan biaya 100 sebagai waktu tertentu. Karena contoh ini menggunakan tabel sementara, aktivitas buffer muncul di bawah blok lokal; buffer tabel dan indeks biasa muncul di bawah blok bersama. PostgreSQL: EXPLAIN

Jika perkiraannya tampak mengejutkan, lanjutkan dalam sesi yang sama dengan kueri diagnostik ini:

SELECT
    schemaname,
    n_distinct,
    histogram_bounds,
    correlation
FROM pg_stats
WHERE schemaname = (
    SELECT nspname
    FROM pg_namespace
    WHERE oid = pg_my_temp_schema()
)
  AND tablename = 'article_scan_demo'
  AND attname = 'id';

Histogram menggambarkan distribusi nilai yang digunakan untuk memperkirakan rentang. Korelasi menggambarkan seberapa dekat urutan logis kolom mengikuti urutan fisik baris; nilai yang mendekati salah satu ekstrem dapat membuat akses indeks ke heap tampak lebih murah. Tidak satu pun dari kedua kolom ini menetapkan persentase universal saat PostgreSQL harus beralih pemindaian. PostgreSQL: pg_stats

Bagaimana cara membandingkan kueri melalui node-postgres?

node-postgres mengirim teks kueri dan nilai parameter ke PostgreSQL, tempat perencanaan berlangsung. API kuerinya menerima SQL diikuti larik nilai, sehingga kedua perbandingan dapat menggunakan nilai batas yang sama, yaitu 11. node-postgres: Kueri

Untuk reproduksi Node.js yang terpisah, gunakan instalasi Node.js/npm yang berfungsi dan pengaturan koneksi PostgreSQL yang sudah ada. Di direktori proyek baru, jalankan npm init -y, lalu npm install pg. Simpan program lengkap di bawah ini sebagai compare-scans.mjs, lalu jalankan node compare-scans.mjs. Klien menggunakan pengaturan lingkungan PostgreSQL seperti PGHOST, PGPORT, dan PGDATABASE; gunakan pengaturan untuk basis data lokal Anda yang sudah ada. node-postgres: Menghubungkan

import pg from 'pg';

const client = new pg.Client();

try {
  await client.connect();

  await client.query(`
    CREATE TEMP TABLE article_scan_demo (
      id integer PRIMARY KEY,
      title text NOT NULL,
      body text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO article_scan_demo (id, title, body)
    SELECT
      g,
      'Article ' || g,
      repeat('Sample article text. ', 20)
    FROM generate_series(1, 100000) AS s(g)
  `);

  await client.query('ANALYZE article_scan_demo');

  const queries = [
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id < $1',
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id > $1'
  ];

  for (const text of queries) {
    const result = await client.query(text, [11]);
    console.log(text);
    console.log(
      result.rows.map(row => row['QUERY PLAN']).join('\n')
    );
  }
} finally {
  await client.end();
}

Program ini membuat tabel sampelnya sendiri dan menjalankan semua operasi pada satu koneksi. Program ini mencetak kedua rencana dan menutup koneksi setelahnya. Sintaks JavaScript-nya diperiksa dengan Node.js; eksekusi basis data tidak dilakukan saat persiapan. Jika penyiapan koneksi gagal, selesaikan masalah tersebut sebelum menafsirkan perilaku pemindaian.

Bisakah pernyataan bernama yang disiapkan mengubah perbandingan?

Menambahkan name ke konfigurasi kueri node-postgres membuat pernyataan bernama yang disiapkan pada setiap koneksi. Hal ini menimbulkan perbedaan tambahan: PostgreSQL dapat menggunakan rencana khusus berdasarkan nilai parameter atau rencana generik yang digunakan bersama di beberapa eksekusi. node-postgres: Kueri, PostgreSQL: PREPARE

Menurut kebijakan otomatis bawaan PostgreSQL 18, lima eksekusi pertama menggunakan rencana khusus. Setelah itu PostgreSQL membandingkan perkiraan biaya rencana generik dengan rata-rata biaya rencana khusus; PostgreSQL tidak otomatis beralih ke rencana generik pada eksekusi keenam. PostgreSQL: PREPARE

Jika pernyataan bernama dalam aplikasi berperilaku berbeda dari pengujian SQL literal, periksa pernyataan tersebut pada koneksi yang sama menggunakan EXPLAIN EXECUTE. Rencana generik menampilkan simbol parameter, sedangkan rencana khusus menggantinya dengan nilai yang diberikan. Pisahkan kedua operator perbandingan ke dalam pernyataan bernama yang berbeda karena teks SQL-nya tidak sama. PostgreSQL: PREPARE

Kapan sebaiknya Anda mengubah kueri atau pengaturan perencana?

Pertama, pertahankan tujuan sebenarnya dari kueri. Mengganti SELECT * dengan SELECT id mengubah data yang harus diambil PostgreSQL. Jika semua kolom yang diperlukan tersedia dari indeks yang sesuai, pemindaian hanya-indeks menjadi mungkin. Menghindari pengambilan heap juga bergantung pada peta visibilitas, jadi rencana hanya-indeks tidak menjamin akses heap menjadi nol. PostgreSQL: Pemindaian Hanya-Indeks dan Indeks Cakupan

Jika tugasnya memerlukan setiap artikel yang cocok, pertahankan kebutuhan itu saat membandingkan rencana. Jika tugasnya hanya memerlukan sebagian kecil yang diurutkan, uji kueri ORDER BY dan LIMIT yang sebenarnya: pengurutan dan penghentian lebih awal dapat mengubah rencana yang lebih disukai. PostgreSQL: Menggunakan EXPLAIN

Setelah memeriksa statistik, Anda dapat membandingkan rencana alternatif dalam sesi SQL yang ada:

BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

ROLLBACK;

Pengaturan ini mengurangi kecenderungan untuk memilih pemindaian sekuensial; pengaturan ini tidak dapat sepenuhnya meniadakannya. Gunakan hasilnya sebagai perbandingan diagnostik. PostgreSQL juga memodelkan akses halaman sekuensial dan nonsekuensial melalui seq_page_cost dan random_page_cost. Mengubah nilai relatifnya dapat mengubah pilihan rencana, tetapi dokumentasi menyarankan untuk mengevaluasi pengaturan biaya pada seluruh beban kerja kueri, bukan hanya beberapa percobaan. PostgreSQL: Perencanaan Kueri

Untuk tabel sintetis ini, perbandingan yang menentukan adalah 10 baris yang cocok versus 99.989. Periksa kueri yang tepat, segarkan statistik, bandingkan jumlah baris perkiraan dengan jumlah aktual, dan periksa perilaku rencana yang disiapkan bila relevan. Langkah-langkah ini memastikan apakah pemindaian sekuensial sesuai dengan pekerjaan yang diminta dan memberi Anda bukti untuk perubahan lebih lanjut.

Sumber