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:

• zapytanie, które ograniczenia dla poszczególnych kolumn łączy operatorem AND,
• j.w., ale z operatorem 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:

• podadzą zamówienia, których miejca dostaw są nie dalej niż 10 jednostek od centrum miasta, czyli punktu (50, 50),
• podadzą wszystkie zamówienia dla północno-zachodniej ćwiartki miasta.

Utwórz indeks typu GiST wspierający ww. zapytania i powtórz analizę planu i pomiar czasu.