/* 6a */ EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF)
SELECT MIN(k.keyword) AS movie_keyword,
       MIN(n.name) AS actor_name,
       MIN(t.title) AS marvel_movie
FROM cast_info AS ci,
     keyword AS k,
     movie_keyword AS mk,
     name AS n,
     title AS t
WHERE k.keyword = 'marvel-cinematic-universe'
  AND n.name LIKE '%Downey%Robert%'
  AND t.production_year > 2010
  AND k.id = mk.keyword_id
  AND t.id = mk.movie_id
  AND t.id = ci.movie_id
  AND ci.movie_id = mk.movie_id
  AND n.id = ci.person_id;
/*
 Aggregate (actual rows=1.00 loops=1)
   ->  Gather (actual rows=6.00 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Nested Loop (actual rows=1.20 loops=5)
               ->  Parallel Hash Join (actual rows=244.80 loops=5)
                     Hash Cond: (ci.movie_id = t.id)
                     Rows Removed by Hash Matching: 7248624
                     ->  Parallel Seq Scan on cast_info ci (actual rows=7248868.80 loops=5)
                     ->  Parallel Hash (actual rows=2.20 loops=5)
                           Buckets: 1024  Batches: 1  Memory Usage: 40kB
                           ->  Nested Loop (actual rows=2.20 loops=5)
                                 ->  Nested Loop (actual rows=2.80 loops=5)
                                       ->  Parallel Seq Scan on keyword k (actual rows=0.20 loops=5)
                                             Filter: (keyword = 'marvel-cinematic-universe'::text)
                                             Rows Removed by Filter: 26834
                                       ->  Index Only Scan using ind_2 on movie_keyword mk (actual rows=14.00 loops=1)
                                             Index Cond: (keyword_id = k.id)
                                             Heap Fetches: 0
                                             Index Searches: 1
                                 ->  Index Scan using title_pkey on title t (actual rows=0.79 loops=14)
                                       Index Cond: (id = mk.movie_id)
                                       Filter: (production_year > 2010)
                                       Rows Removed by Filter: 0
                                       Index Searches: 14
               ->  Index Scan using name_pkey on name n (actual rows=0.00 loops=1224)
                     Index Cond: (id = ci.person_id)
                     Filter: (name ~~ '%Downey%Robert%'::text)
                     Rows Removed by Filter: 1
                     Index Searches: 1224
 Planning Time: 2.258 ms
 Execution Time: 1566.857 ms
 */
 
-- Indexes:
CREATE INDEX ind_1 ON cast_info(movie_id,person_id);


/*
 Aggregate (actual rows=1.00 loops=1)
   ->  Gather (actual rows=6.00 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Nested Loop (actual rows=1.20 loops=5)
               ->  Nested Loop (actual rows=244.80 loops=5)
                     Join Filter: (ci.movie_id = t.id)
                     ->  Nested Loop (actual rows=2.20 loops=5)
                           ->  Parallel Hash Join (actual rows=2.80 loops=5)
                                 Hash Cond: (mk.keyword_id = k.id)
                                 Rows Removed by Hash Matching: 904783
                                 ->  Parallel Seq Scan on movie_keyword mk (actual rows=904786.00 loops=5)
                                 ->  Parallel Hash (actual rows=0.20 loops=5)
                                       Buckets: 1024  Batches: 1  Memory Usage: 40kB
                                       ->  Parallel Seq Scan on keyword k (actual rows=0.20 loops=5)
                                             Filter: (keyword = 'marvel-cinematic-universe'::text)
                                             Rows Removed by Filter: 26834
                           ->  Index Scan using title_pkey on title t (actual rows=0.79 loops=14)
                                 Index Cond: (id = mk.movie_id)
                                 Filter: (production_year > 2010)
                                 Rows Removed by Filter: 0
                                 Index Searches: 14
                     ->  Index Only Scan using ind_2 on cast_info ci (actual rows=111.27 loops=11)
                           Index Cond: (movie_id = mk.movie_id)
                           Heap Fetches: 0
                           Index Searches: 11
               ->  Index Scan using name_pkey on name n (actual rows=0.00 loops=1224)
                     Index Cond: (id = ci.person_id)
                     Filter: (name ~~ '%Downey%Robert%'::text)
                     Rows Removed by Filter: 1
                     Index Searches: 1224
 Planning Time: 7.905 ms
 Execution Time: 220.021 ms
*/

-- Step 2: Indexes:

CREATE INDEX idx_4 ON movie_keyword(keyword_id,movie_id);

/*
 Aggregate (actual rows=1.00 loops=1)
   ->  Gather (actual rows=6.00 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Nested Loop (actual rows=1.20 loops=5)
               ->  Nested Loop (actual rows=244.80 loops=5)
                     Join Filter: (ci.movie_id = t.id)
                     ->  Nested Loop (actual rows=2.20 loops=5)
                           ->  Nested Loop (actual rows=2.80 loops=5)
                                 ->  Parallel Seq Scan on keyword k (actual rows=0.20 loops=5)
                                       Filter: (keyword = 'marvel-cinematic-universe'::text)
                                       Rows Removed by Filter: 26834
                                 ->  Index Only Scan using idx_4 on movie_keyword mk (actual rows=14.00 loops=1)
                                       Index Cond: (keyword_id = k.id)
                                       Heap Fetches: 0
                                       Index Searches: 1
                           ->  Index Scan using title_pkey on title t (actual rows=0.79 loops=14)
                                 Index Cond: (id = mk.movie_id)
                                 Filter: (production_year > 2010)
                                 Rows Removed by Filter: 0
                                 Index Searches: 14
                     ->  Index Only Scan using ind_2 on cast_info ci (actual rows=111.27 loops=11)
                           Index Cond: (movie_id = mk.movie_id)
                           Heap Fetches: 0
                           Index Searches: 11
               ->  Index Scan using name_pkey on name n (actual rows=0.00 loops=1224)
                     Index Cond: (id = ci.person_id)
                     Filter: (name ~~ '%Downey%Robert%'::text)
                     Rows Removed by Filter: 1
                     Index Searches: 1224
 Planning Time: 6.751 ms
 Execution Time: 25.694 ms
*/