Indexy v PostgreSQL

Indexací se rozumí rodina optimalizačních technik sloužících k urychlení vykonávání SQL dotazů. Nejprve si připomeňme, jak PostgreSQL vykonává SQL dotazy. Při vyhodnocení SELECT dotazu plánovač dotazů v PostgreSQL serveru sestaví tzv. query plan, tedy plán, jak se dotaz vykoná. Tento plán si můžeme prohlédnout pomocí klíčového slova EXPLAIN před příslušným dotazem, např. (viz. příklad databáze dále v tomto cvičení)

EXPLAIN ANALYZE SELECT count(*) FROM mytable;

dává výsledek

                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1693.00..1693.01 rows=1 width=0) (actual time=251.654..251.655 rows=1 loops=1)
   ->  Seq Scan on mytable  (cost=0.00..1443.00 rows=100000 width=0) (actual time=0.025..127.282 rows=100000 loops=1)
 Total runtime: 251.721 ms

Vidíme zde, že se provádí agregace (Aggregate) na základě výsledků sekvenčního scanu tabulky mytable.

Plánovač dotazů se snaží optimalizovat vyhodnocení dotazu. Využívá pro to různých technik, např. odhadu počtu řádků v dané tabulce. Tyto odhady je třeba čas od času aktualizovat příkazem ANALYZE. Je však možné v konfiguraci PostgreSQL zapnout AUTOVACUUM démon, který se o to v pravidelných intervalech postará.

Malá vsuvka o tom, proč nejsou statistiky aktualizovány při každé změně dat v DB. Důvodem je to, že pri DELETE/UPDATE se musi zachovat historicke (puvodni) verze aktualizovanych/mazanych radku pro transakce, ktere si drive zamkli tyto radky pro cteni. Podobne 'mrtve radky' mohou vzniknout pri rollbacku transakce. K temto mrtvym radkum se neni mozne jiz dostat, je proto treba je smazat, aby nezabiraly misto (to se provede příkazem VACUUM). Tyto řádky však nejsou z výkonnostních důvodů mazány automaticky - zbytečně by to zabíralo čas.

Příklad indexace

V postgresql.conf je nastaveno “autovacuum off”, takže statistiky nejsou aktualizovány automaticky. Vytvoříme testovací tabulku. Vsimnete si, ze se automaticky vytvori index na primarni klic.

CREATE TABLE mytable( id serial PRIMARY KEY, value int );

Vygenerujeme testovaci data

INSERT INTO mytable(value) SELECT trunc(1000*random()) FROM generate_series(1,100000);

V tomto okamziku nejsou statistiky spocitany, nebot autovacuum=off. Overime takto:

SELECT relname, reltuples FROM pg_class WHERE relname='mytable';

Vyčistíme staré řádky a aktualizujeme statistiky:

VACUUM ANALYZE;

A znovu podivame se na aktualizovane statistiky

SELECT relname, reltuples FROM pg_class WHERE relname='mytable';

Pouzijeme-li primarni klic jako filtr, vidime, ze se pouzije index implicitne vygenerovany pri tvorbe primarniho klice (index scan) misto mene sekvencniho scanu. Index scan se vyplati, je-li filtr hodne selektivni (napr. zde 100/100000).

EXPLAIN ANALYZE SELECT count(*) FROM mytable WHERE id=100;

Úkol 1: Srovnejte s dotazem, ktery filtruje podle sloupce, ktery nema index. Jaky typ scanu se pouziva a proc ? Srovnejte runtime casy v obou prikladech.<file>EXPLAIN ANALYZE SELECT count(*) FROM mytable WHERE value=100;</file>

Zkusime vytvorit index pomocí konstrukce CREATE INDEX:

CREATE INDEX i on mytable(value);

Úkol 2: Ověřte, jak se změní vyhodnocení předchozího SELECTu.

Lepší výsledek indexu je dán nejen indexem, ale i částečně cachováním výsledků předchozího dotazu (bez indexace) v PostgreSQL. Můžete si prohlédnout statistiky počty jednotlivých typů scanů/přístupů na disk v pg_stat_user_tables, resp. pg_stat_user_tables. Před každým takto testovaným dotazem je však třeba tyto statistiky vynulovat (může pouze administrátor databáze) pomocí

SELECT pg_stat_reset();

Úkol 3: Indexy mají výhodu pouze, má-li dotaz, který index využívá, vysokou selektivitu. V opačném případě může plánovač zvolit sekvenční scan navzdory existujícímu indexu. Abychom to ověřili, zkuste smazat všechna data z tabulky a vygenerovat data ještě jednou s většími množinami stejných hodnot (např. 10*random()). Pote naleznete hranici X filtru WHERE value < X v predchozim dotazu, pri kterem k prepnuti na sekvencni scan dojde. Porovnejte časy vyhodnocovaní dotazu v hranicnich pripadech.

Unikátní omezení patří k nejčastějším. Lze je vytvořit v zásadě třemi způsoby: (i) automaticky vytvořením primárního klíče, (ii) klíčovým slovem UNIQUE za definicí atributu v CREATE TABLE, (iii) vytvoření indexu CREATE UNIQUE INDEX, který bude unikátnost kontrolovat.

Úkol 4: Napadá vás, jaký je rozdíl mezi označením atributu UNIQUE a vytvořením unikátního indexu CREATE UNIQUE INDEX na tento atribut (zkuste si oba vytvořit) ? Který způsob je z hlediska údržby databáze výhodnější, a proč ?

Lze vytvářet i vícesloupcové indexy. Pokud vytvoříme např. index pro (id,value) v příkladě nahoře, výsledný index bude patrně menší než dva samostatné indexy na id a na value, ovšem jeho použitelnost bude pouze na dotazy, které buď používají filtr přes 'id', nebo přes id a value, ovšem nikoliv přes 'value' samostatně.

Úkol 5: Vyzkoušejte si, jak se liší doba vyhodnocení různých typů dotazů pro vícesloupcový index a pro více jednosloupcových indexů.

Transakce v PostgreSQL

Ukázka zajištění automicity operace

Vytvořte si tabulku representující zůstatky na běžném bankovním účtu

CREATE TABLE zustatek (
   id_ucet      int           NOT NULL UNIQUE,
   zustatek_kc  numeric(10,2) NOT NULL DEFAULT 10 CHECK (zustatek_kc>0),
   PRIMARY KEY (id_ucet));

Zadejte si několik účtů s různými zůstatky a implementujte převod peněz z jednoho účtu na druhý.

UPDATE zustatek SET zustatek_kc = zustatek_kc + (nejaka castka) WHERE id_ucet = (ucet1);
UPDATE zustatek SET zustatek_kc = zustatek_kc - (nejaka castka) WHERE id_ucet = (ucet2);

Pomocí převodu otestujte funkčnost integritního omezení CHECK (zustatek_kc>0)

Pokud není využito transakce (logicky atomické operace, která je v databázi řešena pomocí dvou a více dotazů), může být chování systému krajně nebezpečné. Proto je v takových situacích nutné použít transakcí. Otestujte předchozí převod peněz pomocí uzavření do transakce (v SQL pomocí BEGIN a COMMIT/ROLLBACK) a diskutujte další výhody jejich použití.

Ukázka ACID

Úkol 6: Co znamenají vlastnosti ACID, a jakými technickými prostředky je každé z nich v relačních databázích dosaženo ?

Spusťte si dva DB klienty C1 a C2, napr. PgAdmin, psql, a připojte je k vasi databazi.

Úkol 7: Jaký minimální stupeň izolovanosti musí mít databáze nastaven, pokud se nezměnil výsledek SELECT dotazů pro C1 v následujícím rozvrhu:

C1: BEGIN;                                               
C2: BEGIN;
C1: SET TRANSACTION ISOLATION LEVEL .....
C1: SELECT zustatek_kc FROM zustatek WHERE id_ucet = (cislouctu);
C2: UPDATE zustatek SET zustatek_kc = zustatek_kc + 100;
C1: SELECT zustatek_kc FROM zustatek WHERE id_ucet = (cislouctu);
C1: COMMIT;
C2: COMMIT;

nebo

C1: BEGIN;                                               
C2: BEGIN;
C1: SET TRANSACTION ISOLATION LEVEL .....
C1: SELECT count(value) FROM mytable WHERE value = 100;
C2: INSERT INTO mytable(value) VALUES (100);
C1: SELECT count(value) FROM mytable WHERE value = 100;
C1: COMMIT;
C2: COMMIT;

Úkol 8: Nastavte vhodný stupeň izolovanosti tak, aby oba dva SELECTy v C1 v následujícím rozvrhu vracely stejný výsledek. Stupeň izolovanosti se nastavuje na začátku transakce pomocí SQL příkazu SET TRANSACTION ISOLATION LEVEL <X>, kde <X> je SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED; viz SET TRANSACTION .

C1: BEGIN;                                               
C2: BEGIN;
C1: SET TRANSACTION ISOLATION LEVEL .....
C1: SELECT zustatek_kc FROM zustatek WHERE id_ucet = (cislouctu);
C2: UPDATE zustatek SET zustatek_kc = zustatek_kc + 100;
C1: SELECT zustatek_kc FROM zustatek WHERE id_ucet = (cislouctu);
C2: COMMIT;
C1: SELECT zustatek_kc FROM zustatek WHERE id_ucet = (cislouctu);
C1: COMMIT;