PostgreSQL mungkin menggunakan indeks untuk id < 11 dan imbasan berjujukan untuk id > 11 kerana syarat pertama sepadan dengan sangat sedikit baris, manakala syarat kedua sepadan dengan hampir seluruh jadual. Kedua-dua operator menyokong indeks B-tree; perancang membandingkan anggaran kosnya.

Panduan ini membantu pembangun yang bekerja pada Mac menghasilkan semula pilihan itu dengan baris artikel sintetik, membaca pelan dan memeriksa sama ada perbezaan tersebut perlu diberi perhatian. Panduan ini mengikuti dokumentasi PostgreSQL 18 semasa dan dokumentasi node-postgres semasa yang diperoleh. Periksa versi pelayan anda sebelum membandingkan butiran pelan; contoh ini tidak menjanjikan pelan yang serupa merentas versi atau mesin. PostgreSQL: Menggunakan EXPLAIN

Mengapa indeks yang sama boleh menghasilkan pilihan imbasan yang berbeza?

Indeks B-tree menyokong perbandingan kurang daripada dan lebih daripada. Menukar arah perbandingan tidak menjadikan indeks tidak boleh digunakan. Yang berubah ialah julat nilai—dan oleh itu bilangan baris—yang dijangka diperoleh oleh perancang. PostgreSQL: Jenis Indeks

Pertimbangkan jadual ilustrasi yang mengandungi tepat 100,000 baris artikel, dengan ID integer berturutan dari 1 hingga 100,000:

Predikat Baris sepadan Bahagian jadual
id < 11 10 0.01%
id > 11 99,989 99.989%
id = 11 1 0.001%

Kiraan ini berdasarkan input sintetik, bukannya ukuran daripada pangkalan data sedia ada. Kedua-dua julat tidak merangkumi ID 11. Indeks boleh mencari sepuluh ID yang sepadan dalam julat pertama dengan cekap. Untuk julat kedua, indeks akan mengenal pasti hampir setiap baris juga.

Imbasan indeks biasa PostgreSQL mendapatkan entri daripada indeks dan baris yang sepadan daripada storan utama jadual, yang dipanggil heap. Baris heap tersebut boleh tersebar pada banyak halaman. Apabila hampir setiap halaman diperlukan, membaca jadual secara langsung boleh menelan kos yang lebih rendah daripada menelusuri indeks dan mengambil baris yang dirujuknya. PostgreSQL: Imbasan Indeks Sahaja dan Indeks Liputan

Oleh itu, angka 11 tidak mempunyai makna khas bagi perancang. Dengan data yang berbeza, syarat lebih daripada mungkin merupakan julat yang sempit. PostgreSQL menganggarkan pecahan baris yang sepadan, yang dikenali sebagai selektiviti, menggunakan statistik jadual. Besarnya nilai sempadan sahaja tidak menentukan pecahan itu. PostgreSQL: Statistik yang Digunakan oleh Perancang

Bagaimanakah anda menghasilkan semula perbandingan pada Mac?

Anda memerlukan sambungan PostgreSQL sedia ada dan arahan psql yang tersedia dalam Terminal Mac anda. Pemasangan tidak termasuk dalam latihan ini. Buka sambungan pangkalan data biasa anda dengan psql; ia menerima tetapan sambungan dan menyediakan gesaan SQL interaktif. Jalankan blok berikut dalam satu sesi baharu. PostgreSQL: psql

Persediaan ini mencipta jadual sementara yang hanya mengandungi teks artikel sintetik. Jadual sementara hilang apabila sesi berakhir. Kunci primer integer biasa mencipta indeks B-tree unik secara automatik, 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;

Integer PostgreSQL generate_series merangkumi kedua-dua hujung dengan langkah lalai satu. Oleh itu, pertanyaan kiraan sepatutnya mengembalikan jumlah 100,000 baris, 10 di bawah 11 dan 99,989 di atas 11. PostgreSQL: Fungsi yang Mengembalikan Set

Jalankan ANALYZE selepas memuatkan baris supaya perancang mempunyai statistik tentang jadual yang telah diisi. Arahan ini mengumpulkan statistik; fungsinya berbeza daripada pilihan pengukuran pelaksanaan dalam EXPLAIN ANALYZE. PostgreSQL: ANALYZE

Seterusnya, bandingkan dua pertanyaan baris penuh:

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;

Arahan ini melaksanakan setiap pertanyaan dan melaporkan pelan serta kiraan baris sebenar tanpa mengembalikan semua baris artikel yang dipilih. BUFFERS menambahkan aktiviti blok; TIMING OFF menyahdayakan pemasaan nod individu. Ini berguna untuk mengkaji pelaksanaan, tetapi tidak mengukur masa yang diperlukan untuk menghantar semua data artikel yang sepadan kepada aplikasi desktop. PostgreSQL: EXPLAIN

Dengan tetapan biasa, jangkakan pelan berasaskan indeks untuk julat sempit dan imbasan berjujukan untuk julat luas. Anggap itu sebagai jangkaan: PostgreSQL boleh memilih pelan bitmap atau pelan lain yang sesuai berdasarkan anggarannya. SQL contoh ini telah diperiksa, tetapi tiada pelayan PostgreSQL tersedia untuk melaksanakannya semasa penyediaan; aritmetik kiraan baris telah disemak secara berasingan.

Bahagian pelan yang manakah menerangkan pilihan itu?

Mulakan dengan nod imbasan, anggaran rows dan rows sebenar. Untuk pertanyaan pertama, cari Index Scan dengan Index Cond, atau Bitmap Index Scan di bawah Bitmap Heap Scan. Untuk pertanyaan kedua, cari Seq Scan dengan Filter. Pelan bitmap masih menggunakan indeks; pelan itu menyusun lokasi baris yang sepadan sebelum mengambil halaman heap. PostgreSQL: Menggunakan EXPLAIN

Kemudian semak pemerhatian berikut:

Pemerhatian dalam contoh ini Perkara yang perlu disiasat
Anggaran hampir sepadan dengan 10 dan 99,989 baris sebenar Perbezaan bilangan padanan menerangkan pilihan yang berbeza.
Anggaran baris berbeza jauh daripada baris sebenar Segarkan statistik sebelum mengubah tetapan perancang.
Kedua-dua predikat menggunakan imbasan berjujukan pada sampel yang jauh lebih kecil Saiz jadual mungkin menjadikan imbasan langsung lebih murah.
Pelan bitmap muncul Bandingkan anggaran baris dan kerja heap sebagai alternatif berasaskan indeks.

Kos perancang ialah anggaran dalam unit sewenang-wenangnya, bukannya milisaat. Bandingkannya sebagai sebahagian daripada model PostgreSQL dan bukannya mentafsir kos 100 sebagai tempoh. Oleh sebab contoh ini menggunakan jadual sementara, aktiviti penimbal muncul di bawah blok tempatan; penimbal jadual dan indeks biasa muncul di bawah blok kongsi. PostgreSQL: EXPLAIN

Jika anggaran kelihatan menghairankan, teruskan dalam sesi yang sama dengan pertanyaan 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 menerangkan taburan nilai yang digunakan untuk menganggarkan julat. Korelasi menerangkan sejauh mana susunan logik lajur mengikut susunan fizikal baris; nilai yang hampir pada mana-mana ekstrem boleh menjadikan akses indeks kepada heap kelihatan lebih murah. Tiada satu pun medan ini menetapkan peratusan sejagat yang mewajibkan PostgreSQL menukar imbasan. PostgreSQL: pg_stats

Bagaimanakah anda membandingkan pertanyaan melalui node-postgres?

node-postgres menghantar teks pertanyaan dan nilai parameter kepada PostgreSQL, tempat perancangan berlaku. API pertanyaannya menerima SQL diikuti tatasusunan nilai, jadi kedua-dua perbandingan boleh menggunakan nilai sempadan yang sama, iaitu 11. node-postgres: Pertanyaan

Untuk menghasilkan semula ujian secara berasingan dengan Node.js, gunakan pemasangan Node.js/npm yang berfungsi dan tetapan sambungan PostgreSQL sedia ada anda. Dalam direktori projek baharu, jalankan npm init -y, kemudian npm install pg. Simpan program lengkap di bawah sebagai compare-scans.mjs dan jalankan node compare-scans.mjs. Klien menggunakan tetapan persekitaran PostgreSQL seperti PGHOST, PGPORT dan PGDATABASE; gunakan tetapan untuk pangkalan data setempat sedia ada anda. node-postgres: Menyambung

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 mencipta jadual sampelnya sendiri dan memastikan setiap operasi menggunakan satu sambungan. Program ini mencetak kedua-dua pelan dan menutup sambungan selepas itu. Sintaks JavaScript telah disemak dengan Node.js; pelaksanaan pangkalan data tidak dilakukan semasa penyediaan. Jika persediaan sambungan gagal, selesaikan masalah itu sebelum mentafsir tingkah laku imbasan.

Bolehkah pernyataan tersedia bernama mengubah perbandingan?

Menambahkan name pada konfigurasi pertanyaan node-postgres akan mencipta pernyataan tersedia bernama pada setiap sambungan. Hal ini menambahkan satu lagi perbezaan: PostgreSQL boleh menggunakan pelan tersuai berdasarkan nilai parameter atau pelan generik yang dikongsi merentas pelaksanaan. node-postgres: Pertanyaan, PostgreSQL: PREPARE

Di bawah dasar automatik lalai PostgreSQL 18, lima pelaksanaan pertama menggunakan pelan tersuai. PostgreSQL kemudian membandingkan anggaran kos pelan generik dengan kos purata pelan tersuai; ia tidak bertukar secara automatik kepada pelan generik pada pelaksanaan keenam. PostgreSQL: PREPARE

Jika pernyataan bernama aplikasi berkelakuan berbeza daripada ujian SQL literal, periksa pernyataan itu pada sambungan yang sama menggunakan EXPLAIN EXECUTE. Pelan generik memaparkan simbol parameter, manakala pelan tersuai menggantikan simbol itu dengan nilai yang dibekalkan. Kekalkan kedua-dua operator perbandingan dalam pernyataan bernama yang berasingan kerana teks SQLnya berbeza. PostgreSQL: PREPARE

Bilakah anda patut mengubah pertanyaan atau tetapan perancang?

Pertama, kekalkan tugas sebenar pertanyaan. Menggantikan SELECT * dengan SELECT id mengubah data yang perlu diperoleh PostgreSQL. Apabila semua lajur yang diperlukan tersedia daripada indeks yang sesuai, imbasan indeks sahaja boleh dilakukan. Mengelakkan pengambilan heap juga bergantung pada peta keterlihatan, jadi pelan imbasan indeks sahaja tidak menjamin ketiadaan akses heap. PostgreSQL: Imbasan Indeks Sahaja dan Indeks Liputan

Jika tugas memerlukan setiap artikel yang sepadan, kekalkan keperluan itu semasa membandingkan pelan. Jika tugas hanya memerlukan subset kecil yang tersusun, uji pertanyaan ORDER BY dan LIMIT sebenar: pengisihan dan pemberhentian awal boleh mengubah pelan yang diutamakan. PostgreSQL: Menggunakan EXPLAIN

Selepas menyemak statistik, anda boleh membandingkan pelan alternatif dalam sesi SQL sedia ada:

BEGIN;
SET LOCAL enable_seqscan = off;

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

ROLLBACK;

Tetapan ini tidak menggalakkan imbasan berjujukan; ia tidak dapat menyekatnya sepenuhnya. Gunakan hasilnya sebagai perbandingan diagnostik. PostgreSQL turut memodelkan akses halaman berjujukan dan bukan berjujukan melalui seq_page_cost dan random_page_cost. Mengubah nilai relatifnya boleh mengubah pilihan pelan, tetapi dokumentasi menyarankan supaya tetapan kos dinilai merentas beban kerja pertanyaan dan bukannya beberapa eksperimen sahaja. PostgreSQL: Perancangan Pertanyaan

Bagi jadual sintetik ini, perbandingan penentunya ialah 10 baris yang sepadan berbanding 99,989. Semak pertanyaan yang tepat, segarkan statistik, bandingkan baris anggaran dengan baris sebenar dan periksa tingkah laku pelan tersedia apabila berkaitan. Langkah-langkah itu menentukan sama ada imbasan berjujukan sesuai dengan kerja yang diminta dan memberikan bukti untuk sebarang perubahan lanjut.

Sumber