KAL
Menu

Pengindeksan JSONB

Mempercepat query JSONB dengan index GIN.

Daftar isi

Topik ini membahas pengindeksan dokumen JSONB, yaitu index GIN yang membuat penyaringan containment dilayani tanpa memindai seluruh tabel, serta index ekspresi btree untuk pencarian nilai tunggal yang panas. Seperti modul sebelumnya, rencana eksekusinya dibaca sebagai jenis simpulnya agar keluaran tetap sama di lingkungan mana pun.

Blok penyiapan dataset#

bash 
# dataset profil berjumlah besar yang deterministik
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS profil CASCADE;
CREATE TABLE profil (
    id   SERIAL PRIMARY KEY,
    nama TEXT NOT NULL,
    data JSONB NOT NULL
);
INSERT INTO profil (nama, data)
SELECT 'Pengguna ' || g,
       jsonb_build_object('kota', (ARRAY['Bandung', 'Yogyakarta', 'Surabaya'])[1 + g % 3], 'umur', 18 + g % 40)
FROM generate_series(1, 20000) g;
ANALYZE profil;
SQL

Dua puluh ribu profil menyebarkan tiga kota secara bergilir, sehingga penyaringan satu kota menyentuh sepertiga tabel dan perbedaan rencananya terlihat.

GIN menindeks isi dokumen#

GIN, kependekan generalized inverted index, memetakan setiap kunci dan nilai di dalam dokumen menuju barisnya, sehingga operator containment menemukan kandidatnya tanpa membaca tabel penuh. Blok berikut mengukur rencana sebelum dan sesudah index GIN dibuat.

bash 
# rencana penyaringan kota sebelum dan sesudah GIN
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT COUNT(*) FROM profil WHERE data @> '{\"kota\": \"Bandung\"}';" | grep -oE "Seq Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "CREATE INDEX idx_profil_data ON profil USING GIN (data);"
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT COUNT(*) FROM profil WHERE data @> '{\"kota\": \"Bandung\"}';" | grep -oE "Seq Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "DROP INDEX idx_profil_data;"
text Keluaran 
      1 Seq Scan
CREATE INDEX
      1 Bitmap Heap Scan
      1 Bitmap Index Scan
DROP INDEX

Rencana pertama memindai seluruh tabel, sedangkan rencana kedua menyusuri index GIN melalui sepasang simpul bitmap, dan keputusan itu terbaca dari jenis simpulnya. GIN melayani operator @>, <@, ?, ?|, dan ?& dalam satu index, keunggulan yang membuat satu index menopang pola penyaringan dokumen yang beragam.

jsonb_path_ops untuk index yang lebih ramping#

GIN bentuk bawaan pada kolom jsonb menindeks setiap kunci dan setiap nilai secara terpisah. Kelas operator jsonb_path_ops menindeks pasangan kunci dan nilainya sekaligus, sehingga indexnya lebih kecil dan lebih cepat untuk containment, tetapi tidak mendukung operator keberadaan kunci seperti ?.

bash 
# GIN dengan jsonb_path_ops
psql -U postgres -d perpustakaan <<'SQL'
CREATE INDEX idx_profil_path ON profil USING GIN (data jsonb_path_ops);
SELECT pg_size_pretty(pg_relation_size('idx_profil_path')) AS ukuran_index;
DROP INDEX idx_profil_path;
SQL
text Keluaran 
CREATE INDEX
 ukuran_index
--------------
 88 kB
(1 row)

DROP INDEX

Ukuran indexnya terbaca langsung, dan pemilihannya mengikuti pola query, yaitu jsonb_path_ops untuk containment murni pada dokumen besar, sedangkan bawaan jsonb_ops untuk kebutuhan keberadaan kunci. Dokumen yang memuat banyak kunci yang sama antar baris memperoleh penghematan yang paling terasa pada bentuk path ops ini.

Index ekspresi untuk nilai yang panas#

Sifat yang disaring hampir setiap query lebih murah diangkat menjadi index ekspresi btree pada jalurnya, karena pencarian kesamaan tunggal dilayani btree lebih ramping daripada GIN. Blok berikut mengindeks kota sebagai ekspresi lalu membaca rencana penyaringannya.

bash 
# index ekspresi btree pada kota
psql -U postgres -d perpustakaan <<'SQL'
CREATE INDEX idx_profil_kota ON profil ((data->>'kota'));
ANALYZE profil;
SQL
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT COUNT(*) FROM profil WHERE data->>'kota' = 'Bandung';" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "DROP INDEX idx_profil_kota; DROP TABLE profil;"
text Keluaran 
CREATE INDEX
ANALYZE
      1 Bitmap Heap Scan
      1 Bitmap Index Scan
DROP INDEX
DROP TABLE

Rencananya menyusuri index ekspresi melalui bitmap, dan bentuk penyaringannya harus persis sama dengan ekspresi yang diindeks, sebagaimana dibahas pada modul anti-pola. Pemisahan peran yang sehat menempatkan sifat yang panas di kolom sungguhan bila desainnya masih dapat diubah, dan index ekspresi menjadi jalan di tengah ketika kolomnya tidak dapat ditambah.

Kesalahan umum#

Kesalahan pertama adalah mengira index btree biasa melayani containment, padahal dokumen berisi banyak kunci dan GIN-lah yang cocok dengan strukturnya. Kesalahan kedua adalah memakai jsonb_path_ops sambil menyaring keberadaan kunci dengan ?, yang ditolak karena kelas operatornya tidak menindeks kunci tunggal.

Kesalahan ketiga adalah mengabaikan biaya tulis GIN, karena setiap perubahan dokumen memperbarui banyak entri index, dan GIN yang ditopang parameter fastupdate menanggung pengelompokan tulisannya sendiri. Kesalahan keempat adalah membuat GIN pada tabel yang kecil, karena rencananya tetap memilih pemindaian penuh dan indexnya hanya menambah biaya tulis tanpa dipakai.

Satu perilaku tulis GIN yang perlu dipahami adalah daftar tertunda (pending list), yaitu kumpulan entri baru yang ditumpuk dulu sebelum dimasukkan ke struktur index utamanya, sehingga penulisan cepat dibayar dengan pencarian yang sesekali memeriksa daftar itu. Parameter fastupdate mengatur penumpukannya, dan vacuum menyatukannya ke index utama, pola yang menjadi alasan GIN menyukai tulisan bertahap. Pemantauannya melalui pg_stat_user_indexes beserta ukuran indexnya memperlihatkan kapan penyatuan itu bekerja.

Ringkasan#

  • GIN menindeks kunci dan nilai di dalam dokumen, dan melayani containment beserta operator keberadaan kunci.
  • jsonb_path_ops menindeks pasangan kunci dan nilai, lebih ramping dan lebih cepat untuk containment murni.
  • Sifat yang disaring terus-menerus dilayani index ekspresi btree dengan bentuk ekspresi yang persis sama.
  • Biaya tulis GIN nyata, sehingga indexnya dibuat mengikuti pola query pada volume data yang nyata.

Referensi#