Mendeteksi dan mengoptimalkan query lambat adalah tugas seorang DBA dimana setiap minggunya akan dibahas mengenai top 10 query yang lambat dan bagaimana cara mengoptimalkan query tersebut.
Query lambat dapat di cek dengan menjalankan query seperti berikut :
SELECT round((100 * total_time / sum(total_time)
OVER ())::numeric, 2) percent,
round(total_time::numeric, 2) AS total,
calls,
round(mean_time::numeric, 2) AS mean,
round(min_time::numeric, 2) AS min,
round(max_time ::numeric, 2) AS max,
dbid,
regexp_replace(query, ‘\r|\n’, ‘ ‘, ‘g’) as QUERY
FROM pg_stat_statements
ORDER by mean desc
setelah menjalankan query diatas maka akan muncul output seperti berikut :
Dari hasil dijalankannya query diatas maka akan muncul informasi mengenai query apa saja yang lambat mulai dari persentasi cost query yang dibutuhkan, total waktu yang dibutuhkan dalam satuan milisecond, berapa kali query tersebut di eksekusi, rata – rata waktu yang diperlukan, dll.
Setelah query lambat ditemukan maka query tersebut akan dilakukan analisa seperti menambahkan “explain analyze” pada query yang bermasalah contoh :
pada hasil query diatas akan menunjukkan bagaimana query tersebut di eksekusi, berapa waktu eksekusi yang dibutuh kan dan jenis scan apa yang digunakan, apabila tidak ada sequence scan maka query tersebut seharus nya sudah optimal di bagian filtering namun apabila query tersebut masih menggunakan seq scan atau sequencial scan maka query tersebut perlu di tambahkan index.
Cara menambahkan index bisa diliat dari kebutuhan filtering pada sebuah query misal :
select * from table where id = 1 and name = ‘ariq’ and age = 24
bisa diliat di bagian setelah where adalah proses filtering pada sebuah query maka bisa kita tambahkan index pada query tersebut, cara menambahkan index agar optimal harus kita urutkan sesuai kolom apa saja yang di filter pada query tersebut, karena urutan filter pada query tersebut adalah id,name,age maka kita akan sesuaikan index sesuai dengan filter tersebut :
create index nama_index on nama_table using btree (id,name,age)
apabila index sudah terbuat seharusnya pada saat dilakukan explain analyze sudah tidak ada seq scan, namun apabila masih ditemukan seq scan ada 2 kemungkinan, kemungkinan yang pertama seq scan dirasa lebih optimal dari pada index scan yang dibuat sehingga akhirnya query planner memutuskan menggunakan seq scan, yang kedua cost pada query terlalu besar sehingga tidak dapat dijalankan index scan