Celem laboratorium jest zapoznanie z działaniem indeksowania w relacyjnych bazach danych.
W archiwum cukiernia.tgz znajdują się 2 pliki:
1-ascii.sql
- DDL, oraz podstawowe dane,3-ascii.sql
- rozszerzone dane.Załaduj obydwa pliki do swojej bazy PostgreSQL na odpowiednim serwerze (borg/charon). Możesz utworzyć osobny schemat, tak aby oddzielić dane od danych z innych ćwiczeń.
Każde ćwiczenie dobrze zaczynać z bazą bez indeksów, należy je więc pousuwać (DROP INDEX
).
Proszę zapisywać plany i czasy wykonania dla wszystkich testowanych zapytań.
Przypomnij sobie z wykładu, jakie kolejne bloki funkcjonalne przetwarzają zapytanie i jak jest ono przekształcane. Podpowiedź: http://www.postgresql.org/docs/9.3/static/query-path.html
Polecenie ANALYZE
zbiera statystyki dotyczące rozkładu wartości w kolumnach tabel i zapisuje wyniki w tabeli systemowej pg_statistic
. Aby planer był w stanie optymalnie określić plan wykonania zapytania, wcześniej musi zostać wykonana operacja ANALYZE na tabeli i istotnych kolumnach.
Polecenie EXPLAIN
wyświetla plan wykonania zapytania. Dodanie opcji VERBOSE
powoduje, że wyniki są wyświetlane w postaci rozszerzonej, a opcja ANALYZE
automatycznie wykonuje polecenie ANALYZE
oraz samo zapytanie, w związku z czym może dostarczyć również statystyk dotyczących rzeczywistego przebiegu wykonania zapytania i jego czasu.
Wykonaj w tabeli zamowienia
zapytanie, które wyświetla wszystkie zamówienia na kompozycję buk1
. Sprawdź plan wykonania zapytania i zapisz czas wykonania.
Dodaj do tabeli zamowienia
indeks oparty o haszowanie, który pozwoli na przyspieszenie powyższego zapytania. Sprawdź plan i zapisz czas.
Uwaga: w ćwiczeniu korzystaj z porównywania wartości, nie z dopasowywania wzorców.
Usuń indeks utworzony w poprzednim kroku i utwórz analogiczny indeks oparty o b-drzewa. Powtórz poprzednie zapytanie i zapisz wyniki (plan i czas).
Wykonaj zapytanie wyświetlające zamówienia na wszystkie kompozycje, których ID zaczyna się na litery stojące w alfabecie przed „b”. Czy indeks jest wykorzystywany?
Wykonaj zapytanie o pozostałe kompozycje, czyli „b” i kolejne litery. Czy teraz indeks został użyty?
Wymuś stosowanie indeksów przez wyłączenie parametru enable_seqscan
:
SET ENABLE_SEQSCAN TO OFF;
Zapytanie to sprawia, że PostgreSQL „chętniej” korzysta z indeksów – ta forma optymalizacji konfigurajci bazy może być użyteczna w praktyce, gdy baza jest w pamięci masowej o niskim czasie wyszukiwania, np. na dysku SSD bądź na dysku z dużym buforem. Powtórz dwa poprzednie zapytania i porównaj plany oraz czasy wykonania.
Załóż indeks dla kolumny uwagi
i wykonaj zapytanie o wszystkie zamówienia, których uwagi zaczynają się od znaków „do”. Czy indeks jest wykorzystywany?
Usuń indeks i utwórz nowy, ale tym razem określając dla niego jawnie klasę operatorów varchar_pattern_ops
(więcej informacji):
CREATE INDEX zamowienia_uwagi ON zamowienia (uwagi varchar_pattern_ops);
Powtórz ćwiczenie i porównaj plany oraz czas wykonania.
Załóż indeks wielokolumnowy obejmujący kolumny idklienta
, idodbiorcy
oraz idkompozycji
.
Wybierz po jednej wartości występującej w tych kolumnach (np. z jednego przykładowego rekordu) i wykonaj:
AND
,OR
.Porównaj plany wykonania.
Wykonaj teraz zapytanie o wszystkie zamówienia na kompozycję buk1
.
Usuń indeks i załóż po jednym indeksie na www. kolumny. Wykonaj trzy powyższe zapytania i porównaj wyniki.
Nie usuwaj indeksów przed następnym ćwiczeniem.
Wykonaj zapytanie zwracające wszystkie zamówienia posortowane według ID kompozycji. Czy indeks został wykorzystany?
Teraz usuń indeks i powtórz zapytanie. Porównaj wyniki.
Usuń wszystkie indeksy.
Utwórz indeks na kolumnie idklienta
, ale tylko dla zamówień zapłaconych. Sprawdź jego działanie poprzez pobranie wszystkich zapłaconych zamówień wybranego klienta. Powtórz zapytanie dla zamówień niezapłaconych.
Teraz oblicz sumę wszystkich zamówień niezapłaconych. Czy zapytanie korzysta z indeksu?
Utwórz indeks oraz zapytanie, które pozwolą na wyszukiwanie klientów z miasta zaczynającego się od określonego ciągu znaków (np. „krak”), niezależnie od wielkości znaków (czyli „krak”=„Krak”=„KRAK”, itd.).
Proszę dodać do tabeli zamowienia
kolumnę lokalizacja
typu point
(zawierać będzie współrzędne miejsca dostawy) i wypełnić je punktami o losowych współrzędnych na płaszczyźnie (0,0)–(100,100):
ALTER TABLE zamowienia ADD COLUMN lokalizacja point; UPDATE zamowienia SET lokalizacja=point(random()*100, random()*100);
Napisz zapytania, które:
Utwórz indeks typu GiST wspierający ww. zapytania i powtórz analizę planu i pomiar czasu.