Database Indexing Cheat Sheet

Database Indexing Cheat Sheet - BelajarKoding | BelajarKoding

-- Index pada satu kolom
CREATE INDEX idx_users_email ON users(email);
 
-- Index unik (mencegah duplikat)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
 
-- Index dengan nama custom
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
 
-- Index dengan expression
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
 
-- Index dengan operator class
CREATE INDEX idx_users_email_ci ON users(LOWER

sql

-- Full-text search
CREATE INDEX idx_articles_fts ON articles USING gin(to_tsvector('english', title || ' ' || body));
 
-- Array contains
CREATE INDEX idx_posts_tags ON posts USING gin(tags);
 
-- JSONB
CREATE INDEX

sql

-- Geospatial (PostGIS)
CREATE INDEX idx_locations_geom ON locations USING gist(geom);
 
-- Range type
CREATE INDEX idx_events_period ON events USING gist(during);
 
-- Exclusion constraint
CREATE TABLE reservations (
  id serial

Tipe	Operator	Ukuran	Cocok Untuk
B-tree	=, <, >, BETWEEN, LIKE 'prefix%'	Sedang	General purpose, range, sort
Hash	=	Kecil	Equality saja, key panjang
GIN	@>, ?, tsvector	Besar	Array, JSONB, FTS
GiST	&&, <->, <<	Sedang	Geometri, range, trigram
BRIN	=, <, >	Sangat kecil	Log append-only, tabel besar
SP-GiST	Tergantung	Sedang	Tree-like, trie

sql

-- Index pada dua kolom
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
 
-- Index ini optimal untuk:
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';
SELECT * FROM orders

sql

-- Index hanya untuk order yang belum lunas
CREATE INDEX idx_orders_unpaid ON orders(user_id) WHERE status = 'pending';
 
-- Index hanya untuk user aktif
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;
 
-- Index untuk high-value orders

sql

-- Index covering untuk query yang butuh user_id dan amount
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (amount, status);
 
-- Query ini bisa pakai index-only scan
SELECT amount, status FROM orders WHERE user_id = 123;

Aspek	Composite (`(a, b, c)`)	Covering (`(a) INCLUDE (b, c)`)
Bisa filter?	Ya, semua kolom	Hanya kolom key (a)
Bisa sort?	Ya, sesuai urutan	Tidak, kolom INCLUDE tidak sort
Bisa SELECT?	Ya	Ya
Ukuran	Lebih besar	Sedikit lebih besar dari key-only

sql

-- Query plan tanpa eksekusi
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
 
-- Query plan dengan eksekusi dan statistik waktu
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com'

plaintext

HashAggregate  (cost=12.50..15.00 rows=100 width=40) (actual time=2.1..2.5 rows=85 loops=1)
  Hash Join  (cost=8.0..12.0 rows=200 width=40) (actual time=1.0..1.8 rows=200 loops=1)
    Hash Cond: (o.user_id = u.id)
    ->  Seq Scan on orders o  (cost=0..5.0 rows=200 width=8) (actual time=0.01..0.3 rows=200 loops=1)
    ->  Hash  (cost=6.0..6.0 rows=100 width=36) (actual time=0.5..0.5 rows=100 loops=1)
          Buckets: 1024  Batches: 1  Memory Usage: 12kB
          ->  Seq Scan on users u  (cost=0..6.0 rows=100 width=36) (actual time=0.01..0.3 rows=100 loops=1)
                Filter: (status = 'active')

Node Type	Arti
`Seq Scan`	Sequential scan, baca seluruh tabel
`Index Scan`	Baca index lalu akses tabel
`Index Only Scan`	Hanya baca index, tanpa akses tabel
`Bitmap Index Scan`	Bitmap dari index, lalu akses tabel
`Bitmap Heap Scan`	Akses tabel berdasarkan bitmap
`Hash Join`	Hash table untuk join
`Nested Loop`	Loop nested untuk join
`Merge Join`	Merge untuk join (butuh data terurut)
`Sort`	Pengurutan data

sql

-- Index yang tidak pernah dipakai
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
 
-- Index duplikat atau redundant
SELECT pg_size_pretty(sum(pg_relation_size(idx))::

sql

-- Reindex satu index
REINDEX INDEX idx_users_email;
 
-- Reindex seluruh tabel
REINDEX TABLE users;
 
-- Reindex seluruh database
REINDEX DATABASE mydb;
 
-- Reindex tanpa blocking (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_users_email;

sql

-- Vacuum biasa
VACUUM users;
 
-- Vacuum dengan analisis statistik
VACUUM ANALYZE users;
 
-- Vacuum penuh (rebuild tabel, mengunci tabel)
VACUUM FULL users;
 
-- Autovacuum setting
ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE users SET

sql

SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
       idx_scan as index_scans
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

sql

-- LIKE dengan prefix: pakai text_pattern_ops
CREATE INDEX idx_users_name ON users(name varchar_pattern_ops);
-- Sekarang LIKE 'John%' bisa pakai index
 
-- ILIKE case-insensitive: pakai LOWER
CREATE INDEX idx_users_name_ci ON users(LOWER(name) varchar_pattern_ops);
-- Sekarang LOWER(name) LIKE 'john%' bisa pakai index

sql

-- GIN index untuk JSONB
CREATE INDEX idx_events_data ON events USING gin(data);
 
-- jsonb_path_ops lebih kecil tapi hanya dukung @>
CREATE INDEX idx_events_data_path ON events USING gin(data jsonb_path_ops);
 
-- B-tree index untuk key spesifik
CREATE INDEX idx_events_type

sql

-- GIN index untuk array containment
CREATE INDEX idx_posts_tags ON posts USING gin(tags);
 
SELECT * FROM posts WHERE 'javascript' = ANY(tags);
SELECT * FROM posts WHERE tags && ARRAY['javascript', 'python'];
SELECT *

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 
-- B-tree index untuk UUID
CREATE INDEX idx_sessions_uuid ON sessions USING btree(session_id);
 
-- Untuk UUID v7 (sortable) atau UUID v4 (random)
-- UUID v7 lebih baik untuk index locality karena sequential

Baca Cheat Sheet Lengkap

#Dasar Index

#Apa Itu Index

#Anatomi Index PostgreSQL

#Membuat dan Menghapus Index

#CONCURRENTLY

#Tipe Index di PostgreSQL

#B-tree (Default)

#Hash Index

#GIN (Generalized Inverted Index)

#GiST (Generalized Search Tree)

#BRIN (Block Range Index)

#SP-GiST (Space-Partitioned GiST)

#Perbandingan Tipe Index

#Composite Index

#Apa Itu Composite Index

#Aturan Urutan Kolom

#Multi-Column Index Limit

#Partial Index

#Apa Itu Partial Index

#Covering Index

#Apa Itu Covering Index

#Perbedaan Composite vs Covering

#Index-Only Scan

#Apa Itu Index-Only Scan

#EXPLAIN ANALYZE

#Apa Itu EXPLAIN ANALYZE

#Membaca Query Plan

#Node Type yang Umum

#Indikasi Masalah

#Index yang Tidak Terpakai

#Mendeteksi Index Tak Terpakai

#Kapan TIDAK Perlu Index

#Maintenance Index

#REINDEX

#VACUUM dan Visibility Map

#Monitoring Index Bloat

#Index untuk Tipe Data Spesifik

#Text dan Pattern Matching

#JSONB

#Array

#UUID

#Index dan Join

#Glossary