
-- INDEXY

-- žádný 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 použití
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;



-- implicitní b-tree index na jmeno (protože 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
);

-- implicitní vícesloupcový 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 každý constriant vytváří implicitně indexy (jen tam, kde to má smysl - tj u unique a primary key se musí kontrolovat, že tam hodnota ještě není.)

Ddokumentace:

 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.
*/

-- zrušení právě vytvořeného (implicitního) indexu

ALTER TABLE lide DROP CONSTRAINT lide_jmeno_vek_key;



--tentýž index přidáme zpět explicitně
CREATE UNIQUE INDEX lide_jmeno_vek_idx ON lide (jmeno, vek);  --pokud nespecifikujete typ indexu použije se b-tree

-- zrušení ručně 
DROP INDEX lide_jmeno_vek_idx;

 -- další 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);

-- částečný 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 >= 25 ;

-- index na výsledek výrazu
CREATE INDEX foo_bmi_idx ON lide ((hmotnost/(vyska * vyska)));  -- všimněte si dvou párů závorek 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 použití
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 >= 25;  -- zkuste libovolne jiné číslo

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