
-- ukazkovy dotaz
SELECT person.name, movie.year, count(*) AS c
FROM   movie, director, person
WHERE  person.person_id = director.person_id
AND    movie.movie_id = director.movie_id
GROUP BY person.name, movie.year
HAVING count(*) > 1
ORDER BY c ASC;

-- EXPLAIN vs EXPLAIN ANALYZE (s ANALYZE se dotaz i provede)
EXPLAIN
SELECT person.name, movie.year, count(*) AS c
       FROM   movie, director, person
       WHERE  person.person_id = director.person_id
         AND    movie.movie_id = director.movie_id
       GROUP BY person.name, movie.year
       HAVING count(*) > 1
       ORDER BY c ASC;


EXPLAIN ANALYZE
SELECT person.name, movie.year, count(*) AS c
       FROM   movie, director, person
       WHERE  person.person_id = director.person_id
         AND    movie.movie_id = director.movie_id
       GROUP BY person.name, movie.year
       HAVING count(*) > 1
       ORDER BY c ASC;


-- VERBOSE

EXPLAIN ANALYZE VERBOSE
   SELECT person.name, movie.year, count(*) AS c
       FROM   movie, director, person
       WHERE  person.person_id = director.person_id
         AND    movie.movie_id = director.movie_id
       GROUP BY person.name, movie.year
       HAVING count(*) > 1
       ORDER BY c ASC;

-- Na prednasce jste videli mnoho dalsich prikladů, co vse planovac můze delat. Ukazky mate v dodanych skriptech - projdete si je!


-----------------------------

-- Planovac sam provadi optimalizace 

EXPLAIN VERBOSE SELECT * FROM (director NATURAL JOIN person) WHERE person.birth > 1950;

EXPLAIN VERBOSE SELECT * FROM director NATURAL JOIN (SELECT * FROM person  WHERE  birth > 1950) AS a;




-----------------------------

-- Planovac pri planovani vyuziva i různa metadata o databazi.
-- Mnoho různych informaci - vizte dokumentaci

--napr.

SELECT * FROM pg_stat_all_indexes;

SELECT * FROM pg_stat_all_tables;

SELECT * FROM pg_statio_all_tables;


-----------------------------
-- Aktualizace statistik pomoci ANALYZE
-- pokud dojde k velkym zmenam dat, nebo primo struktury tabulky, tak se můze vyplatit vynucena aktualizace metadat

ANALYZE movie;
ANALYZE VERBOSE director;


-- (vizte dokumentaci)
