Sebagai administrator basis data, kita harus terobsesi dengan alat dan metode untuk meningkatkan kinerja basis data.
Di PostgreSQL, kami memiliki akses ke perintah EXPLAIN ANALYZE yang memungkinkan kami menganalisis rencana eksekusi dan kinerja kueri basis data yang diberikan. Perintah mengembalikan informasi terperinci tentang bagaimana mesin basis data memproses kueri. Ini termasuk urutan operasi yang dilakukan, perkiraan biaya kueri, waktu eksekusi, dan lainnya.
Kami kemudian dapat menggunakan informasi ini untuk mengidentifikasi kueri database serta mengidentifikasi dan memperbaiki potensi hambatan kinerja.
Tutorial ini membahas cara menggunakan perintah EXPLAIN ANALYZE di PostgreSQL untuk melihat dan mengoptimalkan kinerja query.
PostgreSQL MENJELASKAN ANALISIS
Perintahnya cukup mudah. Pertama, kita perlu menambahkan perintah EXPLAIN ANALYZE di awal kueri yang ingin kita analisis.
Sintaks perintahnya adalah sebagai berikut:
JELASKAN ANALISISSetelah Anda menjalankan perintah, PostgreSQL mengembalikan keluaran mendetail tentang kueri yang disediakan.
Memahami Output Query EXPLAIN ANALYZE
Seperti yang telah disebutkan, begitu kita menjalankan perintah EXPLAIN ANALYZE, PostgreSQL menghasilkan laporan rinci tentang rencana kueri dan statistik eksekusi.
Output terdiri dari satu set kolom yang berisi informasi yang berguna. Kolom yang dihasilkan adalah seperti yang ditunjukkan dengan artinya masing-masing:
RENCANA PERTANYAAN – Kolom ini menampilkan rencana eksekusi dari kueri yang ditentukan. Rencana eksekusi mengacu pada urutan operasi yang dilakukan mesin database untuk menyelesaikan kueri dengan sukses.
RENCANA – Kolom kedua adalah kolom RENCANA. Ini berisi representasi tekstual dari setiap operasi atau langkah dalam rencana eksekusi. Sekali lagi, setiap operasi diindentasi untuk menunjukkan hierarki operasi.
TOTAL BIAYA – Kolom biaya total menunjukkan perkiraan biaya total kueri. Biaya mengacu pada ukuran relatif yang digunakan oleh perencana kueri basis data untuk menentukan rencana eksekusi yang optimal.
BARIS SEBENARNYA – Kolom ini menunjukkan jumlah persis baris yang diproses pada setiap langkah dalam eksekusi kueri.
WAKTU SEBENARNYA – Kolom ini menunjukkan waktu aktual yang diambil oleh setiap operasi yang mencakup waktu eksekusi operasi dan waktu yang dihabiskan untuk sumber daya.
WAKTU PERENCANAAN – Kolom ini menampilkan waktu yang dibutuhkan perencana kueri untuk membuat rencana eksekusi. Ini termasuk total waktu pengoptimalan kueri dan pembuatan rencana.
WAKTU PELAKSANAAN – Kolom ini menunjukkan total waktu untuk mengeksekusi kueri. Ini juga termasuk waktu yang dihabiskan untuk perencanaan dan waktu eksekusi kueri.
Contoh PostgreSQL EXPLAIN ANALYZE
Mari kita lihat beberapa contoh dasar penggunaan pernyataan EXPLAIN ANALYZE.
Contoh 1: Pilih Pernyataan
Mari kita gunakan pernyataan EXPLAIN ANALYZE untuk menunjukkan eksekusi dari pernyataan pemilihan sederhana di PostgreSQL.
Setelah kita menjalankan pernyataan sebelumnya, kita harus mendapatkan output sebagai berikut:
RENCANA PERTANYAAN-------------------------------------------------- -----------------
Seq Scan pada wp_users (biaya=0.00..10.38 baris=10 lebar=2256) (waktu sebenarnya=0.009..0.010 baris=7 putaran=1)
Filter: (id > 3)
Baris Dihapus oleh Filter: 3
Waktu Perencanaan: 0,995 mdtk
Waktu Eksekusi: 0,021 ms
(5 baris)
Dalam hal ini, kita dapat melihat bahwa bagian Rencana kueri menunjukkan bahwa kueri melakukan pemindaian berurutan pada tabel wp_users. Garis filter menunjukkan kondisi yang digunakan untuk memfilter baris yang dihasilkan.
Kami kemudian melihat 'Baris Dihapus oleh Filter' yang menunjukkan jumlah baris yang dihilangkan oleh kondisi filter.
Terakhir, waktu eksekusi menampilkan total waktu eksekusi kueri. Dalam hal ini, kueri memerlukan waktu 0,021 md.
Contoh 2: Menganalisis Gabungan
Mari kita ambil kueri yang lebih kompleks yang melibatkan gabungan SQL. Untuk ini, kami menggunakan database sampel Pagila. Anda dapat mendownload dan menginstal database sampel pada mesin Anda untuk tujuan demonstrasi.
Kita dapat menjalankan gabungan sederhana seperti yang ditunjukkan berikut ini:
jelaskan analisis SELECT f.title, c.nameDARI film f
GABUNG film_category fc ON f.film_id = fc.film_id
GABUNG kategori c ON fc.category_id = c.category_id;
Setelah kami menjalankan kueri yang diberikan, kami akan melihat hasilnya sebagai berikut:
Mari kita jelajahi rencana kueri berikut:
- Nested Loop – Ini menunjukkan bahwa gabungan menggunakan strategi gabungan loop bersarang.
- Hash Join – Operasi ini menggabungkan film_category dan tabel film menggunakan algoritma Hash join. Operasi ini memiliki biaya 77,50 dan diperkirakan 1000 baris. Namun, waktu aktual yang dibutuhkan untuk operasi ini adalah 0,254 hingga 0,439 milidetik, dan mengambil 1000 baris.
- Hash Cond – Ini menunjukkan bahwa kondisi join menggunakan Hash join untuk mencocokkan kolom film_id dan kolom film_category di tabel film.
- Seq Scan pada film_category – Operasi ini melakukan pemindaian berurutan pada tabel film_category dengan biaya 16,00 dan perkiraan 1000 baris. Waktu aktual yang dibutuhkan untuk operasi ini adalah 0,008 hingga 0,056 milidetik, dan mengambil 1000 baris.
- Pemindaian Seq pada film – Kueri melakukan pemindaian berurutan pada tabel film dengan biaya dan baris perkiraan dan aktual yang dihasilkan dalam operasi ini.
- Memoize – Operasi ini menyimpan hasil gabungan antara film_category dan tabel film untuk penggunaan selanjutnya.
- Kunci Cache – Ini menunjukkan bahwa kunci cache yang digunakan untuk memoisasi didasarkan pada kolom category_id dari film_category.
- Mode Cache – Ini menunjukkan bahwa kueri menggunakan mode cache logis.
- Hits, Misses, Evictions, Overflows – Tiga baris memberikan statistik tentang cache, jumlah hit, misses, evictions, dan overflows selama eksekusi. Blok ini juga mencakup penggunaan memori selama eksekusi kueri.
- Pemindaian Indeks menggunakan kunci_kategori – Ini menunjukkan operasi yang melakukan pemindaian indeks pada tabel kategori menggunakan indeks kunci utama.
- Index Cond – Ini menunjukkan bahwa pemindaian indeks didasarkan pada kondisi yang cocok dengan kolom category_id di tabel kategori.
- Waktu Perencanaan – Baris ini menunjukkan waktu yang dibutuhkan untuk perencanaan kueri yaitu 3,005 milidetik.
- Waktu Eksekusi – Terakhir, baris ini menunjukkan total waktu eksekusi kueri yaitu 0,745 milidetik.
Itu dia! Informasi mendetail tentang eksekusi gabungan sederhana di PostgreSQL.
Kesimpulan
Anda menemukan kekuatan dan penggunaan pernyataan EXPLAIN ANALYZE di PostgreSQL. Pernyataan EXPLAIN ANALYZE adalah alat yang ampuh untuk analisis dan pengoptimalan kueri. Gunakan alat ini untuk membuat kueri yang efisien dan tidak terlalu intensif sumber daya.