Wajib Tau Cara Mengatasi Full Table Scan karena Query Tidak Optimal pada PostgreSQL

Salah satu masalah yang sering ditemukan dalam pengelolaan PostgreSQL adalah terjadinya Full Table Scan. Full table scan terjadi ketika PostgreSQL membaca seluruh baris dalam tabel untuk memenuhi sebuah query. Meskipun ini terkadang diperlukan, tetapi dalam tabel besar, ini bisa menyebabkan performa lambat.

Contoh Masalah Full Table Scan

Skema Tabel

Misalkan kita memiliki tabel berikut:

CREATE TABLE orders (

    order_id SERIAL PRIMARY KEY,

    customer_id INT NOT NULL,

    order_date DATE NOT NULL,

    total_amount DECIMAL(10, 2) NOT NULL

);

Query Tidak Dioptimalkan

Query berikut menyebabkan full table scan:

SELECT * FROM orders WHERE customer_id = 1001;

Penyebab Full Table Scan

• Indeks Tidak Ada: PostgreSQL tidak memiliki indeks untuk kolom yang digunakan dalam filter.
• Fungsi atau Operator Tidak Didukung: Query menggunakan fungsi yang membuat indeks tidak bisa digunakan (misalnya UPPER(customer_name) jika customer_name diindeks).
• Penggunaan Operator Tidak Tepat: Indeks tidak digunakan jika operator yang dipakai tidak sesuai.
• Statistik Tidak Akurat: PostgreSQL salah memperkirakan jumlah baris yang akan diambil karena statistik tidak diperbarui.

Langkah-Langkah Mengatasi Full Table Scan

1. Verifikasi Rencana Eksekusi Query

Gunakan EXPLAIN untuk memeriksa apakah query menggunakan full table scan:

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;

Output Tanpa Indeks:

Seq Scan on orders  (cost=0.00..1234.00 rows=10 width=48)

Seq Scan menunjukkan full table scan.

2. Buat Indeks yang Sesuai

Untuk query yang sering difilter berdasarkan kolom tertentu, buat indeks pada kolom tersebut:

CREATE INDEX idx_customer_id ON orders(customer_id);

Setelah indeks dibuat, ulangi perintah EXPLAIN dan pastikan indeks digunakan:

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;

Output Setelah Indeks Dibuat:

Index Scan using idx_customer_id on orders  (cost=0.00..34.00 rows=10 width=48)

Index Scan menunjukkan PostgreSQL menggunakan indeks, yang lebih efisien.

3. Hindari Fungsi pada Kolom yang Diindeks

Jika query menggunakan fungsi seperti UPPER() atau LOWER(), indeks tidak akan digunakan.

Contoh Query Bermasalah:

SELECT * FROM orders WHERE UPPER(customer_id::TEXT) = '1001';

Solusi:

Gunakan query tanpa fungsi langsung pada kolom yang diindeks, atau buat indeks fungsional:

CREATE INDEX idx_upper_customer_id ON orders (UPPER(customer_id::TEXT));

4. Optimalkan Operator dalam Query

Jika operator yang digunakan tidak sesuai dengan jenis indeks, PostgreSQL mungkin tidak menggunakan indeks.

Masalah:

SELECT * FROM orders WHERE customer_id::TEXT = '1001';

Solusi:

Gunakan tipe data yang konsisten dalam query:

SELECT * FROM orders WHERE customer_id = 1001;

5. Perbarui Statistik Tabel

Jika statistik tabel tidak akurat, PostgreSQL mungkin memilih full table scan meskipun ada indeks.

Perbarui statistik dengan perintah berikut:

ANALYZE orders;

6. Hindari Penggunaan SELECT *

Mengambil semua kolom (SELECT *) bisa membuat PostgreSQL memilih strategi yang kurang optimal. Ambil hanya kolom yang diperlukan:

SELECT order_id, total_amount FROM orders WHERE customer_id = 1001;

7. Optimalkan Query dengan Batasan (LIMIT)

Jika hanya membutuhkan sejumlah hasil tertentu, gunakan LIMIT untuk mengurangi beban query:

SELECT * FROM orders WHERE customer_id = 1001 LIMIT 10;

8. Gunakan Partitioning untuk Tabel Besar

Jika tabel memiliki jutaan baris, pertimbangkan untuk membagi tabel menjadi beberapa partisi.

Contoh: Membuat partisi berdasarkan tanggal:

CREATE TABLE orders_2024 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

Pengujian Kinerja Query Sebelum dan Sesudah

Jalankan query awal tanpa indeks dan catat waktu eksekusi.

Tambahkan indeks dan ulangi query, catat perbedaan waktu.

Gunakan alat seperti pg_stat_statements untuk memonitor performa query secara berkelanjutan:

SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Full table scan dapat memperlambat performa PostgreSQL jika tabel berukuran besar. Dengan membuat indeks, memastikan tipe data dan operator konsisten, serta menjaga statistik tabel tetap akurat, Anda dapat mengoptimalkan query dan memanfaatkan potensi penuh PostgreSQL. Semoga bermanfaat dan mohon maaf jika ada informasi yang tidak sesuai.