Index pada JSONB

Kolom jsonb memberi fleksibilitas skema yang memukau — sampai tabel tumbuh ratusan ribu baris dan query penyaring mulai terasa lambat. Masalahnya khas: tanpa index, setiap query atribut @> '{"kategori": "elektronik"}' harus membaca dan mengurai seluruh dokumen di setiap baris, karena index B-tree biasa tidak mengerti isi dokumen. PostgreSQL menyediakan dua strategi index untuk JSONB yang karakternya berlawanan: GIN pada seluruh kolom yang siap melayani containment pada path mana pun, dan expression index B-tree pada satu path tertentu yang jauh lebih kecil dan lebih cepat tetapi hanya melayani path itu. Di chapter ini kita membangun keduanya di atas katalog produk Toko Maju Jaya beratribut semi-terstruktur, mengukur ukurannya, lalu memetakan kapan masing-masing layak dipilih.

Dua Strategi Index untuk Dokumen JSONB #

Strategi pertama mengindeks seluruh kolom dengan GIN — inverted index yang memetakan setiap kunci dan nilai di dalam dokumen ke daftar baris pemiliknya. Mekanika dasar GIN sudah kita bedah di chapter GIN Index ; untuk jsonb, kelas operator yang dipakai menentukan operator mana yang dilayani. Strategi kedua memilih satu path yang selalu dikueri — misalnya kategori — lalu mengindeks hasil ekspresi atribut ->> 'kategori' dengan B-tree biasa. Index jenis ini tidak tahu apa-apa tentang struktur dokumen; ia hanya melihat nilai teks hasil ekspresi, persis seperti kolom biasa.

Perbedaan konsekuensinya besar. GIN kolom penuh melayani @>, ?, ?&, ?| pada kombinasi kunci apa pun, tetapi harus menyimpan entri untuk setiap kunci dan nilai di setiap dokumen. Expression index hanya melayani query yang menulis ekspresi yang sama persis, tetapi indexnya seperserempat ukuran GIN dan tidak perlu menulis ulang saat bagian dokumen lain berubah.

Menyiapkan Data Produk #

Tabel produk_attribut berisi 50.000 produk dengan dokumen berisi kategori, merek, harga, tag, dan — hanya pada sepersepuluh produk — kunci garansi:

sql
buat-tabel.sql
-- Katalog produk Toko Maju Jaya dengan atribut semi-terstruktur:
-- tiap produk membawa kombinasi kunci yang berbeda-beda.
CREATE TABLE produk_attribut (
    id      integer PRIMARY KEY,
    nama    text NOT NULL,
    atribut jsonb NOT NULL
);

INSERT INTO produk_attribut (id, nama, atribut)
SELECT g,
       'Produk ' || lpad(g::text, 5, '0'),
       jsonb_strip_nulls(jsonb_build_object(
           'kategori', (ARRAY['elektronik','pakaian','makanan','olahraga'])[1 + (g % 4)],
           'merek',   (ARRAY['Elang','Nusantara','Gunung Jaya'])[1 + (g % 3)],
           'harga',   ((g % 40) + 1) * 2500,
           'tag',     jsonb_build_array((ARRAY['diskon','baru','bestseller'])[1 + (g % 3)], 'gudang-jakarta'),
           'garansi', CASE WHEN g % 10 = 0 THEN '12 bulan' END
       ))
FROM generate_series(1, 50000) AS g;

ANALYZE produk_attribut;

-- Baris 1 dan 2 tanpa garansi; baris 10 memuat kunci garansi.
SELECT id, nama, atribut
FROM produk_attribut
WHERE id IN (1, 2, 10)
ORDER BY id;

Output yang Dihasilkan:

text
CREATE TABLE
INSERT 0 50000
ANALYZE
 id |     nama     |                                                         atribut                                                         
----+--------------+-------------------------------------------------------------------------------------------------------------------------
  1 | Produk 00001 | {"tag": ["baru", "gudang-jakarta"], "harga": 5000, "merek": "Nusantara", "kategori": "pakaian"}
  2 | Produk 00002 | {"tag": ["bestseller", "gudang-jakarta"], "harga": 7500, "merek": "Gunung Jaya", "kategori": "makanan"}
 10 | Produk 00010 | {"tag": ["baru", "gudang-jakarta"], "harga": 27500, "merek": "Nusantara", "garansi": "12 bulan", "kategori": "makanan"}
(3 rows)

Fungsi jsonb_build_object menyusun dokumen dari nilai terhitung, dan jsonb_strip_nulls membuang pasangan yang nilainya NULL — itulah cara garansi hanya muncul pada produk kelipatan sepuluh. Tiga baris contoh memperlihatkan keragaman strukturnya: dokumen produk 1 dan 2 tanpa garansi, produk 10 memuatnya.

GIN pada Kolom Penuh #

Index GIN pada seluruh kolom memakai kelas operator bawaan jsonb_ops. Tiga query berikut menguji tiga operator yang dilayaninya:

sql
gin-kolom.sql
-- GIN pada seluruh kolom jsonb: kelas operator bawaan jsonb_ops
-- mendukung @>, ?, ?&, dan ?|.
CREATE INDEX idx_gin_attribut ON produk_attribut USING gin (atribut);

-- Containment: dokumen memuat pasangan kategori tertentu.
SELECT count(*) AS produk_elektronik
FROM produk_attribut
WHERE atribut @> '{"kategori": "elektronik"}';

-- Keanggotaan kunci: dokumen yang memiliki kunci "garansi".
SELECT count(*) AS produk_bergaransi
FROM produk_attribut
WHERE atribut ? 'garansi';

-- Containment array: dokumen yang tag-nya memuat "diskon".
SELECT count(*) AS produk_diskon
FROM produk_attribut
WHERE atribut @> '{"tag": ["diskon"]}';

SELECT pg_size_pretty(pg_relation_size('idx_gin_attribut')) AS ukuran_gin_kolom;

Output yang Dihasilkan:

text
CREATE INDEX
 produk_elektronik 
-------------------
             12500
(1 row)

 produk_bergaransi 
-------------------
              5000
(1 row)

 produk_diskon 
---------------
         16666
(1 row)

 ukuran_gin_kolom 
------------------
 752 kB
(1 row)

Operator @> mencari dokumen yang memuat pasangan {"kategori": "elektronik"} — 12.500 produk, seperempat katalog. Operator ? menguji keberadaan kunci garansi — 5.000 produk. Containment bekerja sampai ke elemen array: @> '{"tag": ["diskon"]}' mengembalikan 16.666 produk yang array tag-nya memuat elemen "diskon". Ketiganya berjalan tanpa memindai seluruh tabel; harganya adalah index 752 kB — sekitar 9% dari ukuran tabel 8,5 MB, dan itu bayaran untuk fleksibilitas query pada struktur apa pun.

jsonb_path_ops: Lebih Kecil, Hanya @> #

Kelas operator kedua untuk GIN, jsonb_path_ops, membangun entri index dari hash pasangan path-nilai alih-alih setiap kunci dan nilai terpisah:

sql
jsonb-path-ops.sql
-- Kelas operator jsonb_path_ops: index lebih kecil,
-- tetapi hanya operator @> yang bisa dilayani.
CREATE INDEX idx_gin_path_attribut ON produk_attribut
    USING gin (atribut jsonb_path_ops);

SELECT pg_size_pretty(pg_relation_size('idx_gin_attribut')) AS ukuran_jsonb_ops,
       pg_size_pretty(pg_relation_size('idx_gin_path_attribut')) AS ukuran_jsonb_path_ops;

-- Query @> yang sama tetap berjalan dan hasilnya tidak berubah.
SELECT count(*) AS produk_elektronik
FROM produk_attribut
WHERE atribut @> '{"kategori": "elektronik"}';

Output yang Dihasilkan:

text
CREATE INDEX
 ukuran_jsonb_ops | ukuran_jsonb_path_ops 
------------------+-----------------------
 752 kB           | 496 kB
(1 row)

 produk_elektronik 
-------------------
             12500
(1 row)

Index turun dari 752 kB menjadi 496 kB — sekitar sepertiga lebih hemat — dan query @> yang sama tetap mengembalikan 12.500 produk. Trade-off-nya tegas: jsonb_path_ops hanya mendukung operator @>. Query atribut ? 'garansi' tidak bisa memanfaatkan index ini, sehingga bila Anda memakai ? secara rutin, jsonb_ops adalah satu-satunya pilihan.

Expression Index pada Path Tertentu #

Sekarang strategi yang paling sering dipakai di produksi: bila aplikasi Anda selalu menyaring pada satu path — filter kategori di halaman katalog, misalnya — indeks hasil ekspresinya langsung:

sql
ekspresi-index.sql
-- Pola produksi: index B-tree pada SATU path yang selalu dikueri.
-- Ekspresi di query harus sama persis dengan ekspresi di index.
CREATE INDEX idx_expr_kategori ON produk_attribut ((atribut ->> 'kategori'));

SELECT count(*) AS produk_elektronik
FROM produk_attribut
WHERE atribut ->> 'kategori' = 'elektronik';

-- Perbandingan ukuran ketiga strategi sekaligus.
SELECT 'btree ekspresi (->> kategori)' AS strategi,
       pg_size_pretty(pg_relation_size('idx_expr_kategori')) AS ukuran
UNION ALL
SELECT 'gin kolom penuh (jsonb_ops)',
       pg_size_pretty(pg_relation_size('idx_gin_attribut'))
UNION ALL
SELECT 'gin kolom penuh (jsonb_path_ops)',
       pg_size_pretty(pg_relation_size('idx_gin_path_attribut'))
ORDER BY strategi;

Output yang Dihasilkan:

text
CREATE INDEX
 produk_elektronik 
-------------------
             12500
(1 row)

             strategi             | ukuran 
----------------------------------+--------
 btree ekspresi (->> kategori)    | 360 kB
 gin kolom penuh (jsonb_ops)      | 752 kB
 gin kolom penuh (jsonb_path_ops) | 496 kB
(3 rows)

Index idx_expr_kategori dibangun di atas ekspresi (atribut ->> 'kategori') — nilai teks path tersebut, bukan dokumennya. Ukurannya hanya 360 kB, terkecil dari ketiga strategi, dan query WHERE atribut ->> 'kategori' = 'elektronik' mengembalikan 12.500 baris yang sama seperti lewat GIN. Karena nilai hasil ->> adalah text biasa, index B-tree ini juga melayani perbandingan rentang dan ORDER BY pada path tersebut — hal yang tidak bisa GIN.

Rencana Eksekusi: Planner Memilih Index yang Tepat #

Ketiga index kini hidup bersama di satu tabel. File berikut menjalankan tiga query — satu untuk tiap pola — dan memperlihatkan index mana yang dipilih planner. Output disalin manual dari eksekusi nyata karena memuat waktu yang bervariasi antar mesin; pilihan index-nya sendiri stabil:

sql
explain-analyze.sql
-- Containment @>: planner memilih index GIN jsonb_path_ops
-- karena ukurannya paling kecil di antara kandidat GIN.
EXPLAIN ANALYZE SELECT count(*) FROM produk_attribut
WHERE atribut @> '{"kategori": "elektronik"}';

-- Operator ? tidak didukung jsonb_path_ops,
-- sehingga planner memakai index GIN jsonb_ops.
EXPLAIN ANALYZE SELECT count(*) FROM produk_attribut
WHERE atribut ? 'garansi';

-- Ekspresi path tertentu dilayani index B-tree pada ekspresi itu.
EXPLAIN ANALYZE SELECT count(*) FROM produk_attribut
WHERE atribut ->> 'kategori' = 'elektronik';

Output yang Dihasilkan:

text
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1352.80..1352.81 rows=1 width=8) (actual time=6.162..6.163 rows=1 loops=1)
   ->  Bitmap Heap Scan on produk_attribut  (cost=98.12..1320.02 rows=13112 width=0) (actual time=0.856..5.621 rows=12500 loops=1)
         Recheck Cond: (atribut @> '{"kategori": "elektronik"}'::jsonb)
         Heap Blocks: exact=1058
         ->  Bitmap Index Scan on idx_gin_path_attribut  (cost=0.00..94.84 rows=13112 width=0) (actual time=0.752..0.752 rows=12500 loops=1)
               Index Cond: (atribut @> '{"kategori": "elektronik"}'::jsonb)
 Planning Time: 0.305 ms
 Execution Time: 6.217 ms
(8 rows)

                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1173.42..1173.43 rows=1 width=8) (actual time=2.002..2.003 rows=1 loops=1)
   ->  Bitmap Heap Scan on produk_attribut  (cost=42.45..1161.26 rows=4865 width=0) (actual time=0.414..1.780 rows=5000 loops=1)
         Recheck Cond: (atribut ? 'garansi'::text)
         Heap Blocks: exact=1058
         ->  Bitmap Index Scan on idx_gin_attribut  (cost=0.00..41.23 rows=4865 width=0) (actual time=0.314..0.314 rows=5000 loops=1)
               Index Cond: (atribut ? 'garansi'::text)
 Planning Time: 0.059 ms
 Execution Time: 2.035 ms
(8 rows)

                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=597.39..597.40 rows=1 width=8) (actual time=2.458..2.459 rows=1 loops=1)
   ->  Bitmap Heap Scan on produk_attribut  (cost=6.23..596.77 rows=250 width=0) (actual time=0.395..1.913 rows=12500 loops=1)
         Recheck Cond: ((atribut ->> 'kategori'::text) = 'elektronik'::text)
         Heap Blocks: exact=1058
         ->  Bitmap Index Scan on idx_expr_kategori  (cost=0.00..6.17 rows=250 width=0) (actual time=0.298..0.299 rows=12500 loops=1)
               Index Cond: ((atribut ->> 'kategori'::text) = 'elektronik'::text)
 Planning Time: 0.052 ms
 Execution Time: 2.495 ms
(8 rows)

Tiga pengamatan dari rencana tersebut:

@> memilih idx_gin_path_attribut: untuk containment, planner membandingkan kedua index GIN dan memilih jsonb_path_ops karena lebih kecil sehingga biayanya lebih murah.
? memilih idx_gin_attribut: satu-satunya index yang mendukung operator keanggotaan kunci — index jsonb_path_ops dan expression index tidak dilirik.
->> 'kategori' = ... memilih idx_expr_kategori: ekspresi yang cocok persis diterjemahkan menjadi Index Cond pada B-tree, jalur termurah untuk pola query tersebut.

Memilih Strategi untuk Aplikasi Nyata #

Strategi	Ukuran (50 ribu baris)	Operator yang dilayani	Fleksibilitas
GIN `jsonb_ops`	752 kB	`@>`, `?`, `?&`, `?	`
GIN `jsonb_path_ops`	496 kB	`@>` saja	path mana pun, lebih hemat storage
B-tree ekspresi `->> 'path'`	360 kB	`=`, `<`, `>`, `ORDER BY` pada path itu	satu path yang ditetapkan saat membuat index

Pola umum di aplikasi nyata: sebagian besar query menyaring pada dua-tiga path yang sama (kategori, status, kota), sedangkan query eksploratif lintas-struktur jarang terjadi. Untuk profil seperti itu, buat expression index pada path-path rutin tersebut — kecil, cepat, dan tulisannya murah — lalu tambahkan GIN kolom penuh hanya bila query analitis memang butuh menjelajah struktur bebas. Bila GIN dipilih dan ? tidak pernah dipakai, jsonb_path_ops memotong ukurannya tanpa mengorbankan @>.

Kita kini memiliki resep index untuk setiap wujud data: B-tree untuk kesetaraan dan rentang, GIN untuk nilai jamak, GiST untuk geometri dan tetangga terdekat, BRIN untuk tabel raksasa terurut, SP-GiST untuk prefix. Chapter berikutnya merangkum semuanya menjadi satu peta keputusan: diberikan sebuah query, index mana yang harus dibuat?

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- Katalog produk Toko Maju Jaya dengan atribut semi-terstruktur:
-- tiap produk membawa kombinasi kunci yang berbeda-beda.
CREATE TABLE produk_attribut (
    id      integer PRIMARY KEY,
    nama    text NOT NULL,
    atribut jsonb NOT NULL
);

INSERT INTO produk_attribut (id, nama, atribut)
SELECT g,
       'Produk ' || lpad(g::text, 5, '0'),
       jsonb_strip_nulls(jsonb_build_object(
           'kategori', (ARRAY['elektronik','pakaian','makanan','olahraga'])[1 + (g % 4)],
           'merek',   (ARRAY['Elang','Nusantara','Gunung Jaya'])[1 + (g % 3)],
           'harga',   ((g % 40) + 1) * 2500,
           'tag',     jsonb_build_array((ARRAY['diskon','baru','bestseller'])[1 + (g % 3)], 'gudang-jakarta'),
           'garansi', CASE WHEN g % 10 = 0 THEN '12 bulan' END
       ))
FROM generate_series(1, 50000) AS g;

ANALYZE produk_attribut;

-- Baris 1 dan 2 tanpa garansi; baris 10 memuat kunci garansi.
SELECT id, nama, atribut
FROM produk_attribut
WHERE id IN (1, 2, 10)
ORDER BY id;

sql
-- Pola produksi: index B-tree pada SATU path yang selalu dikueri.
-- Ekspresi di query harus sama persis dengan ekspresi di index.
CREATE INDEX idx_expr_kategori ON produk_attribut ((atribut ->> 'kategori'));

SELECT count(*) AS produk_elektronik
FROM produk_attribut
WHERE atribut ->> 'kategori' = 'elektronik';

-- Perbandingan ukuran ketiga strategi sekaligus.
SELECT 'btree ekspresi (->> kategori)' AS strategi,
       pg_size_pretty(pg_relation_size('idx_expr_kategori')) AS ukuran
UNION ALL
SELECT 'gin kolom penuh (jsonb_ops)',
       pg_size_pretty(pg_relation_size('idx_gin_attribut'))
UNION ALL
SELECT 'gin kolom penuh (jsonb_path_ops)',
       pg_size_pretty(pg_relation_size('idx_gin_path_attribut'))
ORDER BY strategi;

sql
-- Containment @>: planner memilih index GIN jsonb_path_ops
-- karena ukurannya paling kecil di antara kandidat GIN.
EXPLAIN ANALYZE SELECT count(*) FROM produk_attribut
WHERE atribut @> '{"kategori": "elektronik"}';

-- Operator ? tidak didukung jsonb_path_ops,
-- sehingga planner memakai index GIN jsonb_ops.
EXPLAIN ANALYZE SELECT count(*) FROM produk_attribut
WHERE atribut ? 'garansi';

-- Ekspresi path tertentu dilayani index B-tree pada ekspresi itu.
EXPLAIN ANALYZE SELECT count(*) FROM produk_attribut
WHERE atribut ->> 'kategori' = 'elektronik';

sql
-- GIN pada seluruh kolom jsonb: kelas operator bawaan jsonb_ops
-- mendukung @>, ?, ?&, dan ?|.
CREATE INDEX idx_gin_attribut ON produk_attribut USING gin (atribut);

-- Containment: dokumen memuat pasangan kategori tertentu.
SELECT count(*) AS produk_elektronik
FROM produk_attribut
WHERE atribut @> '{"kategori": "elektronik"}';

-- Keanggotaan kunci: dokumen yang memiliki kunci "garansi".
SELECT count(*) AS produk_bergaransi
FROM produk_attribut
WHERE atribut ? 'garansi';

-- Containment array: dokumen yang tag-nya memuat "diskon".
SELECT count(*) AS produk_diskon
FROM produk_attribut
WHERE atribut @> '{"tag": ["diskon"]}';

SELECT pg_size_pretty(pg_relation_size('idx_gin_attribut')) AS ukuran_gin_kolom;

sql
-- Kelas operator jsonb_path_ops: index lebih kecil,
-- tetapi hanya operator @> yang bisa dilayani.
CREATE INDEX idx_gin_path_attribut ON produk_attribut
    USING gin (atribut jsonb_path_ops);

SELECT pg_size_pretty(pg_relation_size('idx_gin_attribut')) AS ukuran_jsonb_ops,
       pg_size_pretty(pg_relation_size('idx_gin_path_attribut')) AS ukuran_jsonb_path_ops;

-- Query @> yang sama tetap berjalan dan hasilnya tidak berubah.
SELECT count(*) AS produk_elektronik
FROM produk_attribut
WHERE atribut @> '{"kategori": "elektronik"}';