Perilaku Indeks PostgreSQL

Studi Kasus: Indeks Tunggal

Saat kamu membuat indeks pada satu kolom dan menggunakannya dalam klausa WHERE, PostgreSQL akan melakukan Index Scan untuk menemukan data, yang jauh lebih cepat daripada membaca seluruh tabel.

  • Tabel & Indeks:

    CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100),
    registered_at DATE
);

CREATE INDEX idx_users_username ON users(username);

  • Kueri & Analisis:

    EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john_doe';

  • Perilaku & Hasil yang Diharapkan:

    PostgreSQL akan melihat klausa WHERE dan menemukan bahwa ada indeks yang cocok pada kolom username. Alih-alih melakukan Seq Scan (Sequential Scan), query planner akan memilih Index Scan. Kamu akan melihat Index Scan atau Index Only Scan di hasil EXPLAIN ANALYZE.

    Index Only Scan terjadi jika semua kolom yang diminta dalam SELECT (dalam hal ini *) juga ada di indeks. Untuk indeks tunggal ini, ini tidak akan terjadi, tapi kamu pasti akan melihat Index Scan.

    Contoh hasil EXPLAIN ANALYZE:

    Index Scan using idx_users_username on users  (cost=0.29..8.30 rows=1 width=122) (actual time=0.010..0.011 rows=1 loops=1)
...

    Perhatikan kata Index Scan yang menunjukkan bahwa indeks telah digunakan.

Studi Kasus: Indeks Majemuk (Composite Index)

Ketika kamu sering memfilter data menggunakan kombinasi kolom (misalnya, mencari pengguna yang terdaftar pada tanggal tertentu dengan nama tertentu), membuat indeks majemuk adalah cara paling efisien.

  • Tabel & Indeks:

    CREATE INDEX idx_users_username_registered ON users(username, registered_at);

  • Kueri & Analisis:

    • Kueri 1 (menggunakan dua kolom indeks):

      EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'jane_doe' AND registered_at >= '2025-01-01';

      Perilaku: PostgreSQL akan melakukan Index Scan yang sangat efisien karena kedua kolom ada di indeks. Ini adalah skenario yang paling optimal.

    • Kueri 2 (menggunakan kolom pertama saja):

      EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john_doe';

      Perilaku: Indeks majemuk masih bisa digunakan untuk mencari berdasarkan kolom pertamanya (username). Index Scan akan tetap terjadi, menunjukkan bahwa indeks tersebut dapat digunakan secara parsial.

    • Kueri 3 (menggunakan kolom kedua saja):

      EXPLAIN ANALYZE SELECT * FROM users WHERE registered_at = '2025-02-15';

      Perilaku: Indeks ini TIDAK akan digunakan. Query planner tidak dapat menggunakan indeks majemuk jika hanya kolom kedua (atau kolom di tengah) yang digunakan dalam klausa WHERE. Ia akan beralih ke Seq Scan karena tidak ada cara lain yang lebih cepat.

Studi Kasus: Multiple Indeks Terpisah

Skenario ini terjadi ketika kamu membuat indeks terpisah pada setiap kolom yang akan digunakan dalam klausa WHERE.

  • Tabel & Indeks:

    -- Hapus indeks sebelumnya jika ada
DROP INDEX IF EXISTS idx_users_username;
DROP INDEX IF EXISTS idx_users_username_registered;

CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_registered ON users(registered_at);

  • Kueri & Analisis:

    EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'jane_doe' AND registered_at >= '2025-01-01';

  • Perilaku & Hasil yang Diharapkan:

    PostgreSQL, melalui query planner-nya, akan melihat dua indeks yang potensial. Ia bisa:

    1. Menggunakan Index Scan pada idx_users_username, lalu memfilter hasilnya berdasarkan registered_at.

    2. Menggunakan Index Scan pada idx_users_registered, lalu memfilter hasilnya berdasarkan username.

    3. Melakukan Bitmap Index Scan. Ini adalah teknik canggih di mana PostgreSQL menggunakan kedua indeks secara bersamaan. Ia membuat bitmap (peta bit) dari hasil indeks pertama dan bitmap dari hasil indeks kedua. Kemudian, ia menggabungkan kedua bitmap tersebut untuk menemukan baris yang memenuhi kedua syarat.

    Contoh hasil EXPLAIN ANALYZE:

    Bitmap Heap Scan on users  (cost=12.28..12.29 rows=1 width=122) (actual time=0.012..0.012 rows=1 loops=1)
  Recheck Cond: ((username = 'jane_doe'::text) AND (registered_at >= '2025-01-01'::date))
  ->  BitmapAnd  (cost=12.28..12.28 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_users_username  (cost=0.00..6.14 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=1)
              Index Cond: (username = 'jane_doe'::text)
        ->  Bitmap Index Scan on idx_users_registered  (cost=0.00..6.14 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
              Index Cond: (registered_at >= '2025-01-01'::date)

    Perhatikan istilah Bitmap Index Scan dan BitmapAnd. Ini adalah petunjuk bahwa query planner berhasil menggunakan lebih dari satu indeks untuk memproses kueri.

Sekarang mari kita lihat kasus di mana indeks yang sudah dibuat tidak digunakan oleh PostgreSQL karena kesalahan dalam kueri. Ini sering terjadi dan penting untuk dipahami agar kamu bisa menulis kueri yang lebih optimal.

Studi Kasus: Indeks Tidak Terpakai Karena Operasi pada Kolom

Skenario ini terjadi ketika kamu membuat indeks pada sebuah kolom, tetapi dalam kueri, kamu menerapkan fungsi atau operasi lain pada kolom tersebut di dalam klausa WHERE. PostgreSQL biasanya tidak akan menggunakan indeks dalam kasus seperti ini.

  • Tabel & Indeks:

    Kita gunakan tabel users yang sama, tapi kali ini kita membuat indeks pada kolom registered_at.

    CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100),
    registered_at DATE
);

-- Buat indeks pada tanggal registrasi
CREATE INDEX idx_users_registered_at ON users(registered_at);

  • Kueri yang Salah & Analisis:

    Anggap kamu ingin mencari semua pengguna yang terdaftar pada bulan Juni di tahun 2025. Secara intuitif, kamu mungkin akan menulis kueri seperti ini:

    SQL

    EXPLAIN ANALYZE SELECT * FROM users WHERE MONTH(registered_at) = 6 AND YEAR(registered_at) = 2025;

    Perilaku & Hasil yang Diharapkan:

    Meskipun ada indeks pada kolom registered_at, query planner tidak akan menggunakannya. Kenapa? Karena klausa WHERE tidak membandingkan kolom registered_at secara langsung. Sebaliknya, ia membandingkan hasil dari fungsi MONTH() dan YEAR() yang diaplikasikan pada kolom tersebut.

    Untuk menggunakan indeks, PostgreSQL perlu membandingkan nilai kolom secara langsung dengan suatu nilai atau ekspresi lain. Karena MONTH(registered_at) dan YEAR(registered_at) harus dihitung untuk setiap baris, PostgreSQL akan beralih ke Seq Scan (Sequential Scan) yang lambat.

    Contoh hasil EXPLAIN ANALYZE:

    Seq Scan on users  (cost=0.00..33.12 rows=195 width=12) (actual time=0.009..0.010 rows=2 loops=1)
  Filter: ((date_part('month'::text, registered_at) = '6'::double precision) AND (date_part('year'::text, registered_at) = '2025'::double precision))
...

    Perhatikan kata Seq Scan yang menunjukkan PostgreSQL membaca seluruh tabel dari awal sampai akhir.

Solusi: Menggunakan Kueri yang Benar

Untuk memastikan indeks digunakan, kamu harus mengubah kueri agar perbandingannya langsung pada kolom yang diindeks. Alih-alih menggunakan fungsi, gunakan rentang waktu (range).

  • Kueri yang Benar & Analisis:

    SQL

    EXPLAIN ANALYZE SELECT * FROM users WHERE registered_at >= '2025-06-01' AND registered_at < '2025-07-01';

  • Perilaku & Hasil yang Diharapkan:

    Kali ini, query planner akan melihat bahwa kamu membandingkan kolom registered_at secara langsung dengan literal tanggal. Ini memungkinkan penggunaan indeks idx_users_registered_at. PostgreSQL akan melakukan Index Scan yang cepat dan efisien.

    Contoh hasil EXPLAIN ANALYZE:

    Index Scan using idx_users_registered_at on users  (cost=0.29..8.30 rows=1 width=122) (actual time=0.010..0.011 rows=1 loops=1)
  Index Cond: ((registered_at >= '2025-06-01'::date) AND (registered_at < '2025-07-01'::date))
...

Ini adalah contoh yang jelas bahwa indeks hanya berguna jika kueri memanfaatkannya dengan benar. Dengan memahami cara kerja EXPLAIN ANALYZE, kamu bisa mengidentifikasi masalah seperti ini dan mengoptimalkan kuerimu.

PreviousPostgreSQL 18 😲NextHashiCorp Vault

Last updated