Siklus kueri dan perencana
Meningkatkan Performa Kueri di PostgreSQL
Amy McCarty
Instructor
Siklus kueri dasar
| Sistem | Langkah front end | Proses back end | |
|---|---|---|---|
| 1 | Parser | Kirim kueri ke basis data | Memeriksa sintaks. Menerjemahkan SQL ke sintaks yang lebih ramah komputer berdasarkan aturan tersimpan sistem. |
| 2 | Planner & Optimizer | Menilai dan mengoptimalkan tugas kueri | Menggunakan statistik basis data untuk membuat rencana kueri. Menghitung biaya dan memilih rencana terbaik. |
| 3 | Executor | Mengembalikan hasil kueri | Mengikuti rencana kueri untuk mengeksekusi kueri. |
Perencana dan pengoptimal kueri
Responsif terhadap perubahan struktur SQL
- Menghasilkan pohon rencana
- Node sesuai langkah
- Visualisasi dengan EXPLAIN
- Perkirakan biaya tiap pohon
- Statistik dari pg_tables
- Optimasi berbasis waktu
1 Plan tree: https://www.postgresql.org/docs/current/querytree.html
Statistik dari pg_tables
SELECT * FROM pg_class
WHERE relname = 'mytable'
-- contoh kolom keluaran
| relname | relhasindex |
SELECT * FROM pg_stats
WHERE tablename = 'mytable'
-- contoh kolom keluaran
null_frac | avg_width | n_distinct |
- Indeks kolom
- Jumlah nilai null
- Lebar kolom
- Nilai unik
EXPLAIN
- Jendela ke rencana kueri
- Langkah dan perkiraan biaya
- Tidak menjalankan kueri
- Pemindaian berurutan pada tabel cheeses
- Perkiraan biaya dan ukuran
EXPLAIN
SELECT * FROM cheeses
Seq Scan on cheeses
(cost=0.00..10.50 rows=5725 width=296)
EXPLAIN: Scan
- Langkah rencana kueri
- Mengembalikan baris
Seq Scan pada cheeses (cost=0.00..10.50 rows=5725 width=296)
- Seq Scan: memindai semua baris di tabel
EXPLAIN: Cost
- Tanpa satuan
- Bandingkan struktur dengan keluaran yang sama
- Jangan membandingkan kueri dengan keluaran berbeda
Seq Scan pada cheeses (cost=0.00..10.50 rows=5725 width=296)
- 0.00.. : waktu mulai
..10.50 : waktu total
waktu total = waktu mulai + waktu jalan
EXPLAIN: Size
- Perkiraan ukuran
Seq Scan pada cheeses (cost=0.00..10.50 rows=5725 width=296)
- rows: baris yang perlu diperiksa kueri
- width: lebar byte per baris
EXPLAIN dengan klausa WHERE
EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep')
Seq Scan on cheeses (cost=0.00..378.90 rows=3 width=118)
-> Filter: (species = ANY ('{"goat","sheep"}'::text[]))
- Dari bawah ke atas
- Langkah 1: Filter
- Langkah 2: Sequential scan
- Klausa WHERE
- Mengurangi baris yang dipindai dan menaikkan biaya total
EXPLAIN dengan indeks
EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') -- index on species column
Bitmap Index Scan using species_idx on cheeses (cost=0.29..12.66 rows=3 width=118)
Index Cond: (species = ANY ('{"goat","sheep"}'::text[]))
- Langkah 1: Bitmap Index Scan
- Index Cond menjelaskan langkah pemindaian
- INDEKS
- Biaya mulai naik dari 0
- Biaya total turun dari 379
Ayo berlatih!
Meningkatkan Performa Kueri di PostgreSQL
