Cvičení 10

Témata

• Agregace a seskupování
• Agregační funkce v PostgreSQL
• Klauzule GROUP BY a HAVING výrazu SELECT
• Planování a analýza dotazu
• Indexy

Agregace a shlukování

• Opakování z přednášky:
  • Co je to agregace?
  • Co je to seskupování?
  • Jak to funguje?

Agregační funkce v PostgreSQL

• Funkce očekávající buďto jeden sloupec dané tabulky, nebo všechny sloupce dané tabulky.
• Vrací jednu hodnotu skalárního typu.
• Příklady: max, min, count, sum, …
• Další v dokumentaci.

Ukázka

• Pár agregačních funkcí
• GROUP BY
• HAVING
• Ukázka

Plánování

Všechny zde uvedené informace jsou specifické pro PostgreSQL. U jiných databázových systémů to může fungovat jinak - jsou to záležitosti fyzické vrstvy / implementace systému.

Na přednášce jste již viděli část databáze imdb. Vyzkoušíme si na ní, co vše se můžeme dozvědět o vykonávání námi napsaného dotazu.

Nejprve potřebné zdroje :

• Příkaz EXPLAIN
• Použití EXPLAIN
• Statistická data o databázi
• Příkaz ANALYZE
• Zdroj IMDB dat
• Ukázka

Indexy

Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly. (Dokumentace PostgreSQL)

Stojí za to pročíst

• Starší, ale velmi dobrý článek o indexech v PostgreSQL (navíc česky).
• Příkaz CREATE INDEX
• Analýza vlivu indexu
• Vícesloupcové indexy
• Indexy na výraz
• Dokumentace k indexům
• Ukázka

Úkoly

1. Agregace: zadání v souboru.
2. Import: Seznamte se se strukturou (omezené) databáze imdb z přednášky, přičemž postupujte následovně:
   • Spusťte import ze skriptů dodaných na přednášce.
   • Nejprve pro orientaci použijte nástroje, které nabízí přímo PostgreSQL (psql; informace o databázi; informace o tabulkách).
   • Teprve poté se podívejte na to, co importovací skript přesně obsahuje.
   • Tj cílem je něco zjistit o existující databázi, u které neznáte vytvářecí skript, a pak si získané poznatky ověřit.
3. Plánovač: Vyzkoušejte si různé formy příkazu EXPLAIN na různých dotazech nad databázemi imdb a zaměstnanců (tam máte spoustu dotazů z minulých hodin).
   • Začněte něčím jednoduchým - SELECT z jedné tabulky s WHERE kaluzulí (zkuste klauzule, které splní mnoho n-tic, i klauzule, které jich splní jen velmi málo).
   • Poté zkuste nějaký složitější dotaz, který například spojuje data z více tabulek s různými restrikcemi, projekcemi, agregacemi atd.
4. Zkuste i verze příkazu EXPLAIN s ANALYZE, VERBOSE, …
5. K dispozici máte data o zaměstancích v původní verzi a ve verzi bez indexů. Naimportujte si je do databáze (ideálně každou do jiného schématu; u verze bez indexu je to nachystáno) a porovnejte vykonávání různých dotazů.
6. V neindexované verzi databáze z předchozího příkladu vytvořte ručně indexy, které vykonávání vašich dotázů co nejvíce zefektivní.
7. (Může být náročnější a vyžadovat dost samostudia. Je to ale velmi praktická znalost.) Najděte si online nějakou jinou (doporučuji menší) databázi a zkuste si ji naimportovat do postgresu. Bývají dostupné přímo databáze vytvořené příkazem pg_dump, tam je import velmi snadný. Jsou ale i různé jiné formáty, ze kterých můžete chtít data do databáze dostat. Např. imdb je dostupná v csv souborech a data vyžadují dost číštění (při rozumných integritních omezeních).