DEV Community

Andhi Prayoga
Andhi Prayoga

Posted on

PostgreSQL Indexing: Apa, Mengapa, dan Bagaimana Menerapkannya

#postgres #tutorial #sql

Kita tahu indexing dapat mempercepat pengambilan data pada database. Tapi bagaimana sebenarnya index bekerja?

Inilah yang perlu kita ketahui tentang indexing pada Postgres:

  1. Bagaimana Postgres menyimpan data, membangun index dan menggunakannya?
  2. Bagaimana mengetahui kapan index diperlukan?
  3. Bagaimana cara kita membangun index yang efektif dan mengubah query yang lambat menjadi secepat kilat?

Skema DB

Misal kita punya sebuah database dari suatu sistem peminjaman buku sederhana dengan 3 tabel: users, books, dan borrowings.

CREATE TABLE users (
    id uuid NOT NULL,
    roles jsonb NOT NULL DEFAULT '[]'::jsonb,
    email varchar(255) NOT NULL,
    CONSTRAINT users_email_unique UNIQUE (email),
    CONSTRAINT users_pkey PRIMARY KEY (id)
);
Enter fullscreen mode Exit fullscreen mode 
CREATE TABLE books (
    id uuid NOT NULL,
    title varchar(255) NOT NULL,
    description text NOT NULL,
    CONSTRAINT pk_books_id PRIMARY KEY (id)
);
Enter fullscreen mode Exit fullscreen mode 
CREATE TABLE borrowings (
    id uuid NOT NULL,
    borrower_id uuid NOT NULL,
    book_id uuid NOT NULL,
    borrow_date timestamptz NOT NULL,
    must_return_date timestamptz NOT NULL,
    return_date timestamptz NULL,
    CONSTRAINT pk_borrowings_id PRIMARY KEY (id)
);

ALTER TABLE borrowings ADD CONSTRAINT fk_borrowings_book_id_books_id FOREIGN KEY (book_id) REFERENCES books(id);
ALTER TABLE borrowings ADD CONSTRAINT fk_borrowings_borrower_id_users_id FOREIGN KEY (borrower_id) REFERENCES users(id);
Enter fullscreen mode Exit fullscreen mode

Setelah kita seeds 100 users, 1.000 borrowings, dan 10.000 books, kita mendapatkan sizes sebagai berikut:

table_name  | total | table | index 
------------+-------+-------+------
books       | 6.4M  | 6.1M  | 400K
borrowings  | 200K  | 144K  |  56K
users       | 112K  |  80K  |  32K
Enter fullscreen mode Exit fullscreen mode

Cara Postgres Menyimpan dan Membaca Data

Tabel books memiliki total size 6.4MB. Ukuran 6.4MB ini tersusun dari banyak page data berukuran masing-masing 8KB. Jadi records pada tabel books tersimpan sebagai ±800 pages.

🔑 Ketika kita menjalankan suatu query, Postgres akan mengoptimasi query tersebut dengan cost seminimal mungkin. Ada banyak faktor yang mempengaruhi cost. Salah satu faktor yang paling berpengaruh adalah jumlah page yang terlibat pada suatu query. Semakin banyak page yang di scan, semakin besar cost-nya. Di sisi kita, tentu semakin besar cost, semakin lama pula execution time-nya.

Misal kita mau mencari buku dengan judul Buku Ejnirza.

Kita jalankan query berikut:

select * from books b where title = 'Buku Ejnirza'
Enter fullscreen mode Exit fullscreen mode

Kita pakai explain analyze untuk mendapatkan query plan-nya.

explain analyze
select * from books b where title = 'Buku Ejnirza'
Enter fullscreen mode Exit fullscreen mode

Result-nya sebagai berikut:

Seq Scan on books b  (cost=0.00..895.00 rows=1 width=583) (actual time=0.890..2.828 rows=1 loops=1)
  Filter: ((title)::text = 'Buku Ejnirza'::text)
  Rows Removed by Filter: 9999
Planning time: 0.117 ms
Execution time: 2.852 ms
Enter fullscreen mode Exit fullscreen mode

Mari kita perhatikan result di atas.
Seq Scan menunjukkan bahwa query ini melakukan scanning pada 800 pages / 10.000 rows dan 'membuang' 9.999 sisanya.
Cost-nya 895.00.
Dan memakan waktu sekitar 2 detik lebih. Cukup lama untuk mengambil 1 data saja.

Di sinilah index diperlukan

Kita coba tambahkan index untuk kolom title.

CREATE INDEX ix_books_title ON books USING btree (title);
Enter fullscreen mode Exit fullscreen mode

Lalu kita jalankan ulang query yang sama.

explain analyze
select * from books b where title = 'Buku Ejnirza'
Enter fullscreen mode Exit fullscreen mode

Kita cek query plan-nya:

Index Scan using ix_books_title on books b  (cost=0.29..8.30 rows=1 width=583) (actual time=0.016..0.017 rows=1 loops=1)
  Index Cond: ((title)::text = 'Buku Ejnirza'::text)
Planning time: 0.127 ms
Execution time: 0.026 ms
Enter fullscreen mode Exit fullscreen mode

Dengan Index Scan:
Cost-nya turun drastis dari 895.00 menjadi 8.30
Execution time-nya juga lebih cepat — dari 2.852 ms menjadi 0.026 ms.

Apakah ini keajaiban? Mungkin semacam itu.😆
Tapi ada harga yang harus dibayar.
Mari kita perhatikan sizes dari tabel kita sekarang:

table_name  | total | table | index 
------------+-------+-------+------
books       | 6.8M  | 6.1M  | 728K
borrowings  | 200K  | 144K  |  56K
users       | 112K  |  80K  |  32K
Enter fullscreen mode Exit fullscreen mode

Index dari tabel books meningkat size-nya.
Karena yang terjadi di sini adalah query membaca data dalam index yang "hanya" 41 page. Tentu jauh lebih cepat jika dibandingkan query sebelumnya yang membaca data 800 page.
🔑 Jadi kita "membayar" dengan storage untuk mendapatkan query yang lebih cepat.

Index yang lebih efektif

Kita coba query lain. Misalnya kita mau mengetahui Buku Orkenji sedang dipinjam oleh berapa user.

Kita jalankan query berikut:

explain analyze
select
    count(*)
from
    borrowings b
inner join books b2 on
    b2.id = b.book_id
where
    b2.title = 'Buku Orkenji'
Enter fullscreen mode Exit fullscreen mode

Result-nya sebagai berikut:

Aggregate  (cost=35.94..35.95 rows=1 width=8) (actual time=0.145..0.146 rows=1 loops=1)
  ->  Hash Join  (cost=8.31..35.94 rows=1 width=0) (actual time=0.111..0.144 rows=2 loops=1)
        Hash Cond: (b.book_id = b2.id)
        ->  Seq Scan on borrowings b  (cost=0.00..25.00 rows=1000 width=16) (actual time=0.003..0.055 rows=1000 loops=1)
        ->  Hash  (cost=8.30..8.30 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              ->  Index Scan using ix_books_title on books b2  (cost=0.29..8.30 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=1)
                    Index Cond: ((title)::text = 'Buku Orkenji'::text)
Planning time: 0.145 ms
Execution time: 0.165 ms
Enter fullscreen mode Exit fullscreen mode

Kita bisa melihat bahwa index ix_books_title yang kita buat sebelumnya berkontribusi pada optimasi query ini.
Kita menemukan juga Seq Scan pada tabel borrowings.
🔑 Sepertinya ini clue bahwa index bisa membantu.

Kita tambahkan index untuk book_id pada borrowings:

CREATE INDEX ix_borrowings_book_id ON borrowings USING btree (book_id);
Enter fullscreen mode Exit fullscreen mode

Lalu kita ulangi query sebelumnya:

explain analyze
select
    count(*)
from
    borrowings b
inner join books b2 on
    b2.id = b.book_id
where
    b2.title = 'Buku Orkenji'
Enter fullscreen mode Exit fullscreen mode

Dan inilah hasilnya:

Aggregate  (cost=16.61..16.62 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=1)
  ->  Nested Loop  (cost=0.56..16.61 rows=1 width=0) (actual time=0.018..0.020 rows=2 loops=1)
        ->  Index Scan using ix_books_title on books b2  (cost=0.29..8.30 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1)
              Index Cond: ((title)::text = 'Buku Orkenji'::text)
        ->  Index Only Scan using ix_borrowings_book_id on borrowings b  (cost=0.28..8.29 rows=1 width=16) (actual time=0.009..0.010 rows=2 loops=1)
              Index Cond: (book_id = b2.id)
              Heap Fetches: 2
Planning time: 0.242 ms
Execution time: 0.039 ms
Enter fullscreen mode Exit fullscreen mode

Terjadi penurunan Execution time yang signifikan ya, dari 0.165 ms menjadi 0.039 ms.
Jumlah cost-nya juga berkurang jauh.

Case selanjutnya. Kita jalankan query berikut:

explain analyze
select
    count(*)
from
    borrowings b
where
    return_date is null
    and borrower_id = '03229391-cce9-44d6-8a36-705c90d55e10'
Enter fullscreen mode Exit fullscreen mode

Query plan-nya:

Aggregate  (cost=27.52..27.54 rows=1 width=8) (actual time=0.291..0.292 rows=1 loops=1)
  ->  Seq Scan on borrowings b  (cost=0.00..27.50 rows=10 width=0) (actual time=0.035..0.286 rows=10 loops=1)
        Filter: ((return_date IS NULL) AND (borrower_id = '03229391-cce9-44d6-8a36-705c90d55e10'::uuid))
        Rows Removed by Filter: 990
Planning time: 0.093 ms
Execution time: 0.327 ms
Enter fullscreen mode Exit fullscreen mode

Tentu kita tahu apa yang perlu kita lakukan.

Misalnya ternyata kita perlu menambahkan filter pada query tersebut menjadi:

explain analyze
select
    count(*)
from
    borrowings b
where
    return_date is null
    and borrower_id = '03229391-cce9-44d6-8a36-705c90d55e10' and book_id = '05938c72-1774-4a03-be68-218a69d24c22'
Enter fullscreen mode Exit fullscreen mode

Apakah kita masih perlu menambahkan index untuk menghindari Seq Scan? Kita cek saja query plan-nya.

Aggregate  (cost=8.30..8.31 rows=1 width=8) (actual time=0.038..0.039 rows=1 loops=1)
  ->  Index Scan using ix_borrowings_book_id on borrowings b  (cost=0.28..8.29 rows=1 width=0) (actual time=0.030..0.033 rows=1 loops=1)
        Index Cond: (book_id = '05938c72-1774-4a03-be68-218a69d24c22'::uuid)
        Filter: ((return_date IS NULL) AND (borrower_id = '03229391-cce9-44d6-8a36-705c90d55e10'::uuid))
Planning time: 0.182 ms
Execution time: 0.097 ms
Enter fullscreen mode Exit fullscreen mode

Ternyata tidak perlu.
Karena Seq Scan tidak muncul dan andai kita tetap menambahkan index, hanya akan menambah storage yang terpakai.

Jadi...

Setelah kita mengerti (mengingat kembali) bagaimana index bekerja, barangkali kita akan menangani optimasi query dengan lebih baik ke depannya.

Mengerti bagaimana data disimpan dan dibaca, kapan index diperlukan, dan bagaimana index yang efektif.
Gunakan ini dan kita akan dapat mempercepat pengambilan data pada database dengan optimal.

Top comments (0)

Read next

_russell profile image

Systemd vs. Docker: Exploring a Surprising Alternative

Russell 👨🏾‍💻 -

julia970 profile image

How to Design a Tangram Puzzle Using 3D CAD Software

Juliana Misiko -

sarthak_niranjan_3bd6cb5f profile image

CSS Cleanup Tools: Top Picks for Cleaner, Faster Code

Sarthak Niranjan -

code42cate profile image

Day 8: Week 1 Quiz

Jonas Scholz -