/* 32a*/ EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF)
SELECT MIN(lt.link) AS link_type,
       MIN(t1.title) AS first_movie,
       MIN(t2.title) AS second_movie
FROM keyword AS k,
     link_type AS lt,
     movie_keyword AS mk,
     movie_link AS ml,
     title AS t1,
     title AS t2
WHERE k.keyword ='10,000-mile-club'
  AND mk.keyword_id = k.id
  AND t1.id = mk.movie_id
  AND ml.movie_id = t1.id
  AND ml.linked_movie_id = t2.id
  AND lt.id = ml.link_type_id
  AND mk.movie_id = t1.id;
/*
QUERY PLAN                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=40457.16..40457.17 rows=1 width=96) (actual time=243.879..246.113 rows=1.00 loops=1)
   Buffers: shared hit=25651
   ->  Gather  (cost=40457.12..40457.13 rows=4 width=96) (actual time=243.690..246.107 rows=5.00 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         Buffers: shared hit=25651
         ->  Partial Aggregate  (cost=40457.11..40457.12 rows=1 width=96) (actual time=234.208..234.209 rows=1.00 loops=5)
               Buffers: shared hit=25651
               ->  Nested Loop  (cost=40187.13..40457.10 rows=1 width=46) (actual time=234.203..234.205 rows=0.00 loops=5)
                     Buffers: shared hit=25651
                     ->  Nested Loop  (cost=40186.70..40452.90 rows=1 width=33) (actual time=234.203..234.205 rows=0.00 loops=5)
                           Buffers: shared hit=25651
                           ->  Nested Loop  (cost=40186.27..40452.42 rows=1 width=24) (actual time=234.203..234.204 rows=0.00 loops=5)
                                 Buffers: shared hit=25651
                                 ->  Parallel Hash Join  (cost=40186.14..40452.26 rows=1 width=16) (actual time=234.202..234.203 rows=0.00 loops=5)
                                       Hash Cond: (ml.movie_id = mk.movie_id)
                                       Rows Removed by Hash Matching: 5999
                                       ->  Parallel Seq Scan on movie_link ml  (cost=0.00..237.99 rows=7499 width=12) (actual time=0.007..0.460 rows=5999.40 loops=5)
                                             Buffers: shared hit=163
                                       ->  Parallel Hash  (cost=40186.04..40186.04 rows=8 width=4) (actual time=232.523..232.524 rows=0.20 loops=5)
                                             Buckets: 1024  Batches: 1  Memory Usage: 40kB
                                             Buffers: shared hit=25488
                                             ->  Parallel Hash Join  (cost=1427.29..40186.04 rows=8 width=4) (actual time=230.095..232.201 rows=0.20 loops=5)
                                                   Hash Cond: (mk.keyword_id = k.id)
                                                   Rows Removed by Hash Matching: 904786
                                                   Buffers: shared hit=25488
                                                   ->  Parallel Seq Scan on movie_keyword mk  (cost=0.00..35789.82 rows=1130982 width=8) (actual time=0.009..69.494 rows=904786.00 loops=5)
                                                   ->  Parallel Hash  (cost=1427.28..1427.28 rows=1 width=4) (actual time=7.464..7.464 rows=0.20 loops=5)
                                                          ->  Parallel Seq Scan on keyword k  (cost=0.00..1427.28 rows=1 width=4) (actual time=7.156..7.187 rows=0.20 loops=5)
                                                               Filter: (keyword = '10,000-mile-club'::text)
                                  ->  Index Scan using link_type_pkey on link_type lt  (cost=0.14..0.16 rows=1 width=16) (never executed)
                                       Index Cond: (id = ml.link_type_id)
                                       Index Searches: 0
                           ->  Index Scan using title_pkey on title t1  (cost=0.43..0.49 rows=1 width=21) (never executed)
                                 Index Cond: (id = mk.movie_id)
                                 Index Searches: 0
                     ->  Index Scan using title_pkey on title t2  (cost=0.43..4.20 rows=1 width=21) (never executed)
                           Index Cond: (id = ml.linked_movie_id)
                           Index Searches: 0
 Planning:
   Buffers: shared hit=32
 Planning Time: 4.602 ms
 Execution Time: 246.284 ms
(49 rows)
*/

-- Indexes created:

CREATE INDEX ind_0 ON movie_keyword (keyword_id,movie_id);
CREATE INDEX ind_2 ON movie_link(movie_id);

-- Final result:

/*
/*
 Finalize Aggregate (actual rows=1.00 loops=1)
   ->  Gather (actual rows=5.00 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Partial Aggregate (actual rows=1.00 loops=5)
               ->  Nested Loop (actual rows=0.00 loops=5)
                     ->  Nested Loop (actual rows=0.00 loops=5)
                           ->  Nested Loop (actual rows=0.00 loops=5)
                                 ->  Nested Loop (actual rows=0.00 loops=5)
                                       ->  Nested Loop (actual rows=0.20 loops=5)
                                             ->  Parallel Seq Scan on keyword k (actual rows=0.20 loops=5)
                                                   Filter: (keyword = '10,000-mile-club'::text)
                                                   Rows Removed by Filter: 26834
                                             ->  Index Only Scan using ind_0 on movie_keyword mk (actual rows=1.00 loops=1)
                                                   Index Cond: (keyword_id = k.id)
                                                   Heap Fetches: 0
                                                   Index Searches: 1
                                       ->  Index Scan using ind_2 on movie_link ml (actual rows=0.00 loops=1)
                                             Index Cond: (movie_id = mk.movie_id)
                                             Index Searches: 1
                                 ->  Index Scan using link_type_pkey on link_type lt (never executed)
                                       Index Cond: (id = ml.link_type_id)
                                       Index Searches: 0
                           ->  Index Scan using title_pkey on title t1 (never executed)
                                 Index Cond: (id = mk.movie_id)
                                 Index Searches: 0
                     ->  Index Scan using title_pkey on title t2 (never executed)
                           Index Cond: (id = ml.linked_movie_id)
                           Index Searches: 0
 Planning Time: 6.217 ms
 Execution Time: 16.494 ms
*/
*/