PostgreSQL Performance & Query Optimization · Pelajaran

Peta Visibilitas dan Pemindaian Hanya-Indeks

Jaga peta visibilitas tetap mutakhir agar perencana dapat melayani pemindaian hanya-indeks tanpa mengambil data dari heap.

Pelajaran 3 dari 413 langkah

Peta Visibilitas dan Pemindaian Hanya-Indeks adalah pelajaran PostgreSQL Performance & Query Optimization gratis di CoddyKit. Ini adalah pelajaran 3 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar PostgreSQL Performance & Query Optimization, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus PostgreSQL Performance & Query Optimization mencakup 4 pelajaran total.

Mengapa Pemindaian Indeks Tetap Menyentuh Heap

Di PostgreSQL, pemindaian indeks biasa menemukan baris yang cocok di dalam indeks, tetapi tidak dapat sepenuhnya mengandalkan indeks untuk mengetahui apakah setiap baris terlihat oleh transaksi Anda. MVCC menyimpan informasi keterlihatan (xmin/xmax) hanya di tuple heap, bukan di entri indeks.

Karena itu, untuk setiap kecocokan indeks, pelaksana kueri harus melakukan pengambilan dari heap untuk memeriksa keterlihatan.
Akses heap acak ini mendominasi biaya pemindaian indeks, terutama pada tabel besar.

Peta keterlihatan (VM) ada agar PostgreSQL dapat melewati pengambilan dari heap tersebut ketika keamanannya dapat dibuktikan, sehingga memungkinkan pemindaian hanya indeks.

Hal yang Disimpan Peta Keterlihatan

Peta keterlihatan adalah bitmap ringkas yang disimpan bersama setiap tabel (dalam fork _vm). Peta ini menyimpan dua bit per halaman heap:

all-visible: setiap tuple pada halaman terlihat oleh semua transaksi saat ini dan mendatang.
all-frozen: setiap tuple pada halaman dibekukan (digunakan untuk melewati halaman selama vakum anti-pembungkusan).

Untuk pemindaian hanya indeks, yang penting hanyalah bit all-visible. Jika bit all-visible pada halaman heap ditetapkan, perencana mengetahui bahwa tuple mana pun yang ditunjuk pada halaman tersebut terlihat, sehingga dapat memberikan jawaban hanya dari entri indeks.

Siapa yang Menetapkan Bit All-Visible

Bit all-visible ditetapkan oleh VACUUM (termasuk autovacuum). Ketika vakum memproses halaman heap dan menemukan bahwa semua tuple terlihat oleh semua pihak serta tidak ada tuple mati yang perlu dihapus, vakum menetapkan bit all-visible untuk halaman tersebut.

Penyisipan, pembaruan, dan penghapusan menghapus bit untuk halaman yang terdampak.
Bit tersebut hanya akan ditetapkan lagi ketika vakum mengunjungi kembali halaman itu.

Akibatnya, tabel yang sering ditulis tetapi jarang divakum akan memiliki peta keterlihatan yang usang, dan pemindaian hanya indeks akan diam-diam berubah menjadi pemindaian indeks biasa dengan pengambilan dari heap.

-- Force a vacuum so the VM bits get set for an existing table
VACUUM (VERBOSE) orders;

-- See how many heap pages are currently marked all-visible / all-frozen
SELECT relname,
       relpages,
       pg_relation_size(oid) AS heap_bytes
FROM pg_class
WHERE relname = 'orders';

Memeriksa Cakupan VM dengan pg_visibility

Ekstensi pg_visibility memungkinkan Anda mengukur secara tepat bagian tabel yang ditandai all-visible. Ini adalah diagnostik yang paling berguna untuk kesehatan pemindaian hanya indeks.

pg_visibility_map_summary('tbl') mengembalikan jumlah halaman all-visible dan all-frozen.
Bandingkan jumlah tersebut dengan relpages untuk mendapatkan rasio cakupan.

Cakupan rendah pada tabel yang diharapkan melayani pemindaian hanya indeks merupakan tanda bahaya: VM sudah usang dan memerlukan vakum.

CREATE EXTENSION IF NOT EXISTS pg_visibility;

SELECT c.relname,
       c.relpages,
       v.all_visible,
       v.all_frozen,
       round(100.0 * v.all_visible / NULLIF(c.relpages, 0), 1) AS pct_all_visible
FROM pg_class c
CROSS JOIN LATERAL pg_visibility_map_summary(c.oid) AS v
WHERE c.relname = 'orders';

Persyaratan untuk Pemindaian Hanya Indeks

Agar perencana memilih pemindaian hanya indeks, tiga hal harus terpenuhi:

Indeks harus mencakup setiap kolom yang diperlukan kueri (di dalam kunci indeks atau sebagai muatan INCLUDE).
Kueri harus merujuk hanya kolom yang tercakup tersebut dalam SELECT, WHERE, ORDER BY, dan sebagainya.
Cukup banyak halaman tabel harus ditandai all-visible sehingga pengambilan heap yang dihemat lebih besar daripada biaya pemindaian indeks.

Bahkan indeks yang sepenuhnya mencakup kebutuhan kueri akan kembali melakukan pengambilan dari heap jika VM sudah usang. Cakupan dan kesegaran sama-sama diperlukan.

-- A covering index for: SELECT customer_id, status WHERE customer_id = ?
CREATE INDEX idx_orders_cust_status
    ON orders (customer_id) INCLUDE (status);

Membaca Rencana: Pengambilan dari Heap

Bukti bahwa VM bekerja dengan baik terdapat dalam EXPLAIN (ANALYZE, BUFFERS). Simpul pemindaian hanya indeks melaporkan penghitung Pengambilan Heap.

Pengambilan Heap: 0 berarti setiap baris yang cocok berasal dari halaman yang ditandai all-visible — kondisi ideal.
Jumlah Pengambilan Heap yang besar berarti banyak halaman tidak all-visible, sehingga pemindaian tetap menanggung biaya heap acak.

Pantau angka ini setelah lonjakan penulisan besar: angka tersebut akan meningkat hingga vakum berikutnya menetapkan ulang bit VM.

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customer_id, status
FROM orders
WHERE customer_id = 42;

-- Look for:
--   Index Only Scan using idx_orders_cust_status on orders
--     Heap Fetches: 0

Demo: VM Usang Menyebabkan Pengambilan dari Heap

Anda dapat mereproduksi penurunan kinerja ini secara deterministik. Sisipkan baris, jalankan kueri hanya indeks, dan amati Pengambilan Heap meningkat karena bit all-visible pada halaman yang baru disisipkan telah dihapus.

Segera setelah penyisipan, halaman baru belum all-visible, sehingga Pengambilan Heap > 0.
Setelah VACUUM eksplisit, bit tersebut ditetapkan ulang dan Pengambilan Heap kembali menjadi 0.

Inilah tepatnya regresi diam-diam yang menimpa tabel dengan banyak penulisan di lingkungan produksi.

INSERT INTO orders (customer_id, status)
SELECT 42, 'NEW' FROM generate_series(1, 50000);

-- Heap Fetches will be high here (new pages not all-visible)
EXPLAIN (ANALYZE, COSTS OFF)
SELECT customer_id, status FROM orders WHERE customer_id = 42;

VACUUM orders;

-- Now Heap Fetches should be back near 0
EXPLAIN (ANALYZE, COSTS OFF)
SELECT customer_id, status FROM orders WHERE customer_id = 42;

Menyetel Autovacuum agar VM Tetap Segar

Perbaikan yang bertahan lama adalah membuat autovacuum berjalan cukup sering pada tabel yang sibuk. Parameter utama per tabel:

autovacuum_vacuum_scale_factor — pecahan tabel yang harus berubah sebelum vakum dipicu. Turunkan nilainya pada tabel besar yang sering mengalami perubahan.
autovacuum_vacuum_threshold — batas bawah tetap berupa jumlah baris yang berubah.
autovacuum_vacuum_insert_scale_factor / _insert_threshold — ditambahkan pada PG13, parameter ini memicu vakum pada tabel yang hanya melakukan penyisipan, yang sebelumnya tidak pernah divakum sehingga VM-nya tidak pernah ditetapkan.

Penggantian nilai per tabel melalui ALTER TABLE ... SET lebih disarankan daripada perubahan global.

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_insert_scale_factor = 0.02,
    autovacuum_vacuum_insert_threshold = 1000
);

Jebakan Tabel yang Hanya Melakukan Penyisipan

Sebelum PostgreSQL 13, tabel yang hanya ditambahi (log, peristiwa, deret waktu) sering gagal dalam pemindaian hanya indeks: autovacuum dipicu oleh tuple mati, sedangkan penyisipan murni tidak membuat tuple mati, sehingga vakum tidak pernah berjalan dan VM tetap kosong.

Akibatnya: pemindaian hanya indeks pada tabel-tabel ini selalu membayar biaya penuh pengambilan dari heap.
Pemicu autovacuum berbasis penyisipan pada PG13 memperbaiki perilaku bawaan tersebut.

Pada versi yang lebih lama, solusinya adalah menjadwalkan VACUUM (misalnya melalui cron) agar bit all-visible ditetapkan setelah setiap pemuatan sekumpulan data.

-- Pre-PG13 workaround: vacuum the append-only table after each batch load
-- (run on a schedule)
VACUUM (FREEZE) events;

-- FREEZE also sets all-frozen bits, helping anti-wraparound vacuum later

Transaksi yang Berjalan Lama Menahan VM

Bahkan autovacuum yang agresif tidak dapat menandai halaman sebagai all-visible jika transaksi lama mungkin masih perlu melihat tuple di dalamnya (atau mungkin telah membuatnya). Transaksi yang berjalan lama atau slot replikasi lama menahan horizon xmin.

Vakum tidak dapat melewati horizon tersebut, sehingga tidak dapat menetapkan bit all-visible untuk halaman yang baru saja berubah.
Gejalanya: cakupan VM tetap rendah dan Pengambilan Heap tetap tinggi, sesering apa pun Anda menjalankan vakum.

Cari sesi yang menganggur dalam transaksi dan slot replikasi usang — keduanya merupakan penyebab tersembunyi yang umum dari gagalnya pemindaian hanya indeks.

-- Find the oldest transaction holding back the xmin horizon
SELECT pid,
       state,
       now() - xact_start AS xact_age,
       backend_xmin,
       query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

Memverifikasi Seluruh Siklus

Gabungkan semua bagian menjadi pemeriksaan kesehatan yang dapat diulang untuk setiap tabel yang diharapkan melayani pemindaian hanya indeks:

Pastikan indeks yang mencakup kebutuhan kueri tersedia untuk kueri utama.
Ukur cakupan VM dengan pg_visibility_map_summary.
Jalankan EXPLAIN (ANALYZE, BUFFERS) dan periksa apakah Pengambilan Heap rendah.
Jika cakupan rendah: setel autovacuum, hentikan transaksi yang berjalan lama, atau jadwalkan vakum manual.

Tujuannya adalah keadaan stabil ketika Pengambilan Heap tetap mendekati nol di antara vakum, bukan melonjak setelah setiap lonjakan penulisan.

-- One-shot coverage + size snapshot for a candidate table
SELECT c.relname,
       c.relpages,
       v.all_visible,
       round(100.0 * v.all_visible / NULLIF(c.relpages, 0), 1) AS pct_all_visible,
       (SELECT count(*) FROM pg_index i WHERE i.indrelid = c.oid) AS n_indexes
FROM pg_class c
CROSS JOIN LATERAL pg_visibility_map_summary(c.oid) AS v
WHERE c.relname = 'orders';

Pemeriksaan Singkat

Pemindaian hanya indeks pada tabel yang banyak melakukan penulisan menunjukkan jumlah Heap Fetches yang tinggi segera setelah penyisipan massal, meskipun indeks yang sepenuhnya mencakup kebutuhan kueri tersedia. Apa penyebab dan perbaikan yang paling langsung?

Jawab di pelajaran interaktif

Ringkasan

Pemindaian hanya indeks bergantung pada peta keterlihatan, bukan sekadar pada ketersediaan indeks yang mencakup kebutuhan kueri.

Bit all-visible pada VM memungkinkan pelaksana kueri melewati pengambilan dari heap; bit ini ditetapkan oleh VACUUM dan dihapus oleh setiap penulisan ke halaman.
Ukur kesegarannya dengan pg_visibility_map_summary dan verifikasi melalui Pengambilan Heap dalam EXPLAIN (ANALYZE, BUFFERS).
Jaga VM tetap segar dengan menyetel autovacuum (termasuk pemicu berbasis penyisipan untuk tabel yang hanya ditambahi) serta menghilangkan transaksi yang berjalan lama dan slot replikasi usang yang menahan horizon xmin.

Cakupan dan kesegaran secara bersama-sama menjaga Pengambilan Heap tetap nol.

Gratis untuk memulai

Belajar SQL dengan tutor AI — gratis

Tulis dan jalankan kode asli di browser kamu, dapatkan bantuan instan dari tutor AI 24/7, dan lanjutkan di mana kamu tinggalkan di web atau aplikasi.

Kursus: 22
Pelajaran: 88

Mulai belajar gratis Lihat kursus lengkapnya

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Peta Visibilitas dan Pemindaian Hanya-Indeks” gratis?

Ya — teks lengkap “Peta Visibilitas dan Pemindaian Hanya-Indeks” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus PostgreSQL Performance & Query Optimization, upgrade ke CoddyKit PRO. Kursus PostgreSQL Performance & Query Optimization mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Peta Visibilitas dan Pemindaian Hanya-Indeks”?

Jaga peta visibilitas tetap mutakhir agar perencana dapat melayani pemindaian hanya-indeks tanpa mengambil data dari heap. Kamu berlatih PostgreSQL Performance & Query Optimization dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai PostgreSQL Performance & Query Optimization?

Tidak diperlukan pengalaman sebelumnya. PostgreSQL Performance & Query Optimization di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 3 dari 4.

Berapa lama pelajaran “Peta Visibilitas dan Pemindaian Hanya-Indeks” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran PostgreSQL Performance & Query Optimization ini?

Ya. Setiap pelajaran PostgreSQL Performance & Query Optimization menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

Visibilitas Tupel, xmin, dan xmax
Pembaruan HOT dan Rantai Tupel Khusus Heap
Peta Visibilitas dan Pemindaian Hanya-Indeks
Pembuatan WAL dan Penguatan Penulisan

← Kembali ke PostgreSQL Performance & Query Optimization