PostgreSQL Indexing: Apa, Mengapa, dan Bagaimana Menerapkannya
Tags: postgres, tutorial, sql
Published: 2022-09-19 16:52:59

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)
);
```
```
CREATE TABLE books (
	id uuid NOT NULL,
	title varchar(255) NOT NULL,
	description text NOT NULL,
	CONSTRAINT pk_books_id PRIMARY KEY (id)
);
```
```
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);
```
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
```

## 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'
```
Kita pakai `explain analyze` untuk mendapatkan _query plan_-nya.
```
explain analyze
select * from books b where title = 'Buku Ejnirza'
```

_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
```

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);
```
Lalu kita jalankan ulang _query_ yang sama.
```
explain analyze
select * from books b where title = 'Buku Ejnirza'
```

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
```
Dengan `Index Scan`:
`Cost`-nya turun drastis dari <u>895.00</u> menjadi **<u>8.30</u>**
`Execution time`-nya juga lebih cepat — dari <u>2.852 ms</u> menjadi **<u>0.026 ms</u>**.

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
```
_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'
```

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
```
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);
```
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'
```
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
```
Terjadi penurunan _Execution time_ yang signifikan ya, dari <u>0.165 ms</u> menjadi **<u>0.039 ms</u>**.
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'
```
_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
```
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'
```
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
```
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.