POSTGRES: Mengapa Function pada WHERE Menganggu INDEX

Index itu akan diabaikan

Photo by Shahadat Rahman on Unsplash

Kadang-kala table yang mempunyai index masih memberikan query performance yang tidak baik. Mengapa ya? Dalam rencana kali ini saya ingin membawa anda kepada salah satu faktor mengapa index yang digunakan dalam table diabaikan oleh query execution plan. Baik mari mulakan!!!

Jika anda ingin bersama-sama membuat ujian ini anda boleh download database ini di github saya: northwind_psql (database ini saya fork daripada github asal supaya tidak mengganggu aturan tutorial lain selepes ini).

Berdasarkan database tersebut ada sebuah table order. Jika kita melihat struktur table tersebut ia masih tidak mempunya index selain daripada primary key index.

Bayangkan anda ditugaskan untuk tuning table ini. Dalam salah satu query yang sering digunakan adalah

Query untuk mendapatkan senarai order/pesanan berdasarkan julat tarikh.

Contoh query adalah seperti berikut:

select * from orders
where
    order_date >= '1996-07-01' and  order_date < '1996-08-01';

Berdasarkan maklumat ini anda akan memeriksa execution plan untuk query tersebut.

Berdasarkan maklumat di atas, anda dapati query tersebut menggunakan FULL SCAN. Ini bermakna query engine akan menyemak SEMUA rows dalam table untuk mendapatkan query result dan ini merupakan salah satu punca performance issue.

Untuk mengatasi masalah ini anda akan membina index pada column order_date.

CREATE INDEX idx_orders_dt ON orders(order_date);

Sekarang table tersebut mempunyai index yang pada column order_date

Kita query semula data yang sama dan lihat execution plan tersebut.

Kali ini query engine menggunakan INDEX SCAN untuk mendapatkan data yang diperlukan. Jika anda lihat Total Cost yang diperlukan ianya jauh lebih sedikit berbanding dengan execution plan sebelum ini (lama: 26.45 baru: 8.67)

Performance yang lebih baik bukan?

Katakanlah anda ditugaskan untuk mendapatkan maklumat order hanya pada tahun 1996. Apakah query yang akan anda gunakan? Adakah seperti di bawah?

select * from orders
where
    extract('year' FROM order_date) = '1996'

Jika query ini yang digunakan, anda akan terjerat dengan performance issue sekali lagi. Mungkin anda akan berkata “Sebelum inikan sudah ada index pada column order_date” dan akan menyangka index tersebut akan digunakan. Mari kita lihat execution plan bagi query di atas:

Query tersebut menggunakan FULL SCAN dalam usaha ia mendapatkan maklumat. Ia mengabaikan index yang dibina tadi. Kerana itulah kita patut mengelak daripada menggunakan function dalam WHERE conditon.

Saya sarankan anda menggunakan query di bawah untuk mendapatkan performance yang baik walaupun query yang lebih panjang

select * from orders
where
    order_date >= '1996-01-01' and  order_date < '1997-01-01';

Harapnya dapat membantu anda memahami mengapa kadang-kadang index tidak gunakan dalam query.