KAL
Menu

Statistik

Menjaga akurasi statistik perencana melalui ANALYZE dan memahami peran autovacuum.

Daftar isi

Topik ini membahas statistik perencana, data ringkas tentang isi tabel yang menjadi dasar seluruh keputusan eksekusi, beserta ANALYZE sebagai alat memutakhirnya dan autovacuum sebagai penjaga otomatisnya. Estimasi yang salah di akar rencana eksekusi hampir selalu berujung pada statistik yang basi, sehingga topik ini menempati posisi penting dalam modul performa.

Blok penyiapan dataset#

bash 
# dataset besar deterministik untuk pengamatan statistik
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS sirkulasi CASCADE;
CREATE TABLE sirkulasi (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    anggota_id INTEGER NOT NULL,
    status     TEXT NOT NULL
);
INSERT INTO sirkulasi (anggota_id, status)
SELECT g % 500 + 1, CASE WHEN g % 3 = 0 THEN 'kembali' ELSE 'dipinjam' END
FROM generate_series(1, 50000) g;
ANALYZE sirkulasi;
SQL

Apa yang disimpan statistik#

ANALYZE mengambil sampel baris lalu menyimpan ringkasannya pada katalog sistem, di antaranya banyaknya baris, banyaknya nilai berbeda, dan daftar nilai yang paling sering muncul. Tampilan pg_stats memaparkannya dengan nama yang terbaca, dan keluarannya menjadi bahan belajar cara perencana memperkirakan.

bash 
# statistik kolom penulis dan status
psql -U postgres -d perpustakaan -c "SELECT attname, n_distinct FROM pg_stats WHERE tablename = 'sirkulasi' AND attname IN ('anggota_id', 'status') ORDER BY attname;"
text Keluaran 
  attname   | n_distinct
------------+------------
 anggota_id |        500
 status     |          2
(2 rows)

n_distinct menyatakan perkiraan banyaknya nilai berbeda, dengan nilai negatif yang berarti proporsi terhadap jumlah baris, dan angka inilah yang memandu estimasi baris pada setiap penyaringan. Detail tampilan pg_stats tercantum pada dokumentasi resmi, dan membacanya sekali menyingkirkan teka-teki mengapa EXPLAIN menebak jumlah baris tertentu.

ANALYZE manual dan kapan dibutuhkan#

Autovacuum menjalankan ANALYZE secara otomatis menurut ambang perubahan baris, tetapi dua keadaan menuntut ANALYZE manual, yaitu setelah pemuatan data besar dan setelah perubahan pola yang mengubah sebaran nilai secara tiba-tiba. Tanpa itu, perencana menimbang tabel dengan gambar lama.

bash 
# estimasi baris sebelum dan sesudah ANALYZE
psql -U postgres -d perpustakaan <<'SQL'
INSERT INTO sirkulasi (anggota_id, status)
SELECT g % 2 + 1, 'kembali' FROM generate_series(1, 30000) g;
SELECT reltuples::bigint AS estimasi_lama FROM pg_class WHERE relname = 'sirkulasi';
ANALYZE sirkulasi;
SELECT reltuples::bigint AS estimasi_baru FROM pg_class WHERE relname = 'sirkulasi';
DELETE FROM sirkulasi WHERE anggota_id IN (1, 2) AND status = 'kembali' AND id > 50000;
SQL
text Keluaran 
INSERT 0 30000
 estimasi_lama
---------------
         50000
(1 row)

ANALYZE
 estimasi_baru
---------------
         80000
(1 row)

DELETE 30000

Perkiraan jumlah baris yang tersimpan pada pg_class berubah setelah ANALYZE, dan blok ini menampilkannya sebelum serta sesudah, dengan pembersihan baris tambahan di akhir agar dataset tetap rapi. Penyaringan id di atas 50000 mengisolasi baris yang baru ditambahkan, dan jumlah baris akhirnya kembali ke lima puluh ribu.

Autovacuum sebagai penjaga#

Autovacuum berjalan sebagai proses latar yang mengosongkan baris mati dan memutakhirkan statistik menurut ambang bawaan, yang menghitung proporsi tabel yang berubah ditambah bilangan tetap. Dua pengaturannya yang paling sering ditemui adalah frekuensi per tabel melalui penyimpanan parameter tabel dan ambang yang menyesuaikan tabel yang sangat besar.

bash 
# status autovacuum dan penghitung tabel
psql -U postgres -d perpustakaan -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'sirkulasi';"
text Keluaran 
  relname  | n_live_tup | n_dead_tup
-----------+------------+------------
 sirkulasi |      80000 |      30000
(1 row)

n_live_tup dan n_dead_tup menunjukkan baris hidup serta baris mati yang menunggu dibersihkan, dan keduanya menjadi indikator sehat yang dipantau pada modul administrasi. Tabel yang ramai ditulis dengan n_dead_tup yang menumpuk menandakan autovacuum tertinggal, kondisi yang dibahas lagi pada topik monitoring.

Membaca sinyal dari pg_stat_user_tables#

Selain ringkasan sebaran, server juga menyimpan penghitung aktivitas per tabel, dan dua angka yang paling sering dibaca adalah banyaknya baris hidup dan baris mati. Baris mati adalah sisa UPDATE dan DELETE yang menunggu dibersihkan VACUUM, dan tumbuhnya tanpa henti menjadi sinyal paling awal bahwa pemeliharaan tertinggal.

bash 
# baris mati pada tabel yang baru diisi ulang
psql -U postgres -d perpustakaan -c "SELECT n_dead_tup = 0 AS bersih FROM pg_stat_user_tables WHERE relname = 'sirkulasi';"
text Keluaran 
 bersih
--------
 f
(1 row)

Keluaran yang benar pada tabel yang sehat adalah nilai benar, dan apabila angka baris mati menumpuk pada tabel yang ramai ditulis, frekuensi autovacuumnya ditinjau melalui penyimpanan parameter tabel. Penghitung ini kembali muncul pada topik monitoring karena menjadi indikator harian yang paling murah dibaca.

Kesalahan umum#

Kesalahan pertama adalah memuat jutaan baris lalu langsung mengukur performa, karena estimasinya masih memakai gambar kosong, dan ANALYZE menjadi langkah penutup pemuatan yang wajib. Kesalahan kedua adalah menonaktifkan autovacuum demi kenyamanan penulisan, keputusan yang menumpuk baris mati dan membengkakkan tabel tanpa alarm.

Kesalahan ketiga adalah menaikkan default_statistics_target untuk seluruh server demi satu kolom yang rumit, yang memperlambat setiap ANALYZE, sedangkan target per kolom melalui ALTER TABLE … ALTER COLUMN … SET STATISTICS lebih terarah. Kesalahan keempat adalah mengira ANALYZE membersihkan baris mati, padahal tugas itu milik VACUUM, dan keduanya berpasangan dalam autovacuum dengan peran yang berbeda.

Ringkasan#

  • ANALYZE mengambil sampel dan menyimpan ringkasan sebaran data yang menjadi dasar estimasi perencana.
  • pg_stats dan pg_class memaparkan statistik tersebut, dengan n_distinct dan reltuples sebagai angka utamanya.
  • Pemuatan besar dan perubahan pola data menuntut ANALYZE manual, meskipun autovacuum menjalankannya secara berkala.
  • Autovacuum mengosongkan baris mati dan memutakhirkan statistik, dan penghitung pg_stat_user_tables menjadi indikator kesehatannya.

Referensi#