KAL
Menu

Anti-pola query

Mengenali pola N+1 dari aplikasi dan fungsi pada kolom yang merusak pemakaian index.

Daftar isi

Topik ini menutup modul index dan performa dengan anti-pola, yaitu pola query yang sah secara sintaks tetapi merusak performa secara sistematis. Anti-pola yang dibedah dikenali dari jenis simpul rencananya, dan setiap pola memiliki perbaikan yang jelas setelah penyebabnya terbaca.

Blok penyiapan dataset#

bash 
# dataset besar deterministik untuk bukti rencana
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS judul_uji CASCADE;
CREATE TABLE judul_uji (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, judul TEXT NOT NULL);
INSERT INTO judul_uji (judul) SELECT 'Koleksi nomor ' || g FROM generate_series(1, 20000) g;
CREATE INDEX idx_judul_uji ON judul_uji (judul);
ANALYZE judul_uji;
SQL

Fungsi pada kolom yang berindex#

Index btree tersusun atas nilai kolom apa adanya, sehingga penyaringan yang membungkus kolom dengan fungsi tidak dapat menyusuri index itu. Blok berikut membandingkan tiga keadaan, yaitu penyaringan eksak yang dilayani index, penyaringan berfungsi yang tidak, dan penyaringan berfungsi yang kembali dilayani setelah index ekspresinya dibuat.

bash 
# fungsi pada kolom mematikan index biasa, index ekspresi memulihkannya
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT * FROM judul_uji WHERE judul = 'Koleksi nomor 19999';" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT * FROM judul_uji WHERE lower(judul) = 'koleksi nomor 19999';" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "DROP INDEX idx_judul_uji; CREATE INDEX idx_judul_lower ON judul_uji (lower(judul));"
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT * FROM judul_uji WHERE lower(judul) = 'koleksi nomor 19999';" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "DROP TABLE judul_uji;"
text Keluaran 
      1 Index Scan
      1 Seq Scan
DROP INDEX
CREATE INDEX
      1 Bitmap Heap Scan
      1 Index Scan
DROP TABLE

Rencana pertama menyusuri index kolom, rencana kedua memindai tabel, dan rencana ketiga kembali menyusuri index yang kini dibangun atas ekspresi lower(judul). Perbaikannya terletak pada konsistensi, yaitu memilih satu bentuk penyaringan dan mengindeks bentuk yang sama, kebiasaan yang dikenalkan pada topik strategi CREATE INDEX.

N+1 dari lapisan aplikasi#

N+1 muncul ketika aplikasi menjalankan satu query untuk daftar, lalu satu query lagi untuk setiap butir daftar itu, sehingga seratus baris menghasilkan seratus satu query. Setiap query cepat secara terpisah, tetapi biaya perjalanan jaringan dan perencanaannya dikalikan, dan gejalanya baru terasa pada volume nyata.

bash 
# tidak-dijalankan (pola ini muncul dari kode aplikasi, bukan satu pernyataan SQL)
# pola N+1: satu query daftar, lalu satu query per butir
-- SELECT id, judul FROM buku;
-- SELECT penulis FROM buku WHERE id = $1;   -- diulang N kali

Perbaikannya menyatukan kebutuhan itu menjadi satu query dengan join, sebagaimana dipraktikkan pada modul join, atau mengumpulkan id terlebih dahulu lalu mengambil semuanya dengan IN. Pelacakannya dilakukan pada lapisan aplikasi dengan mencatat jumlah query per permintaan, dan log durasi pernyataan di sisi server yang dikonfigurasi melalui log_min_duration_statement menjadi cermin dari sisi basis data, pengaturan yang dibahas pada modul administrasi.

OFFSET besar dan kunci posisi#

OFFSET besar memaksa server menyusuri dan membuang seluruh baris sebelum titik lompatannya, sehingga halaman yang makin belakang makin mahal. Pola paginasi yang tahan beban memakai kunci posisi, yaitu menyaring baris yang id-nya melampaui posisi terakhir halaman sebelumnya.

bash 
# paginasi berbasis kunci posisi
psql -U postgres -d perpustakaan <<'SQL'
CREATE TABLE judul_uji (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, judul TEXT NOT NULL);
INSERT INTO judul_uji (judul) SELECT 'Koleksi nomor ' || g FROM generate_series(1, 20000) g;
CREATE INDEX idx_judul_id ON judul_uji (id);
ANALYZE judul_uji;
SQL
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT * FROM judul_uji WHERE id > 19000 ORDER BY id LIMIT 10;" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "DROP TABLE judul_uji;"
text Keluaran 
CREATE TABLE
INSERT 0 20000
CREATE INDEX
ANALYZE
      1 Index Scan
DROP TABLE

Rencananya menyusuri index lalu berhenti setelah sepuluh baris, biaya yang tetap seberapa pun posisi halamannya, berbeda dengan OFFSET 19000 yang membuang sembilan belas ribu baris lebih dahulu. Kunci posisi menuntut urutan yang stabil, dan kunci pemutus unik yang dibahas sejak modul ORDER BY menjadi syaratnya, sedangkan OFFSET tetap layak untuk halaman-halaman awal yang tipis.

Kesalahan umum#

Kesalahan pertama adalah menyamaratakan index pada kolom tanpa memeriksa bentuk penyaringannya, karena fungsi, cast, dan perhitungan pada kolom mematikannya diam-diam. Kesalahan kedua adalah menunda penanganan N+1 hingga produksi, padahal jumlah query per permintaan tercatat murah pada lingkungan uji.

Kesalahan ketiga adalah mengganti OFFSET dengan LIMIT tanpa kunci posisi yang stabil, sehingga halaman yang sama dapat menampilkan baris yang berbeda antar unduhan. Kesalahan keempat adalah menyalahkan basis data atas lambatnya aplikasi yang mengirim seratus query kecil, dan pengukuran yang jujur memisahkan biaya per query dari biaya jumlahnya.

Penangkap anti-pola yang berjalan terus di server adalah auto_explain, yaitu pustaka tambahan yang mencatat rencana eksekusi pernyataan yang melampaui ambangnya, sehingga rencana yang buruk menemukan jalannya ke log tanpa menunggu insiden. Pengaktifannya membutuhkan pemuatan pustaka pada sesi, dan hasilnya berpasangan dengan log durasi yang dibahas pada modul administrasi. Pasangan ini menjadikan penemuan anti-pola rutin, bukan reaksi atas keluhan yang datang lebih dahulu.

Ringkasan#

  • Fungsi pada kolom terindex mematikan index kolomnya, dan perbaikannya mengindeks ekspresi yang sama dengan query.
  • N+1 melipatgandakan biaya perjalanan dan perencanaan, dan perbaikannya join atau pengambilan berbasis daftar id.
  • OFFSET besar membayar pembuangan baris sebelum titik lompatan, sedangkan kunci posisi menjaga biaya tiap halaman tetap.
  • Anti-pola terdeteksi melalui jenis simpul rencana dan catatan jumlah query, bukan dari dugaan atas kecepatan.

Referensi#