
-- INDEXY

-- zadny index
DROP TABLE IF EXISTS lide;
DROP VIEW IF EXISTS lide;
CREATE TABLE lide (
       jmeno TEXT NOT NULL,
       vek NUMERIC NOT NULL,
       vyska NUMERIC NOT NULL,
       hmotnost NUMERIC NOT NULL
);

-- data
INSERT INTO lide VALUES
('Jan', 30, 1.9, 100),
('Jana', 40, 1.7, 60),
('Eva', 15, 1.75, 65),
('Lada', 20, 1.9, 85),
('Eda',  15, 1.8, 125),
('Tom', 18, 1.88, 72)
;


-- a pouziti
SELECT * FROM lide WHERE jmeno = 'Tom';

SELECT * FROM lide WHERE vek >= 20;

SELECT * FROM lide WHERE hmotnost / (vyska * vyska) >= 28;


EXPLAIN ANALYZE SELECT * FROM lide WHERE jmeno = 'Tom';

EXPLAIN ANALYZE SELECT * FROM lide WHERE vek >= 20;

EXPLAIN ANALYZE SELECT * FROM lide WHERE hmotnost / (vyska * vyska) >= 28;



-- implicitni b-tree index na jmeno (protoze jmeno je primary key)
DROP TABLE lide;
CREATE TABLE lide (
       jmeno TEXT NOT NULL PRIMARY KEY,
       vek NUMERIC NOT NULL,
       vyska NUMERIC NOT NULL,
       hmotnost NUMERIC NOT NULL
);

-- implicitni vicesloupcovy b-tree index na dvojici (jmeno, vek)
DROP TABLE lide;
CREATE TABLE lide (
       jmeno TEXT NOT NULL,
       vek NUMERIC NOT NULL,
       vyska NUMERIC NOT NULL,
       hmotnost NUMERIC NOT NULL,
       UNIQUE (jmeno, vek)
);



/*
Ale pozor, ne kazdy constriant vytvari implicitne indexy (jen tam, kde to ma smysl - tj u unique a primary key se musi kontrolovat, ze tam hodnota jeste neni.)

Dokumentace:

 PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.


Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.
*/

-- zruseni prave vytvoreneho (implicitniho) indexu

ALTER TABLE lide DROP CONSTRAINT lide_jmeno_vek_key;



--tentyz index pridame zpet explicitne
CREATE UNIQUE INDEX lide_jmeno_vek_idx ON lide (jmeno, vek);  --pokud nespecifikujete typ indexu pouzije se b-tree

-- zruseni rucne 
DROP INDEX lide_jmeno_vek_idx;

 -- dalsi typy indexů
DROP TABLE lide;
CREATE TABLE lide (
       jmeno TEXT NOT NULL PRIMARY KEY,
       vek NUMERIC NOT NULL,
       vyska NUMERIC NOT NULL,
       hmotnost NUMERIC NOT NULL
);


-- hash index 
CREATE INDEX lide_jmeno_idx ON lide USING hash (jmeno);

-- castecny index
CREATE INDEX lide_vek_idx ON lide (vek)  WHERE vek <= 25 ;

--kombinace
CREATE INDEX lide_vek_hash_idx ON lide USING hash (vek)  WHERE vek >= 28 ;

-- index na vysledek vyrazu
CREATE INDEX foo_bmi_idx ON lide ((hmotnost/(vyska * vyska)));  -- vsimnete si dvou parů zavorek okolo

/*
dokumentace:

 The syntax of the CREATE INDEX command normally requires writing parentheses around index expressions ... The parentheses can be omitted when the expression is just a function call ...
*/


-- data
INSERT INTO lide VALUES
('Jan', 30, 1.9, 100),
('Jana', 40, 1.7, 60),
('Eva', 15, 1.75, 65),
('Lada', 20, 1.9, 85),
('Eda',  15, 1.8, 125),
('Tom', 18, 1.88, 72);



-- a pouziti
SELECT * FROM lide WHERE jmeno = 'Tom';

SELECT * FROM lide WHERE vek <= 25;

SELECT * FROM lide WHERE hmotnost / (vyska * vyska) >= 28;

EXPLAIN ANALYZE SELECT * FROM lide WHERE jmeno = 'Tom';

EXPLAIN ANALYZE SELECT * FROM lide WHERE vek <= 25;

EXPLAIN ANALYZE SELECT * FROM lide WHERE hmotnost / (vyska * vyska) >= 28;


ANALYZE lide;


EXPLAIN ANALYZE SELECT * FROM lide WHERE jmeno = 'Tom';

EXPLAIN ANALYZE SELECT * FROM lide WHERE vek <= 25;

EXPLAIN ANALYZE SELECT * FROM lide WHERE hmotnost / (vyska * vyska) >= 28;
