Używanie i tworzenie indeksów

Optymalizacja wydajności zapytań w PostgreSQL

Amy McCarty

Instructor

Omówienie indeksów

Co to jest

  • Metoda tworzenia posortowanych kluczy kolumn w celu przyspieszenia wyszukiwania
  • Podobny do indeksu w książce
  • Odwołanie do lokalizacji danych

Dlaczego

  • Szybsze zapytania

Gdzie

  • Często filtrowane kolumny
  • Klucz główny
Optymalizacja wydajności zapytań w PostgreSQL

Przykład indeksu

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'
Optymalizacja wydajności zapytań w PostgreSQL

Indeks jako klucz i wskaźnik

Dwie tabele. Tabela indeksu zawiera kolumnę recipe oraz kolumnę wskaźników od _12 do _18. Tabela z indeksem to ta sama tabela z dodatkową kolumną wskaźników. Strzałki pokazują, jak cztery wartości fried rice w tabeli indeksu odpowiadają czterem wartościom fried rice w tabeli z indeksem.

Optymalizacja wydajności zapytań w PostgreSQL

Znajdowanie istniejących indeksów

pg_tables
  • Podobny do information_schema
    • specyficzny dla Postgres
  • Metadane o bazie danych
Optymalizacja wydajności zapytań w PostgreSQL

Znajdowanie istniejących indeksów

pg_tables
  • Podobny do information_schema
    • specyficzny dla Postgres
  • Metadane o bazie danych

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Optymalizacja wydajności zapytań w PostgreSQL

Tworzenie indeksu

Tabela dinner z kolumnami ingredient, recipe i serving size. tomatoes - spaghetti & meatballs - 4. green onions - fried rice - 2. eggs - fried rice - 2. ground beef - spaghetti & meatballs - 4. pasta - spaghetti & meatballs - 4. rice - fried rice - 2. soy sauce - fried rice - 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
Optymalizacja wydajności zapytań w PostgreSQL

Używać czy nie używać

Kiedy używać indeksu

  • Duże tabele
  • Częste warunki filtrowania
  • Klucz główny

Kiedy unikać indeksu

  • Małe tabele
  • Kolumny z wieloma wartościami null
  • Często aktualizowane tabele
    • Indeks ulega fragmentacji
    • Dane zapisywane w dwóch miejscach
Optymalizacja wydajności zapytań w PostgreSQL

Często aktualizowane tabele

Dwie tabele. Tabela indeksu zawiera kolumnę recipe oraz kolumnę wskaźników od _12 do _19. Tabela z indeksem to ta sama tabela z dodatkową kolumną wskaźników. Strzałki pokazują, jak cztery wartości spaghetti and meatballs w tabeli indeksu odpowiadają czterem wartościom w tabeli z indeksem. Trzy wartości są zgrupowane na górze tabeli indeksu. Jeden wpis spaghetti and meatballs znajduje się na dole, odpowiadając nowemu wpisowi basil w tabeli z indeksem.

Optymalizacja wydajności zapytań w PostgreSQL

Ocena zapytań z indeksem

Planista zapytań

Kilku kucharzy przy dużym garnku 

EXPLAIN
SELECT * 
FROM cookbook

 

Plan zapytania

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • Szacowany koszt (czas)
Optymalizacja wydajności zapytań w PostgreSQL

Czas na ćwiczenia!

Optymalizacja wydajności zapytań w PostgreSQL

Preparing Video For Download...