Indexing

Making Use of Indexes – Poin-Poin Penting

  1. Pentingnya Indexing

    • Indexing adalah inti dari performance database.

    • Berdasarkan pengalaman lebih dari 25 tahun dalam PostgreSQL consulting dan PostgreSQL 24/7 support, penyebab utama bad performance adalah bad indexing.

    • Pengaturan memory parameter penting, tetapi semua itu sia-sia jika index tidak digunakan dengan benar.

    • Tidak ada pengganti untuk missing index.

  2. Rekomendasi Praktis

    • Jika performance buruk, hal pertama yang harus dicek adalah indexing, khususnya apakah ada index yang hilang (missing indexes).

    • Tidak mungkin mencapai good performance tanpa indexing yang tepat.

  3. Kesalahpahaman Umum

    • Contoh skenario beban query:

      • 1000 queries x 1 ms = 1 detik

      • 1 query x 1000 ms = 1 detik

    • Hasilnya sama-sama 1 detik total, tetapi 1 query lambat saja bisa menyebabkan 50% load.

    • Jika query lambat itu dihilangkan (misalnya dengan indexing tepat), performa bisa meningkat 2x lipat.

  4. Alasan Fokus pada Indexing

    • Satu bab penuh didedikasikan untuk membahas indexing karena dampaknya yang sangat besar.

    • Tujuannya memberi banyak insight tentang bagaimana indexing bekerja dan bisa dimanfaatkan di PostgreSQL.

  5. Topik yang Dibahas dalam Chapter Ini

    • Understanding simple queries and the cost model

    • Improving speed using clustered tables

    • Understanding additional B-tree features

    • Introducing operator classes

    • Understanding PostgreSQL index types

    • Achieving better answers with fuzzy searching

    • Understanding full-text searches

  6. Outcome yang Diharapkan

    • Setelah mempelajari chapter ini, pembaca akan memahami bagaimana index bisa digunakan secara optimal di PostgreSQL untuk mendapatkan performance yang jauh lebih baik.

Understanding simple queries and the cost model

  1. Persiapan Data Uji

    • Buat tabel t_test dengan 2 kolom:

      • id → auto-increment (serial)

      • name → text (berisi nilai statis)

    • Isi data dengan fungsi generate_series:

      • 2,000,000 baris dengan nilai hans

      • 2,000,000 baris dengan nilai paul

    • Total data = 4 juta rows.

    DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (id serial, name text);

INSERT INTO t_test (name)
SELECT 'hans' FROM generate_series(1, 2000000);

INSERT INTO t_test (name)
SELECT 'paul' FROM generate_series(1, 2000000);

    • Verifikasi jumlah data:

      SELECT name, count(*) FROM t_test GROUP BY 1;

      Hasil:

      • hans → 2,000,000

      • paul → 2,000,000

  2. Karakteristik Data

    • Total 4 juta rows.

    • id selalu berurutan naik (ascending order).

    • name hanya punya 2 nilai unik (distinct values).

    → Dataset ini sederhana tapi cocok untuk eksperimen indexing sepanjang bab.

  3. Menjalankan Query Sederhana

    • Contoh query:

      \timing
SELECT * FROM t_test WHERE id = 432332;

    • Output hasil:

      id    | name
432332 | hans
(1 row)
Time: 87.967 ms

  4. Peran \timing

    • Perintah \timing di psql → menampilkan runtime query.

    • Penting dicatat: ini bukan waktu eksekusi real di server, melainkan waktu yang diukur dari sisi psql client.

    • Untuk query yang sangat singkat, network latency bisa memberi pengaruh besar terhadap total waktu.

➡️ Bagian ini menekankan dasar eksperimen indexing:

  • Kita siapkan data besar (4 juta rows).

  • Jalankan query sederhana dengan filter WHERE id = ....

  • Mulai memahami cost model PostgreSQL dengan melihat waktu query (walaupun belum pure server-side).

Making use of EXPLAIN

  1. Permasalahan Awal

    • Membaca 4 juta rows dengan query sederhana memakan waktu ±100 ms.

    • Dari sisi performance, ini dianggap buruk (total disaster).

    • Dibutuhkan alat untuk memahami apa yang salah.

  2. EXPLAIN Command

    • Fungsi: menampilkan execution plan dari sebuah statement SQL.

    • Sintaks umum:

      EXPLAIN [ ( option [, ...] ) ] statement

    • Options yang bisa digunakan (boolean / pilihan):

      • ANALYZE

      • VERBOSE

      • COSTS

      • SETTINGS

      • GENERIC_PLAN

      • BUFFERS

      • SERIALIZE (NONE | TEXT | BINARY)

      • WAL

      • TIMING

      • SUMMARY

      • MEMORY

      • FORMAT (TEXT | XML | JSON | YAML)

    • Dokumentasi resmi: PostgreSQL EXPLAIN.

    ➝ Jika ada query terasa lambat, gunakan EXPLAIN untuk menemukan akar masalahnya.

  3. Contoh Penggunaan

    EXPLAIN SELECT * FROM t_test WHERE id = 432332;

    Hasil (ringkas):

    Gather (cost=1000.00..43455.43 rows=1 width=9)
  Workers Planned: 2
  -> Parallel Seq Scan on t_test
       (cost=0.00..42455.33 rows=1 width=9)
       Filter: (id = 432332)

  4. Empat Tahap Eksekusi Query di PostgreSQL

    • Parser → cek syntax error & masalah dasar.

    • Rewrite system → tangani rules (misalnya views).

    • Optimizer / Planner → hitung cara paling efisien untuk menjalankan query, buat plan.

    • Executor → jalankan plan, hasilkan output.

    ➝ EXPLAIN memperlihatkan hasil yang dibuat oleh planner (optimizer).

  5. Interpretasi Hasil Contoh

    • PostgreSQL menggunakan parallel sequential scan.

    • 2 workers dipakai untuk mengevaluasi kondisi id = 432332.

    • Hasil parsial digabungkan melalui gather node (fitur sejak PostgreSQL 9.6 dalam parallel query infrastructure).

    • rows=1 artinya PostgreSQL memperkirakan hanya 1 row yang dihasilkan dari filter ini.

  6. Parallel Workers

    • Jumlah parallel workers tergantung ukuran tabel.

    • Tabel besar → PostgreSQL otomatis menggunakan lebih banyak parallel workers.

    • Tabel kecil → parallelism tidak dipakai karena overhead justru lebih besar daripada manfaat.

  7. Mengatur Parallelism

    • Bisa mengurangi jumlah workers (misalnya menonaktifkan parallelism) dengan:

      SET max_parallel_workers_per_gather TO 0;

    • Catatan:

      • Setting ini hanya berlaku di session saat ini.

      • Bisa juga diubah di postgresql.conf, tetapi tidak disarankan karena akan menghilangkan optimisasi parallel query yang biasanya meningkatkan performa.

➡️ Bagian ini menjelaskan bahwa EXPLAIN adalah pintu masuk utama untuk menganalisis query plan, dan contoh pertama memperlihatkan bahwa query WHERE id = ... tanpa index menyebabkan sequential scan (meskipun dipercepat dengan parallelism).

Digging into the PostgreSQL cost model

  1. Contoh Execution Plan tanpa Parallelism (1 CPU)

    EXPLAIN SELECT * FROM t_test WHERE id = 432332;

    Hasil:

    QUERY PLAN
----------------------------------------------------------
Seq Scan on t_test (cost=0.00..71622.00 rows=1 width=9)
  Filter: (id = 432332)
(2 rows)

    • PostgreSQL melakukan sequential scan → baca seluruh tabel baris demi baris lalu terapkan filter.

    • Estimasi cost = 71622 penalty points.

  2. Apa itu Cost (Penalty Points)?

    • Cost adalah konsep abstrak yang digunakan PostgreSQL untuk membandingkan berbagai cara eksekusi query.

    • Planner memilih execution plan dengan cost terendah.

    • Cost bukan waktu real (tidak bisa diterjemahkan langsung ke ms).

  3. Menghitung Cost

    • Hitung jumlah blok tabel:

      SELECT pg_relation_size('t_test') / 8192.0;

      Hasil:

      ?column?
--------------------
21622.000000
(1 row)

      ➝ Tabel t_test terdiri dari 21622 blok (1 blok = 8192 bytes).

    • Setiap blok dibaca sequential → cost = 1 per blok.

      SHOW seq_page_cost;
      seq_page_cost
---------------
1
(1 row)

    • Selain membaca blok, ada cost untuk CPU:

      SHOW cpu_tuple_cost;
      cpu_tuple_cost
----------------
0.01
(1 row)
      SHOW cpu_operator_cost;
      cpu_operator_cost
-------------------
0.0025
(1 row)

    • Perhitungan total:

      SELECT 21622*1 + 4000000*0.01 + 4000000*0.0025;

      Hasil:

      ?column?
------------
71622.0000
(1 row)

      ➝ Cocok dengan angka di EXPLAIN plan.

    • Jadi cost terdiri dari:

      • I/O cost (membaca blok)

      • CPU cost (memproses tuple dan operator) → dijadikan satu angka abstrak.

  4. Parameter Lain dalam Cost Model

    • Index-related cost parameters:

      • random_page_cost = 4

        • Jika PostgreSQL menggunakan index, akan ada banyak random I/O.

        • Pada spinning disks (HDD), random read jauh lebih mahal daripada sequential.

        • Pada SSD / NVMe, perbedaan random vs sequential hampir hilang → bisa diatur ke 1.

      • cpu_index_tuple_cost = 0.005

        • CPU cost saat menggunakan index.

        • Default 0.005, biasanya tidak perlu diubah.

    • Parallel query cost parameters:

      • parallel_tuple_cost = 0.1

        • Cost untuk mentransfer satu tuple dari worker ke proses lain.

        • Default = 0.1 → jarang diubah.

      • parallel_setup_cost = 1000.0

        • Cost untuk memulai parallel worker.

        • Karena membuat proses tambahan itu ada overhead, parameter ini memodelkan biaya itu.

      • min_parallel_table_scan_size = 8MB

        • Ukuran minimum tabel agar dipertimbangkan untuk parallel scan.

        • Semakin besar tabel, semakin banyak CPU digunakan.

        • Tabel harus 3x lebih besar agar PostgreSQL menambah 1 worker baru.

      • min_parallel_index_scan_size = 512kB

        • Ukuran minimum index agar dipertimbangkan untuk parallel index scan.

📌 Kesimpulan Bagian Ini:

  • PostgreSQL planner menghitung cost query berdasarkan I/O + CPU.

  • Angka cost tidak mewakili waktu eksekusi real (ms), hanya untuk perbandingan antar strategi eksekusi.

  • Parameter seperti seq_page_cost, cpu_tuple_cost, cpu_operator_cost, random_page_cost, hingga parallel-related cost (parallel_tuple_cost, dll.) mempengaruhi cara PostgreSQL memilih execution plan.

Deploying simple indexes

  1. Masalah Sequential Scan

    • Menambah parallel workers untuk scan tabel besar memang membantu, tapi membaca seluruh tabel hanya untuk menemukan 1 row tetap tidak efisien.

    • Solusi: gunakan index.

  2. Membuat Index Sederhana

    CREATE INDEX idx_id ON t_test (id);

    • Index idx_id dibuat pada kolom id.

    • Coba query ulang:

      SELECT * FROM t_test WHERE id = 43242;

      Output:

      id    | name
-------+------
43242 | hans
(1 row)

Time: 0.259 ms

    🔑 Hasilnya: dari ±100 ms (sequential scan) → turun drastis jadi 0.259 ms dengan index.

  3. Tipe Index Default PostgreSQL

    • PostgreSQL menggunakan Lehman-Yao’s high-concurrency B-tree untuk index standar. 📖 Paper asli Lehman-Yao B-tree

    • PostgreSQL menambahkan berbagai optimisasi spesifik.

    • Kelebihan utama Lehman-Yao B-tree:

      • Mendukung high concurrency → banyak operasi read dan write bisa berjalan bersamaan pada index yang sama.

      • Hasil: throughput meningkat drastis.

  4. Biaya Index (Tidak Gratis)

    • Index memakan ruang disk dan membuat write lebih lambat, karena index harus selalu sinkron dengan tabel.

    • Contoh cek ukuran index:

      \x        -- aktifkan expanded display
\di+ idx_id

      Hasil:

      List of relations
-[ RECORD 1 ]-+----------
Schema        | public
Name          | idx_id
Type          | index
Owner         | hs
Table         | t_test
Persistence   | permanent
Access method | btree
Size          | 86 MB
Description   |

    • Index idx_id dengan 4 juta rows = 86 MB di disk.

    • Setiap INSERT, UPDATE, DELETE harus update index juga. ➝ Jika ada 20 indexes, maka setiap INSERT harus tulis ke 20 index → proses jadi jauh lebih lambat.

  5. Parallel Index Creation

    • Sejak PostgreSQL 11 → mendukung parallel index creation untuk B-tree indexes.

    • Sejak PostgreSQL 17 → juga mendukung parallel build untuk BRIN indexes.

    • Artinya: PostgreSQL bisa pakai banyak CPU core untuk membangun index, membuat proses jauh lebih cepat.

    • Saat ini, parallel build hanya berlaku untuk B-tree dan BRIN → belum untuk index tipe lain.

    • Parameter kontrol:

      max_parallel_maintenance_workers

      • Menentukan berapa banyak proses yang boleh digunakan sebagai batas atas saat membangun index.

📌 Kesimpulan Bagian Ini:

  • Index membuat query super cepat (100 ms → 0.259 ms).

  • PostgreSQL default = B-tree index dengan Lehman-Yao high-concurrency.

  • Index punya trade-off:

    • + Membuat SELECT jauh lebih cepat.

    • – Membutuhkan ruang disk & memperlambat write.

  • Versi modern PostgreSQL mendukung parallel index build untuk mempercepat proses pembuatan index di tabel besar.

Making use of sorted output

Seperti yang sudah ditunjukkan, B-tree index tidak hanya berguna untuk menemukan baris tertentu, tetapi juga bisa digunakan untuk menyediakan data yang sudah tersortir ke tahap query berikutnya.

🔹 Contoh 1: ORDER BY + LIMIT

EXPLAIN SELECT *
FROM t_test
ORDER BY id DESC
LIMIT 10;

Hasil query plan:

QUERY PLAN
---------------------------------------------------------
Limit  (cost=0.43..0.74 rows=10 width=9)
  ->  Index Scan Backward using idx_id on t_test
        (cost=0.43..125505.43 rows=4000000 width=9)

👉 PostgreSQL cukup membaca 10 baris terakhir dari index (karena index idx_id sudah tersortir ASC secara default, maka untuk DESC cukup dibaca mundur). ➡️ Tidak ada full sort di seluruh tabel → sangat cepat!

✅ Praktisnya, kita bisa dapatkan Top-N query (misalnya "10 data terbaru") dalam <1 ms, bahkan di tabel dengan jutaan rows.

🔹 Contoh 2: MIN() dan MAX()

Fungsi min dan max juga sangat terbantu dengan index.

EXPLAIN SELECT min(id), max(id) FROM t_test;

Hasil query plan:

QUERY PLAN
--------------------------------------------------------
Result  (cost=0.91..0.92 rows=1 width=8)
  InitPlan 1
    ->  Limit  (cost=0.43..0.46 rows=1 width=4)
          ->  Index Only Scan using idx_id on t_test
  InitPlan 2
    ->  Limit  (cost=0.43..0.46 rows=1 width=4)
          ->  Index Only Scan Backward using idx_id on t_test t_test_1

➡️ PostgreSQL cukup melakukan Index Only Scan sekali ke awal dan sekali ke akhir index:

  • Awal index → dapatkan min(id)

  • Akhir index → dapatkan max(id)

Tidak perlu baca semua data di tabel (t_test), hanya cukup baca metadata index.

🔑 Insight penting

  1. B-tree index = sorted structure

    • Bisa dibaca dari awal (ASC) maupun dari akhir (DESC).

    • Cocok untuk query yang membutuhkan data terurut.

  2. Top-N query optimization

    • ORDER BY ... LIMIT N akan sangat cepat dengan index yang sesuai.

    • Biasanya dipakai untuk "recent data", "latest log", "top ranking".

  3. Aggregate optimization (min/max)

    • Tidak perlu scan seluruh tabel.

    • Hanya perlu baca ujung index.

  4. Index Only Scan

    • Kalau semua kolom yang dibutuhkan ada di index (atau bisa diambil dari index tanpa lihat tabel), maka query bisa dijawab tanpa menyentuh tabel utama.

    • Ini mempercepat query lebih jauh.

📘 Jadi, indeks B-tree bukan cuma soal WHERE filtering, tapi juga ORDER BY, LIMIT, MIN/MAX, dan aggregate tertentu. Nanti ada pembahasan lebih lanjut tentang bagaimana PostgreSQL memilih index untuk grouping, distinct, join, dll.

Using more than one index at a time

Sejauh ini, kita sudah lihat bahwa query biasanya menggunakan satu index pada satu waktu. Namun, di dunia nyata sering kali query membutuhkan lebih dari satu kondisi, sehingga PostgreSQL bisa menggunakan lebih dari satu index dalam satu query.

🔹 Contoh: OR condition

EXPLAIN SELECT * FROM t_test WHERE id = 30 OR id = 50;

Query Plan:

Bitmap Heap Scan on t_test  (cost=8.88..16.85 rows=2 width=9)
  Recheck Cond: ((id = 30) OR (id = 50))
  ->  BitmapOr  (cost=8.88..8.88 rows=2 width=0)
        ->  Bitmap Index Scan on idx_id  (cost=0.00..4.44 rows=1 width=0)
              Index Cond: (id = 30)
        ->  Bitmap Index Scan on idx_id  (cost=0.00..4.44 rows=1 width=0)
              Index Cond: (id = 50)

👉 Hal penting di sini:

  • PostgreSQL menggunakan index yang sama (idx_id) dua kali untuk dua kondisi berbeda (id = 30 dan id = 50).

  • Hasilnya digabung menggunakan BitmapOr.

🔹 Apa itu Bitmap Scan?

⚠️ Perlu dibedakan:

  • Bitmap index (Oracle) → jenis index itu sendiri.

  • Bitmap scan (PostgreSQL) → metode eksekusi query, bukan tipe index.

Cara kerjanya di PostgreSQL:

  1. Index pertama discan → menghasilkan daftar halaman (pages/blocks) yang mungkin berisi hasil.

  2. Index kedua discan → menghasilkan daftar halaman lagi.

  3. Untuk operasi OR, daftar halaman ini digabung (union).

  4. PostgreSQL kemudian membaca halaman-halaman tersebut dari tabel.

  5. Karena bisa saja ada “kelebihan” data (false positives), PostgreSQL melakukan recheck untuk memastikan baris benar-benar sesuai dengan kondisi WHERE.

🔹 Bitmap Scan untuk AND

Bitmap scan juga bisa digunakan untuk AND atau kombinasi AND + OR. Namun PostgreSQL tidak selalu memilih bitmap scan pada AND.

Contoh: Misalnya ada query mencari:

  • Semua orang dengan country = 'Austria' (8 juta baris)

  • DAN id = 12345 (hanya 1 baris)

👉 Kalau PostgreSQL scan index country dulu, hasilnya jutaan rows, padahal kita hanya butuh 1 row dari kondisi id. Lebih murah kalau langsung pakai index id.

➡️ Optimizer PostgreSQL akan menghitung biaya (cost) dari berbagai opsi, lalu memilih rute paling efisien.

🔹 Catatan penting

  • OR bisa mahal → karena harus scan lebih banyak halaman dari tabel.

  • Kadang lebih cepat diubah ke UNION:

    SELECT * FROM t_test WHERE id = 30
UNION
SELECT * FROM t_test WHERE id = 50;

    → PostgreSQL bisa eksekusi dua query kecil lalu gabungkan hasilnya, seringkali lebih efisien daripada satu query dengan OR.

🔑 Ringkasan

  1. PostgreSQL bisa menggunakan lebih dari satu index dalam satu query.

  2. Bitmap scan memungkinkan penggabungan hasil index (baik OR, AND, atau kombinasi).

  3. PostgreSQL akan selalu mempertimbangkan cost untuk memutuskan strategi terbaik.

  4. Untuk query dengan banyak OR, coba pertimbangkan alternatif seperti UNION.

Using Bitmap Scans Effectively

Bitmap scan bukan sekadar fitur tambahan — ada situasi spesifik di mana PostgreSQL memilihnya karena lebih efisien daripada Index Scan biasa.

Ada dua use case utama:

1. Menghindari fetching block yang sama berulang-ulang

Misalnya:

  • Kita mencari semua orang yang berbicara bahasa tertentu.

  • Katakanlah 10% dari semua data punya bahasa itu.

Kalau PostgreSQL pakai Index Scan biasa:

  • Index akan menunjuk ke banyak baris.

  • Tetapi baris-baris itu mungkin ada di block tabel yang sama.

  • Artinya, PostgreSQL harus baca block yang sama berkali-kali → overhead I/O lebih besar.

Dengan Bitmap Scan:

  • PostgreSQL mengumpulkan list block yang berisi data yang relevan.

  • Lalu membaca setiap block sekali saja.

  • Ini mengurangi I/O dan meningkatkan performa.

2. Menggabungkan kondisi yang relatif lemah

Misalnya query:

SELECT * 
FROM people
WHERE age BETWEEN 20 AND 30
  AND shirt_color = 'yellow';

  • Katakanlah 15% orang berusia 20–30.

  • Dan 15% orang memakai baju kuning.

  • Tetapi hasil gabungannya hanya sekitar 1% dari data total.

👉 Dalam kasus ini:

  • Sequential Scan terlalu mahal (baca semua tabel).

  • PostgreSQL bisa ambil index age dan index shirt_color, lalu gabungkan dengan bitmap.

  • Hasilnya jauh lebih sedikit, sehingga lebih murah daripada scan penuh.

3. Parallel Bitmap Heap Scan (sejak PostgreSQL 10)

  • Bitmap scan biasanya dipakai untuk query mahal → data besar, kondisi kompleks.

  • Mulai PostgreSQL 10, PostgreSQL bisa menjalankan Parallel Bitmap Heap Scan.

  • Artinya, beberapa worker bisa membaca block secara paralel berdasarkan bitmap hasil index.

  • Ini mempercepat query skala besar dengan kondisi gabungan.

🔑 Ringkasan

  • Bitmap Scan efektif ketika:

    1. Banyak baris target ada dalam block yang sama (hindari block dibaca berulang).

    2. Kondisi filter masing-masing lemah, tapi hasil gabungannya kecil.

  • PostgreSQL pintar memilih: kalau pakai Index Scan atau Seq Scan lebih murah, dia akan hindari Bitmap Scan.

  • Parallel Bitmap Heap Scan (Postgres 10+) menambah power untuk dataset besar.

Using Indexes Intelligently

Selama ini, kita melihat index seperti alat sakti yang selalu membuat query lebih cepat. Tapi kenyataannya tidak selalu begitu — ada kondisi di mana index tidak berguna atau bahkan memperlambat query.

📍 Struktur tabel untuk contoh

\d t_test

Output:

Table "public.t_test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+-------------------------
 id     | integer |           | not null | nextval('t_test_id_seq')
 name   | text    |           |          |
Indexes:
    "idx_id" btree (id)

  • Sudah ada index idx_id di kolom id.

  • Lalu kita buat index tambahan di kolom name:

CREATE INDEX idx_name ON t_test (name);

📍 Query dengan nilai yang tidak ada

EXPLAIN SELECT * FROM t_test WHERE name = 'hans2';

Output:

Index Scan using idx_name on t_test
(cost=0.43..4.45 rows=1 width=9)
Index Cond: (name = 'hans2'::text)

✅ PostgreSQL pakai Index Scan, meskipun nilai 'hans2' tidak ada di tabel. Kenapa? Karena planner tetap asumsikan ada kemungkinan ada baris (tidak akan pernah estimasi rows=0). Kalau planner mengestimasi nol, perhitungan biaya untuk node lain di execution plan jadi sulit.

📍 Query dengan nilai yang mencakup seluruh tabel

EXPLAIN SELECT *
FROM t_test
WHERE name = 'hans'
   OR name = 'paul';

Output:

Seq Scan on t_test
(cost=0.00..81622.00 rows=3000005 width=9)
Filter: ((name = 'hans'::text) OR (name = 'paul'::text))

❌ PostgreSQL abaikan index, langsung pakai Sequential Scan. Kenapa? Karena 'hans' dan 'paul' mencakup seluruh dataset. Membaca index + tabel → lebih mahal. Lebih efisien langsung baca tabel penuh sekali jalan.

📍 Query dengan nilai yang jarang muncul

EXPLAIN SELECT *
FROM t_test
WHERE name = 'hans2'
   OR name = 'paul2';

Output:

Bitmap Heap Scan on t_test (cost=8.88..12.89 rows=1 width=9)
  Recheck Cond: ((name = 'hans2'::text) OR (name = 'paul2'::text))
  -> BitmapOr (cost=8.88..8.88 rows=1 width=0)
       -> Bitmap Index Scan on idx_name
            (cost=0.00..4.44 rows=1 width=0)
            Index Cond: (name = 'hans2'::text)
       -> Bitmap Index Scan on idx_name
            (cost=0.00..4.44 rows=1 width=0)
            Index Cond: (name = 'paul2'::text)

✅ PostgreSQL pakai Bitmap Index Scan karena hasilnya diperkirakan sangat kecil → lebih hemat dibanding sequential scan.

🔑 Hal penting yang harus dipahami

  1. Execution plan tergantung data & input value PostgreSQL tidak hanya melihat query, tapi juga data distribution di tabel.

  2. Index tidak selalu digunakan Kalau kondisi WHERE mencakup hampir semua data, index dilewati → langsung Seq Scan.

  3. Planner tidak pernah estimasi nol baris Bahkan jika nilai jelas tidak ada, PostgreSQL tetap kasih estimasi minimal (misalnya rows=1).

  4. Rencana eksekusi bisa berubah Query yang sama bisa punya execution plan berbeda tergantung datanya. Inilah sebabnya runtime bisa terasa tidak konsisten di dunia nyata.

⚡ Jadi, index harus digunakan dengan bijak. Tidak semua index akan mempercepat query — dalam beberapa kasus, index justru menambah overhead.

Understanding index de-duplication (PostgreSQL 13+)

Mulai PostgreSQL 13, ada fitur baru yang sangat signifikan: Index De-duplication. Fitur ini khusus berlaku untuk B-tree indexes.

🔹 Masalah sebelum PostgreSQL 13

  • Jika kita punya tabel dengan banyak nilai yang sama (high cardinality rendah, banyak duplikat), maka index B-tree akan menyimpan entry untuk setiap row.

  • Contoh: kolom gender dengan nilai M / F.

    • Jika ada 10 juta baris, setengah M, setengah F, maka index tetap menyimpan jutaan entry dengan nilai yang sama.

  • Akibatnya:

    • Index jadi besar → misalnya ratusan MB.

    • Cache hit ratio rendah → lebih banyak I/O.

    • Query jadi lambat karena traversal lebih panjang.

🔹 Solusi PostgreSQL 13: Index Entry De-duplication

PostgreSQL sekarang bisa mengompres duplikat di index.

  • Jadi untuk banyak baris dengan nilai sama, index tidak menyimpan setiap baris, melainkan menyimpan satu entry nilai lalu daftar pointer ke block/row.

  • Hasilnya:

    • Index jadi jauh lebih kecil.

    • Lebih efisien di RAM (buffer cache).

    • Faster index scans karena traversal lebih singkat.

🔹 Contoh nyata

Misalnya:

CREATE TABLE t_test (
    id serial primary key,
    name text
);

-- Isi dengan banyak duplikat
INSERT INTO t_test (name)
SELECT CASE WHEN i % 2 = 0 THEN 'Alice' ELSE 'Bob' END
FROM generate_series(1, 5000000) i;

-- Buat index
CREATE INDEX idx_name ON t_test(name);

Sebelum PostgreSQL 13:

  • Index idx_name bisa mencapai puluhan MB karena setiap "Alice"/"Bob" direkam jutaan kali.

Sesudah PostgreSQL 13 (dengan dedup):

  • Index bisa lebih kecil drastis (misalnya 26 MB vs 86 MB seperti di contoh).

🔹 Dampak positif utama

  1. Smaller Index Size → hemat disk dan RAM.

  2. Better Cache Hit Rate → index bisa lebih sering disimpan penuh di memori.

  3. Faster Queries → lebih sedikit disk access, traversal lebih cepat.

  4. Efisiensi Maintenance → VACUUM, REINDEX, dan Index Scans lebih cepat.

🔹 Batasan

  • Hanya berlaku untuk B-tree indexes.

  • Efektif jika data punya banyak nilai yang sama (low cardinality).

  • Kalau datanya high cardinality (unik hampir semua), dedup tidak membawa banyak manfaat.

⚡ Kesimpulan: Sejak PostgreSQL 13, index dengan banyak nilai duplikat (misalnya kolom status, gender, country_code, dll) akan jauh lebih hemat dan lebih cepat, berkat index de-duplication.

Clustered Tables dan Correlation

Ketika PostgreSQL membaca data, letak fisik di disk berpengaruh besar terhadap performa. Meskipun jumlah row yang diambil sama, runtime bisa berbeda drastis tergantung apakah data tersusun rapi (clustered) atau acak (randomized).

📌 Contoh kasus:

  • Tabel t_test (ordered by id) → Data di-insert pakai SERIAL / auto-increment, sehingga baris tersusun berurutan. → Query WHERE id < 10000 bisa diambil dengan sedikit block access → hanya 85 blocks disentuh. → Waktu eksekusi: ~4 ms.

  • Tabel t_random (shuffled by ORDER BY random()) → Data diacak di disk. → Query WHERE id < 10000 harus baca 8034 blocks (!). → PostgreSQL sampai ubah rencana eksekusi → pakai Bitmap Heap Scan (lebih hemat daripada Index Scan biasa). → Waktu eksekusi: ~28 ms.

🔹 Correlation di pg_stats

PostgreSQL menyimpan informasi statistik di pg_stats, termasuk correlation untuk setiap kolom.

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('t_test', 't_random')
ORDER BY 1, 2;

📌 Hasil:

tablename
attname
correlation

t_test

id

1.0

t_test

name

1.0

t_random

id

-0.006

t_random

name

0.49

👉 Interpretasi:

  • Correlation = 1 → Data tersusun rapi (monoton meningkat).

  • Correlation mendekati 0 → Data acak.

  • Correlation negatif → Data benar-benar tidak berhubungan (shuffled).

Efek correlation ini:

  • Jika correlation tinggi (≈1) → query range scan (BETWEEN, <, >, dll) jadi cepat.

  • Jika correlation rendah → PostgreSQL harus lompat ke banyak block berbeda → performa turun.

🔹 Clustered Tables

Untuk mengatasi masalah acak ini, PostgreSQL menyediakan perintah CLUSTER:

CLUSTER t_random USING idx_random;

👉 Hasilnya:

  • PostgreSQL akan menyusun ulang baris di disk sesuai dengan urutan index (idx_random).

  • Setelah clustering, correlation akan naik mendekati 1 lagi.

  • Query range (WHERE id < 10000, BETWEEN, ORDER BY) jadi jauh lebih cepat.

🔹 Pentingnya ANALYZE

  • PostgreSQL menggunakan ANALYZE untuk meng-update statistik (pg_stats).

  • Autovacuum biasanya sudah otomatis menjalankannya.

  • Bisa juga manual:

ANALYZE t_random;

🔹 Kesimpulan

  1. Layout fisik data di disk memengaruhi kecepatan query range.

  2. Correlation di pg_stats memberi gambaran apakah kolom “berurutan” di disk atau tidak.

  3. Gunakan CLUSTER untuk menata ulang tabel berdasarkan index → meningkatkan correlation.

  4. Hati-hati: CLUSTER reorganizes seluruh tabel (bisa lama & blocking). Biasanya dipakai untuk batch maintenance, bukan rutin harian.

CLUSTER di PostgreSQL

Perintah CLUSTER digunakan untuk menyusun ulang data fisik dalam tabel sesuai urutan index tertentu. Setelah clustering, baris di disk akan tersimpan dalam urutan yang sama dengan index pilihan.

📌 Sintaks:

CLUSTER [ ( option [, ...] ) ] [ table_name [ USING index_name ] ];

Contoh:

CLUSTER t_random USING idx_random;

🔹 Hal-hal penting tentang CLUSTER

Walaupun powerful, ada beberapa hal yang harus dipertimbangkan sebelum digunakan, terutama di production:

  1. Locking 🔒

    • Selama proses CLUSTER, tabel akan di-lock eksklusif.

    • Artinya: INSERT/UPDATE/DELETE tidak bisa dilakukan hingga proses selesai.

    • Jadi, di production harus dijalankan saat maintenance window atau saat sistem idle.

  2. Hanya 1 index 📌

    • CLUSTER hanya bisa menggunakan satu index untuk mengatur urutan data.

    • Jadi, kamu harus memilih index yang paling sering dipakai.

    • Tidak bisa sekaligus urut berdasarkan id, name, created_at misalnya.

  3. Efeknya relatif terhadap workload ⚡

    • Seperti contoh di bab sebelumnya, CLUSTER bisa membuat query range jadi jauh lebih cepat.

    • Tapi kalau workload lebih banyak random access / OLTP, efeknya mungkin kecil.

    • Efektivitas juga dipengaruhi cache hit rate & pola query.

  4. Tidak bertahan selamanya 🕒

    • Setelah clustering, data memang rapi.

    • Tapi begitu ada INSERT/UPDATE/DELETE, urutan fisik pelan-pelan rusak lagi (correlation turun).

    • Artinya: CLUSTER bukan proses sekali untuk selamanya, biasanya perlu dijalankan ulang berkala.

🔹 Kapan pakai CLUSTER?

  • Cocok untuk tabel besar yang sering di-scan berdasarkan range (misalnya data time-series: log, transaksi harian).

  • Tidak cocok untuk tabel dengan banyak update/insert random, karena clustering cepat rusak.

  • Biasanya dijalankan sebagai bagian dari batch maintenance (mirip VACUUM FULL atau REINDEX).

🔹 Perbandingan CLUSTER vs lainnya

  • VACUUM FULL → rewrite tabel juga, tapi hanya untuk menghapus bloat, tidak menjamin urutan.

  • REINDEX → hanya rebuild index, tidak mengubah urutan fisik tabel.

  • CLUSTER → rewrite tabel dengan urutan sesuai index → meningkatkan correlation.

⏩ Jadi intinya, CLUSTER bagus untuk kasus spesifik, terutama saat ada range query dominan. Tapi harus dipakai hati-hati karena locking & maintenance overhead.

Index Scan vs Index-Only Scan

Sejauh ini kita sudah melihat Index Scan biasa. Tapi PostgreSQL bisa lebih pintar dengan Index-Only Scan.

Contoh perbedaan:

1. Query ambil semua kolom (butuh akses tabel):

EXPLAIN SELECT * FROM t_test WHERE id = 34234;

Hasil:

Index Scan using idx_id on t_test
(cost=0.43..8.45 rows=1 width=9)
Index Cond: (id = 34234)

👉 PostgreSQL pakai index untuk mencari row, tapi tetap akses tabel (heap) karena butuh baca semua kolom.

2. Query ambil kolom yang ada di index saja:

EXPLAIN SELECT id FROM t_test WHERE id = 34234;

Hasil:

Index Only Scan using idx_id on t_test
(cost=0.43..8.45 rows=1 width=4)
Index Cond: (id = 34234)

👉 PostgreSQL bisa langsung jawab query hanya dari index, tanpa perlu baca tabel. Ini disebut Index-Only Scan → jauh lebih cepat karena menghindari akses ke heap (table data).

🔹 Kapan Index-Only Scan bisa dipakai?

  1. Semua kolom yang diminta query ada di index

    • Misalnya index (id) → query hanya SELECT id bisa pakai index-only scan.

    • Kalau query SELECT id, name, maka tetap harus ke tabel (heap).

  2. Visibility map bersih (VACUUM/Autovacuum berjalan baik)

    • PostgreSQL tetap perlu tahu apakah row "valid" (tidak dihapus).

    • Kalau visibility map sudah update (misalnya habis vacuum), maka index-only scan bisa berjalan 100%.

    • Kalau tidak, PostgreSQL mungkin masih harus cek heap → menurunkan benefit.

🔹 Covering Indexes (INCLUDE)

Kadang query butuh lebih dari 1 kolom. Untuk menghindari heap lookup, PostgreSQL mendukung covering indexes (sejak PostgreSQL 11).

📌 Contoh:

CREATE INDEX idx_name_id ON t_test (name) INCLUDE (id);

  • name → kolom utama index (dipakai untuk pencarian/filtering/sorting).

  • id → kolom tambahan, tidak ikut urutan index, tapi disimpan di index agar query bisa dijawab dari index saja.

Dengan begitu query seperti:

SELECT id FROM t_test WHERE name = 'hans';

👉 Bisa dijawab dengan Index-Only Scan tanpa akses ke tabel.

🔹 Manfaat Index-Only Scan

  • ✅ Lebih cepat → karena tidak perlu baca blok tabel.

  • ✅ Lebih sedikit I/O.

  • ✅ Sangat berguna untuk query yang hanya ambil beberapa kolom yang sudah ada di index.

  • ✅ Cocok untuk workload read-heavy (misalnya laporan, dashboard).

⚡ Singkatnya: Index Scan → baca index lalu ke tabel. Index-Only Scan → cukup baca index (lebih hemat). Covering Index (INCLUDE) → cara praktis bikin index-only scan jalan di lebih banyak query.

Combined Indexes (Multi-Column Indexes)

Sebuah combined index (atau multi-column index) adalah indeks yang mencakup lebih dari satu kolom. PostgreSQL menyimpan data dalam urutan berdasarkan kolom-kolom yang didefinisikan pada indeks tersebut.

Misalnya, kita punya tabel dengan kolom:

CREATE TABLE phonebook (
    postal_code TEXT,
    last_name   TEXT,
    first_name  TEXT
);

Jika kita buat indeks seperti ini:

CREATE INDEX idx_phonebook
    ON phonebook (postal_code, last_name, first_name);

Maka data di indeks tersebut akan diurutkan berdasarkan:

  1. postal_code terlebih dahulu,

  2. lalu di dalam setiap postal_code diurutkan berdasarkan last_name,

  3. dan di dalam kombinasi postal_code + last_name, diurutkan lagi berdasarkan first_name.

Kapan indeks gabungan bisa digunakan?

PostgreSQL hanya bisa menggunakan bagian prefix kiri dari indeks gabungan secara efisien. Artinya:

  • Query dengan WHERE postal_code = '12345' → bisa pakai indeks.

  • Query dengan WHERE postal_code = '12345' AND last_name = 'Doe' → bisa pakai indeks.

  • Query dengan WHERE postal_code = '12345' AND last_name = 'Doe' AND first_name = 'John' → full usage dari indeks.

  • Query dengan WHERE last_name = 'Doe' saja → tidak bisa langsung pakai indeks ini, karena postal_code adalah kolom pertama di indeks.

  • Query dengan WHERE last_name = 'Doe' AND first_name = 'John' → juga tidak bisa langsung pakai, karena postal_code dilewati. Biasanya PostgreSQL akan jatuh ke bitmap scan atau full scan.

Contoh penggunaan

EXPLAIN SELECT * FROM phonebook
WHERE postal_code = '12345'
  AND last_name = 'Doe';

Akan menggunakan Index Scan dengan baik, karena query memanfaatkan prefix postal_code lalu last_name.

Tapi:

EXPLAIN SELECT * FROM phonebook
WHERE last_name = 'Doe';

Biasanya tidak bisa memanfaatkan indeks (postal_code, last_name, first_name) secara langsung. PostgreSQL mungkin akan tetap mempertimbangkan bitmap index scan (menggabungkan beberapa indeks jika tersedia), tapi performanya lebih buruk dibanding indeks yang memang dibuat khusus untuk last_name.

Best Practice untuk Combined Indexes

  1. Urutan kolom penting

    • Kolom dengan selectivity tinggi (membatasi hasil query lebih spesifik) biasanya ditempatkan di awal.

    • Contoh: postal_code punya ribuan kemungkinan, tapi gender cuma ada 2 (M/F). Lebih baik taruh postal_code dulu.

  2. Gunakan secara selektif Jangan buat semua kombinasi kolom jadi indeks, karena akan boros storage & memperlambat operasi INSERT/UPDATE/DELETE.

  3. Pertimbangkan kebutuhan query Lihat pola query yang sering digunakan, lalu buat indeks gabungan sesuai kebutuhan.

📌 Jadi ringkasannya:

  • Indeks gabungan (multi-column) berguna kalau query sering memakai kombinasi kolom dengan urutan tertentu.

  • PostgreSQL hanya bisa memanfaatkan prefix kiri dari indeks secara optimal.

  • Jika pola query sangat beragam, kadang lebih baik menggunakan indeks terpisah + biarkan PostgreSQL menggunakan bitmap scan.

1. Functional Indexes

Biasanya kita membuat indeks langsung pada kolom. Namun, PostgreSQL juga mendukung indeks fungsional, yaitu indeks yang menyimpan hasil dari suatu ekspresi atau fungsi terhadap kolom.

Contoh:

CREATE INDEX idx_cos ON t_random (cos(id));
ANALYZE;

Sekarang query seperti ini:

EXPLAIN SELECT * FROM t_random WHERE cos(id) = 10;

Akan menggunakan Index Scan pada idx_cos.

Syarat penting: fungsi yang dipakai harus immutable → hasilnya tidak berubah untuk input yang sama.

  • ✅ cos(x), lower(text) → aman.

  • ❌ age(timestamp), random() → tidak bisa, karena hasilnya berubah tergantung waktu/kondisi.

👉 Functional indexes sangat berguna untuk query pencarian case-insensitive (LOWER(name)), pencarian substring (substr(field,1,3)), atau normalisasi data sebelum pencarian.

2. Space Consumption of Indexes

Indeks itu tidak gratis. Untuk setiap baris, PostgreSQL harus menyimpan pointer + nilai indeks.

Contoh kasus di buku:

  • Tabel total data: 338 MB

  • Indeks total: 284 MB

Artinya, indeks bisa hampir sebesar (atau bahkan lebih besar) dari tabel itu sendiri.

Masalahnya:

  • Over-indexing → membuat DB membengkak, memperlambat INSERT, UPDATE, DELETE karena indeks juga harus di-maintain.

  • Kadang 50%+ ukuran DB adalah indeks, dan sering ada indeks yang tidak pernah dipakai.

👉 Untuk menganalisis:

\di+         -- lihat ukuran indeks

Atau dengan query:

SELECT relname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE schemaname = 'public';

3. Partial Indexes

Jika data punya distribusi yang sangat tidak seimbang, kita bisa bikin partial index → hanya untuk subset data tertentu.

Contoh:

CREATE INDEX idx_name ON t_test (name)
WHERE name NOT IN ('hans', 'paul');

Artinya indeks hanya mencakup baris di mana name ≠ 'hans' AND name ≠ 'paul'. Jika 80% data = "hans" atau "paul", maka indeks jadi sangat kecil → lebih hemat storage & lebih cepat.

Ukuran indeks bisa turun drastis:

  • Full index: puluhan MB

  • Partial index: hanya beberapa KB

👉 Cocok untuk kolom dengan nilai dominan, misalnya:

  • gender (jika mayoritas male/female)

  • status (jika mayoritas "active")

  • nationality (jika mayoritas warga negara sama)

⚠️ Syarat: PostgreSQL hanya bisa gunakan partial index jika kondisi query persis sama dengan definisi indeks.

🔑 Ringkasan

  • Functional Index: mempercepat pencarian berbasis fungsi (harus immutable).

  • Indeks mahal dalam storage → bisa lebih besar dari tabel.

  • Partial Index: solusi hemat ruang & lebih cepat jika mayoritas data punya nilai dominan yang tidak perlu diindeks.

Adding Data While Indexing

Membuat indeks memang mudah, tapi ada konsekuensi locking yang perlu dipahami.

1. Normal CREATE INDEX

  • Saat kita menjalankan:

    CREATE INDEX idx_name2 ON t_test (name);

  • PostgreSQL akan memberikan SHARE lock pada tabel.

  • Artinya:

    • SELECT masih bisa berjalan,

    • tapi INSERT, UPDATE, dan DELETE akan diblokir sampai indeks selesai dibuat.

  • Ini tidak masalah untuk tabel kecil, tapi untuk tabel besar (misalnya 1 TB) → bisa jadi downtime panjang di sistem produksi.

2. CREATE INDEX CONCURRENTLY

Untuk mengatasi masalah itu, PostgreSQL menyediakan mode concurrent:

CREATE INDEX CONCURRENTLY idx_name2 ON t_test (name);

Perbedaan penting:

  • Tabel tetap bisa digunakan normal (dapat SELECT, INSERT, UPDATE, DELETE) selama proses pembuatan indeks.

  • Proses pembuatan indeks berlangsung lebih lama (bisa 2x lipat atau lebih).

  • Karena berjalan paralel dengan transaksi lain, ada risiko gagal → indeks bisa ditandai sebagai invalid.

3. Mengecek Index Invalid

Kalau indeks gagal dibuat dengan CONCURRENTLY, PostgreSQL akan tetap mencatatnya, tapi dengan status invalid.

Cara cek:

\d t_test

Indeks yang invalid akan muncul dengan tanda seperti:

"idx_name2" btree (name) INVALID

Atau dengan query:

SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index
WHERE indrelid = 't_test'::regclass;

4. Menghapus Index Invalid

Jika ketemu indeks invalid, hapus lalu buat ulang:

DROP INDEX CONCURRENTLY idx_name2;
CREATE INDEX CONCURRENTLY idx_name2 ON t_test (name);

⚖️ Trade-offs

  • CREATE INDEX

    • ✅ Lebih cepat

    • ❌ Lock menulis (tidak cocok untuk tabel besar di production)

  • CREATE INDEX CONCURRENTLY

    • ✅ Non-blocking, tetap bisa read/write

    • ❌ Lebih lama, risiko gagal (invalid index)

👉 Jadi, untuk production dengan tabel besar, selalu gunakan CONCURRENTLY. Untuk dev/testing atau tabel kecil, cukup pakai CREATE INDEX biasa.

Understanding PostgreSQL Index Types

Sejauh ini kita sudah banyak membahas B-tree, tapi PostgreSQL menyediakan beberapa jenis indeks lain, masing-masing dengan tujuan dan keunggulannya sendiri. Tidak semua tipe data cocok dengan B-tree, terutama jika data tidak bisa diurutkan secara alami (misalnya geometri, teks, atau range).

PostgreSQL (hingga versi 17) menyediakan 6 jenis indeks utama:

postgres=# SELECT * FROM pg_am;
 oid  |  amname |   amhandler   | amtype
------+---------+---------------+-------
 403  | btree   | bthandler     | i
 405  | hash    | hashhandler   | i
 783  | gist    | gisthandler   | i
2742  | gin     | ginhandler    | i
4000  | spgist  | spghandler    | i
3580  | brin    | brinhandler   | i

Selain itu, ada ekstensi tambahan seperti rum, vodka, cognac, dan bloom filter.

Mari kita bahas satu per satu:

1. B-tree

  • Default index di PostgreSQL.

  • Mendukung operator: <, <=, =, >=, >.

  • Cocok untuk:

    • Pencarian exact match (id = 100)

    • Pencarian range (date BETWEEN '2025-01-01' AND '2025-02-01')

    • Sorting (ORDER BY)

  • Tidak cocok untuk: full-text search, data geometri, data dengan banyak nilai duplikat.

2. Hash Indexes

  • Menyimpan hash dari nilai, bukan nilainya langsung.

  • Ideal untuk equality lookup (WHERE col = 'value').

  • Dulu (sebelum PostgreSQL 10) tidak WAL-logged, jadi tidak crash-safe → sekarang sudah aman.

  • Kekurangan:

    • Lebih besar di disk dibanding B-tree.

    • Tidak bisa dipakai untuk range query (>, <).

  • Contoh:

    CREATE INDEX idx_hash_name ON users USING hash(name);

3. GiST (Generalized Search Tree)

  • Paling fleksibel karena bisa di-customize dengan operator class.

  • Cocok untuk:

    • Range types (int4range, daterange, dll.)

    • Fuzzy searching (misalnya similarity search)

    • Geometri (PostGIS → ST_Contains, ST_Within, dll.)

  • Bisa mendukung operator: overlap, contains, left-of, right-of, dsb.

  • Contoh:

    CREATE INDEX idx_range ON sales USING gist(sale_period);
CREATE INDEX idx_geom ON locations USING gist(geom);

4. GIN (Generalized Inverted Index)

  • Dirancang untuk full-text search dan array.

  • Menyimpan inverted map: setiap key disimpan sekali → punya daftar dokumen (posting list) tempat key muncul.

  • Sangat hemat ruang untuk teks besar.

  • Cocok untuk:

    • Full-text search (to_tsvector, to_tsquery)

    • JSONB (jsonb_path_ops)

    • Array columns

  • Kekurangan: tidak bisa digunakan untuk sorting.

  • Contoh:

    CREATE INDEX idx_doc_text ON documents USING gin(to_tsvector('english', content));
CREATE INDEX idx_json ON orders USING gin(details jsonb_path_ops);

5. SP-GiST (Space-Partitioned GiST)

  • Dirancang untuk struktur data hierarkis / partisi ruang.

  • Cocok untuk:

    • Quadtrees, k-d trees, radix trees (tries)

  • Biasanya dipakai untuk data in-memory atau data yang sangat terstruktur.

  • Contoh:

    CREATE INDEX idx_ip ON users USING spgist(ip inet);

6. BRIN (Block Range Index)

  • Sangat kecil dibanding B-tree, karena hanya menyimpan min/max per blok (default 128 pages ≈ 1MB).

  • Cocok untuk data berurutan / terurut (correlated), misalnya:

    • Timestamp (created_at)

    • ID autoincrement

  • Tidak efisien jika datanya acak.

  • Contoh:

    CREATE INDEX idx_brin_created ON logs USING brin(created_at);

Ukuran bisa ribuan kali lebih kecil:

B-tree index: 96 MB
BRIN index : 48 kB

7. Bloom Filters (Extension)

  • Tersedia lewat pg_trgm / bloom extension.

  • Probabilistic index: tidak pernah false negative, tapi bisa false positive.

  • Cocok untuk mengindeks banyak kolom sekaligus.

  • Contoh:

    CREATE EXTENSION bloom;
CREATE INDEX idx_bloom ON big_table USING bloom(col1, col2, col3, col4);

📌 Ringkasan Kapan Memakai Index Type

Index Type
Cocok untuk
Tidak cocok untuk

B-tree

Equality, range, sorting

Full-text, JSONB, spatial

Hash

Equality only (=)

Range queries

GiST

Range, fuzzy, spatial, PostGIS

Sorting biasa

GIN

Full-text search, JSONB, array

Range, sorting

SP-GiST

Trees (quad, k-d, radix), IP search

Generic workloads

BRIN

Large sequential data (timestamps, IDs)

Random/unordered data

Bloom

Many-column filtering

Large random workloads

⚡ Jadi, di PostgreSQL kita bisa mix index types sesuai kebutuhan query. Tidak ada “satu indeks terbaik untuk semua kasus” — justru kombinasi yang tepat bisa menyelamatkan kinerja query di production.

PreviousUnderstanding Transactions and LockingNextFuzzy Searching

Last updated