LIMIT 10 membatasi hasil, tetapi menemukan sepuluh nilai berbeda dapat memerlukan ribuan baris masukan. Panduan ini menunjukkan cara mendiagnosis pekerjaan tersebut dan membandingkan dua pendekatan menggunakan topik artikel sintetis dari Terminal Mac Anda.

Mengapa sepuluh hasil dapat memerlukan ribuan baris

Misalkan sebuah tabel artikel berisi 100.000 baris yang mencakup 100 topik, dengan 1.000 artikel per topik. Tugas Anda adalah mengambil sepuluh nama topik berbeda pertama menurut urutan alfabet. Kueri perlu menghapus nama yang berulang, mempertahankan urutan topik, dan mengembalikan sepuluh hasil.

PostgreSQL mendefinisikan DISTINCT sebagai penghapus baris keluaran duplikat dan LIMIT sebagai pembatas hasil yang dikembalikan. Semantik ini tidak menetapkan batas sepuluh baris pada pemindaian yang mendasarinya. Perencana mempertimbangkan limit saat memilih rencana, sehingga pekerjaan fisik bergantung pada strategi eksekusi yang dipilih. SELECT

Pertimbangkan aliran terurut yang berisi 1.000 kemunculan topic-001, diikuti 1.000 kemunculan topic-002, dan seterusnya. Operasi penghapusan duplikat secara streaming baru menemukan topik berbeda kesepuluh setelah mengonsumsi semua sembilan kelompok sebelumnya dan baris pertama dari kelompok kesepuluh:

Baris masukan yang diperlukan = (9 × 1.000) + 1 = 9.001
Hasil berbeda yang dikembalikan = 10

Ini adalah perhitungan ilustratif untuk strategi streaming tertentu, bukan rencana PostgreSQL yang diukur. Ukuran kelompok yang tidak merata mengubah perhitungan: kelompok topik pertama yang sangat besar dapat mendominasi pekerjaan meskipun kelompok berikutnya kecil.

Indeks B-tree yang sesuai dapat menyediakan masukan terurut tanpa pengurutan eksplisit. PostgreSQL mendokumentasikan pengambilan lebih awal untuk ORDER BY terindeks dengan LIMIT, tetapi penghapusan duplikat menambahkan persyaratan lain: cukup banyak nilai berbeda harus mencapai node limit. Indeks dapat menghilangkan pekerjaan pengurutan, tetapi pekerjaan pemrosesan duplikat yang cukup besar tetap ada. Indexes and ORDER BY

Baca rencana untuk mengetahui apa yang mengonsumsi baris

Gunakan EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) pada kueri dan nilai parameter yang sebenarnya. ANALYZE menjalankan pernyataan dan melaporkan jumlah baris aktual; BUFFERS melaporkan aktivitas blok. Contoh lengkap di bawah ini menjalankan diagnostik tersebut pada data sintetis. EXPLAIN

Dua pola rencana yang berguna ditampilkan di bawah. Bentuknya skematis, bukan keluaran yang direkam atau jaminan tentang rencana yang akan dipilih oleh instalasi Anda.

Jalur streaming terurut:
Limit
  Unique
    Index Only Scan atau Index Scan

Jalur agregasi:
Limit
  Sort
    HashAggregate
      Seq Scan

Pada jalur terurut, bandingkan keluaran pemindaian dengan keluaran Unique dan Limit. Perbedaan besar menunjukkan masukan yang sarat duplikat. Pada jalur agregasi, agregat mengonsumsi masukannya untuk menentukan kelompok sebelum pengurutan dan pembatasan akhir dapat selesai. Dokumentasi rencana PostgreSQL menjelaskan bagaimana pemindaian memasok data ke node pengurutan dan agregasi. Using EXPLAIN

Baca jumlah baris aktual bersama loops: jumlah baris merupakan rata-rata per eksekusi ketika sebuah node berjalan berulang kali. Kalikan dengan jumlah loop saat memperkirakan total keluaran node tersebut. Periksa juga masukan di bawah pengurutan; sedikitnya baris yang dikeluarkan oleh pengurutan tidak membuktikan bahwa masukannya sedikit. Using EXPLAIN

Pisahkan tiga pengukuran: baris yang dikeluarkan oleh node, aktivitas blok, dan waktu yang berlalu. Jumlah buffer menjelaskan blok, bukan baris logis, dan total buffer induk mencakup anak-anaknya. Tabel sementara menggunakan buffer lokal; berkas kerja sementara yang digunakan operasi seperti pengurutan muncul sebagai blok temp. EXPLAIN

Ulangi perbandingan dari Mac Anda

Gunakan rilis LTS Node.js terkini, npm, dan basis data PostgreSQL lokal yang sudah ada. Panduan ini menggunakan PostgreSQL 18 sebagai dasar dokumentasinya. Program mencetak versi server Anda agar Anda dapat menafsirkan hasil berdasarkan dokumentasi versi tersebut. node-postgres mendokumentasikan instalasi dengan npm install pg dan dukungan untuk rilis Node.js terkini serta LTS. node-postgres

Di Terminal, buat direktori kerja dan instal driver:

mkdir distinct-row-demo
cd distinct-row-demo
npm init -y
npm install pg

Simpan program lengkap berikut sebagai distinct-demo.mjs. Program ini membuat baris artikel sintetis dalam tabel sementara, mengukur kueri biasa sebelum dan sesudah penambahan indeks, serta membandingkan kueri pencarian penerus eksplisit. Program menggunakan satu Client sepanjang proses sehingga setiap kueri berjalan dalam sesi basis data yang sama.

Pengaturan koneksi berasal dari variabel lingkungan PostgreSQL yang umum. Konfigurasikan variabel tersebut untuk basis data lokal yang sudah ada sebelum menjalankan program. node-postgres mendokumentasikan koneksi berbasis lingkungan dan cara meneruskan nilai kueri secara terpisah dari teks SQL. Connecting, Queries

import pg from 'pg';
import assert from 'node:assert/strict';

const { Client } = pg;
const client = new Client({ connectionTimeoutMillis: 5000 });
const limit = 10;

const distinctSql = `
  SELECT DISTINCT topic FROM demo_articles
  ORDER BY topic LIMIT $1::integer
`;

const seekSql = `
  WITH RECURSIVE topics(topic, step) AS (
    (SELECT topic, 1 FROM demo_articles ORDER BY topic LIMIT 1)
    UNION ALL
    SELECT n.topic, t.step + 1
    FROM topics AS t
    CROSS JOIN LATERAL (
      SELECT a.topic FROM demo_articles AS a
      WHERE a.topic > t.topic ORDER BY a.topic LIMIT 1
    ) AS n
    WHERE t.step < $1::integer
  )
  SELECT topic FROM topics ORDER BY topic
`;

async function showPlan(label, sql) {
  const plan = await client.query(
    `EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) ${sql}`, [limit]
  );
  console.log(`\n${label}`);
  console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));
}

try {
  await client.connect();
  const version = await client.query('SHOW server_version');
  console.log('PostgreSQL', version.rows[0].server_version);

  await client.query(`
    CREATE TEMP TABLE demo_articles (
      article_id integer NOT NULL,
      topic text COLLATE "C" NOT NULL,
      title text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO demo_articles (article_id, topic, title)
    SELECT n,
           'topic-' || lpad((((n - 1) / 1000) + 1)::text, 3, '0'),
           'Sample article ' || n::text
    FROM generate_series(1, 100000) AS s(n)
  `);

  await client.query('ANALYZE demo_articles');
  await showPlan('Before index', distinctSql);

  await client.query(
    'CREATE INDEX demo_articles_topic_idx ON demo_articles (topic)'
  );
  await client.query('ANALYZE demo_articles');
  await showPlan('After index', distinctSql);
  await showPlan('Successor seeks', seekSql);

  const ordinary = await client.query(distinctSql, [limit]);
  const seeking = await client.query(seekSql, [limit]);
  assert.deepEqual(seeking.rows, ordinary.rows);
  assert.equal(ordinary.rows.length, limit);
  console.log(ordinary.rows.map(row => row.topic));
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

Generator data menghasilkan bilangan bulat dari 1 hingga 100.000 menggunakan fungsi PostgreSQL generate_series yang terdokumentasi. Aritmetika tersebut memberikan topik yang sama kepada setiap kelompok berurutan yang terdiri dari 1.000 artikel. ANALYZE menyediakan statistik tabel yang digunakan perencana saat memilih rencana. Set Returning Functions, ANALYZE

Jalankan program dengan nama basis data lokal yang sudah ada. Misalnya, jika basis data tersebut bernama postgres:

PGHOST=localhost PGPORT=5432 PGDATABASE=postgres node distinct-demo.mjs

Nama topik yang diharapkan adalah topic-001 hingga topic-010; asersi memeriksa jumlah hasil dan kesesuaian antara kedua kueri. Sintaks JavaScript dan perhitungan streaming 9.001 masukan telah diperiksa untuk artikel ini. Eksekusi basis data tidak tersedia di lingkungan riset, sehingga waktu dan rencana PostgreSQL yang direkam sengaja tidak disertakan.

Mengapa pemindaian hanya indeks mungkin tetap melakukan banyak pekerjaan

Index Only Scan berarti kueri dapat memperoleh nilai kolom yang diminta dari indeks. Ini tidak berarti hanya ada satu entri indeks untuk setiap hasil berbeda. Periksa jumlah baris aktualnya meskipun tabel itu sendiri jarang dikunjungi.

Akses heap merupakan pertimbangan terpisah. PostgreSQL memeriksa peta visibilitas dan mungkin tetap mengunjungi tupel heap ketika halaman terkait tidak ditandai all-visible. Akibatnya, baris demonstrasi yang baru disisipkan dapat menghasilkan heap fetch meskipun menggunakan rencana hanya indeks. Periksa Heap Fetches bersama baris pemindaian. Index-Only Scans and Covering Indexes

Menambahkan kolom payload tambahan dengan INCLUDE dapat menyediakan lebih banyak nilai untuk pemindaian hanya indeks, tetapi tidak mengubah jumlah topik berbeda yang diminta. Pilih cakupan indeks untuk menangani pekerjaan akses heap yang terbukti; ukur konsumsi duplikat secara terpisah. Index-Only Scans and Covering Indexes

Apakah skip scan PostgreSQL 18 menyelesaikan ini secara otomatis?

PostgreSQL 18 memperkenalkan dukungan skip scan B-tree. Tujuan yang didokumentasikan adalah meningkatkan beberapa pencarian indeks multikolom ketika batasan pada kolom berikutnya dapat memandu pencarian berulang di berbagai nilai kolom sebelumnya. Fitur ini diperkenalkan pada versi 18; jangan menganggapnya tersedia di PostgreSQL 17 atau versi sebelumnya. Release 18, Multicolumn Indexes

Melompat secara otomatis dari satu kunci awal yang berbeda ke kunci berikutnya biasa disebut loose index scan. Wiki proyek PostgreSQL membedakan operasi ini dari skip scan indeks yang berorientasi predikat dan mendokumentasikan emulasi SQL rekursif. Karena itu, pengumuman skip scan PostgreSQL 18 tidak membuktikan adanya pemindaian longgar otomatis untuk kueri DISTINCT satu kolom ini. Loose indexscan

Kueri pencarian penerus dalam program ini mengadaptasi emulasi tersebut: temukan topik terkecil, lalu berulang kali temukan topik terkecil yang lebih besar daripada topik sebelumnya. Langkah rekursif berhenti setelah sepuluh hasil atau ketika tidak ada penerus. Dengan indeks yang sesuai, setiap pencarian dapat melompat melewati kelompok duplikat sebelumnya. Bandingkan rencana aktual dan waktu eksekusinya dengan kueri biasa alih-alih mengasumsikan peningkatan kecepatan. Loose indexscan

Contoh ini sengaja mensyaratkan topik non-null, urutan menaik, kolasi C yang dideklarasikan, dan limit positif. Limit nol tetap akan mengembalikan jangkar rekursif, sehingga implementasi umum harus menangani nol secara terpisah. Topik yang dapat bernilai null memerlukan penanganan eksplisit karena perbandingan lebih besar dari tidak akan mencakup nilai null. Jika Anda menambahkan filter artikel, terapkan kondisi yang sama pada pencarian awal dan setiap pencarian penerus; jika tidak, kueri hasil penulisan ulang dapat mengembalikan himpunan yang berbeda.

Pilih perubahan berikutnya berdasarkan bukti

Gunakan panduan keputusan ini setelah mengumpulkan rencana. Panduan ini memisahkan pekerjaan yang dapat dihilangkan oleh indeks pengurutan dari pekerjaan yang disebabkan oleh nilai topik berulang.

Hasil yang diamati Hal yang perlu diselidiki berikutnya
Pemindaian memasok data ke agregasi dan pengurutan akhir Bandingkan rencana yang ada dengan indeks B-tree yang sesuai dengan urutan topik.
Pemindaian terurut mengeluarkan ribuan baris untuk sepuluh topik Ukur ukuran kelompok duplikat dan bandingkan pencarian penerus eksplisit.
Pemindaian hanya indeks melaporkan banyak heap fetch Selidiki visibilitas dan pekerjaan akses heap secara terpisah dari jumlah duplikat.
Estimasi sangat berbeda dari baris aktual Perbarui statistik dengan ANALYZE, lalu ukur kembali.
Kueri biasa yang terindeks sudah murah Pertahankan kueri yang lebih sederhana kecuali persyaratan lain yang terukur membenarkan penulisan ulang.

Hindari memindahkan LIMIT 10 ke subkueri yang memilih sepuluh artikel mentah sebelum menerapkan DISTINCT. Dalam urutan contoh, kesepuluh artikel itu berasal dari topic-001, sehingga kueri hasil penulisan ulang mengembalikan satu topik, bukan sepuluh. Ini mengubah tugasnya.

Pastikan juga kunci distinct tetap tepat. Menambahkan article_id ke kolom yang dipilih membuat setiap artikel menjadi baris keluaran berbeda, sehingga deduplikasi topik gagal. Jika tugasnya berubah menjadi memilih satu artikel perwakilan untuk setiap topik, DISTINCT ON PostgreSQL mendukung operasi tersebut, dengan ekspresinya harus cocok dengan ekspresi pengurutan paling kiri. Operasi itu sendiri tidak menjanjikan lebih sedikit baris yang dipindai. SELECT

Untuk sepuluh topik artikel berbeda pertama, mulailah dengan membandingkan keluaran aktual pemindaian dengan sepuluh hasil yang dikembalikan. Lalu uji indeks yang sesuai dan, jika kelompok duplikat mendominasi, kueri pencarian penerus yang dibatasi. Pertahankan himpunan topik dan urutan yang diminta saat memilih pendekatan dengan beban terukur yang sesuai untuk data Anda.

Sumber