Menggunakan dan membuat indeks

Meningkatkan Performa Kueri di PostgreSQL

Amy McCarty

Instructor

Ringkasan indeks

Apa

  • Cara membuat kunci kolom terurut untuk mempercepat pencarian
  • Mirip indeks buku
  • Referensi ke lokasi data

Mengapa

  • Kueri lebih cepat

Di mana

  • Kolom yang sering dipakai untuk filter
  • Primary key
Meningkatkan Performa Kueri di PostgreSQL

Contoh indeks

ingredient recipe
tomatoes spaghetti & meatballs
green onions fried rice
eggs fried rice
ground beef spaghetti & meatballs
pasta spaghetti & meatballs
rice fried rice
soy sauce fried rice 
SELECT *
FROM cookbook
WHERE recipe = 'fried rice'
Meningkatkan Performa Kueri di PostgreSQL

Indeks sebagai kunci dan penunjuk

Dua tabel. Tabel indeks berisi kolom recipe dan kolom pointer, dari _12 hingga _18 berurutan. Tabel dengan indeks sama dengan tabel di kiri dengan kolom pointer tambahan. Nilai di kolom pointer sama dengan yang di Tabel Indeks. Panah menunjukkan empat nilai untuk nasi goreng di Tabel Indeks menuju empat nilai yang sama di Tabel dengan Indeks.

Meningkatkan Performa Kueri di PostgreSQL

Menemukan indeks yang ada

pg_tables
  • Mirip dengan information_schema
    • khusus untuk Postgres
  • Metadata tentang basis data
Meningkatkan Performa Kueri di PostgreSQL

Menemukan indeks yang ada

pg_tables
  • Mirip dengan information_schema
    • khusus untuk Postgres
  • Metadata tentang basis data

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Meningkatkan Performa Kueri di PostgreSQL

Membuat indeks

Meja makan dengan kolom bahan, resep, dan porsi saji. tomat - spaghetti & meatballs - 4. daun bawang - nasi goreng - 2. telur - nasi goreng - 2. daging sapi giling - spaghetti & meatballs - 4. pasta - spaghetti & meatballs - 4. beras - nasi goreng - 2. kecap asin - nasi goreng - 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
Meningkatkan Performa Kueri di PostgreSQL

Perlu indeks atau tidak

Kapan gunakan indeks

  • Tabel besar
  • Kondisi filter umum
  • Primary key

Hindari indeks

  • Tabel kecil
  • Kolom dengan banyak null
  • Tabel sering diperbarui
    • Indeks jadi terfragmentasi
    • Tulis data di dua tempat
Meningkatkan Performa Kueri di PostgreSQL

Tabel sering diperbarui

Dua tabel. Tabel indeks berisi kolom recipe dan kolom pointer, dari _12 hingga _19 berurutan. Tabel dengan indeks sama dengan tabel di kiri dengan kolom pointer tambahan. Nilai di kolom pointer sama dengan yang di Tabel Indeks. Panah menunjukkan empat nilai untuk spaghetti and meatballs di Tabel Indeks menuju empat nilai yang sama di Tabel dengan Indeks. Tiga nilainya berurutan di atas Tabel Indeks. Satu spaghetti and meatballs di bagian bawah Tabel Indeks, sesuai dengan entri basil baru di Tabel dengan indeks.

Meningkatkan Performa Kueri di PostgreSQL

Menilai kueri dengan indeks

Perencana kueri

Beberapa koki di sekitar panci besar 

EXPLAIN
SELECT * 
FROM cookbook

 

Rencana kueri

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • Perkiraan biaya (waktu)
Meningkatkan Performa Kueri di PostgreSQL

Ayo berlatih!

Meningkatkan Performa Kueri di PostgreSQL

Preparing Video For Download...