/* 27c */ EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF)
SELECT MIN(cn.name) AS producing_company,
       MIN(lt.link) AS link_type,
       MIN(t.title) AS complete_western_sequel
FROM complete_cast AS cc,
     comp_cast_type AS cct1,
     comp_cast_type AS cct2,
     company_name AS cn,
     company_type AS ct,
     keyword AS k,
     link_type AS lt,
     movie_companies AS mc,
     movie_info AS mi,
     movie_keyword AS mk,
     movie_link AS ml,
     title AS t
WHERE cct1.kind = 'cast'
  AND cct2.kind LIKE 'complete%'
  AND cn.country_code !='[pl]'
  AND (cn.name LIKE '%Film%'
       OR cn.name LIKE '%Warner%')
  AND ct.kind ='production companies'
  AND k.keyword ='sequel'
  AND lt.link LIKE '%follow%'
  AND mc.note IS NULL
  AND mi.info IN ('Sweden',
                  'Norway',
                  'Germany',
                  'Denmark',
                  'Swedish',
                  'Denish',
                  'Norwegian',
                  'German',
                  'English')
  AND t.production_year BETWEEN 1950 AND 2010
  AND lt.id = ml.link_type_id
  AND ml.movie_id = t.id
  AND t.id = mk.movie_id
  AND mk.keyword_id = k.id
  AND t.id = mc.movie_id
  AND mc.company_type_id = ct.id
  AND mc.company_id = cn.id
  AND mi.movie_id = t.id
  AND t.id = cc.movie_id
  AND cct1.id = cc.subject_id
  AND cct2.id = cc.status_id
  AND ml.movie_id = mk.movie_id
  AND ml.movie_id = mc.movie_id
  AND mk.movie_id = mc.movie_id
  AND ml.movie_id = mi.movie_id
  AND mk.movie_id = mi.movie_id
  AND mc.movie_id = mi.movie_id
  AND ml.movie_id = cc.movie_id
  AND mk.movie_id = cc.movie_id
  AND mc.movie_id = cc.movie_id
  AND mi.movie_id = cc.movie_id;

-- Current analysis:
/*
	Aggregate  (cost=136746.56..136746.57 rows=1 width=96) (actual time=5325.574..5325.928 rows=1.00 loops=1)
	  ->  Nested Loop  (cost=106536.51..136746.55 rows=1 width=48) (actual time=5052.547..5325.644 rows=743.00 loops=1)
	        ->  Nested Loop  (cost=106536.38..136746.38 rows=1 width=52) (actual time=5052.539..5324.034 rows=1028.00 loops=1)
	              ->  Nested Loop  (cost=106536.24..136746.20 rows=1 width=44) (actual time=5052.528..5322.295 rows=1028.00 loops=1)
	                    ->  Nested Loop  (cost=106535.82..136745.74 rows=1 width=29) (actual time=5052.368..5314.203 rows=2598.00 loops=1)
	                          ->  Hash Join  (cost=106535.69..136745.42 rows=2 width=33) (actual time=4985.416..5309.587 rows=2729.00 loops=1)
	                                Hash Cond: (mc.movie_id = ml.movie_id)
	                                ->  Gather  (cost=0.01..25440.02 rows=1271919 width=12) (actual time=0.424..164.883 rows=1271989.00 loops=1)
	                                      Workers Planned: 4
	                                      Workers Launched: 4
	                                      ->  Parallel Seq Scan on movie_companies mc  (cost=0.00..25312.82 rows=317980 width=12) (actual time=0.660..170.327 rows=254397.80 loops=5)
	                                            Filter: (note IS NULL)
	                                            Rows Removed by Filter: 267428
	                                ->  Hash  (cost=106535.67..106535.67 rows=1 width=45) (actual time=4984.764..4985.037 rows=365.00 loops=1)
	                                      Buckets: 1024  Batches: 1  Memory Usage: 36kB
	                                      ->  Nested Loop  (cost=57478.12..106535.67 rows=1 width=45) (actual time=469.102..4984.863 rows=365.00 loops=1)
	                                            Join Filter: (t.id = ml.movie_id)
	                                            ->  Merge Join  (cost=57477.69..106535.17 rows=1 width=24) (actual time=469.068..4983.816 rows=365.00 loops=1)
	                                                  Merge Cond: (mk.movie_id = ml.movie_id)
	                                                  ->  Nested Loop  (cost=55006.02..348450.94 rows=6 width=16) (actual time=457.888..4969.900 rows=34.00 loops=1)
	                                                        Join Filter: (mk.movie_id = mi.movie_id)
	                                                        Rows Removed by Join Filter: 17866222
	                                                        ->  Nested Loop  (cost=55005.99..55669.35 rows=4 width=12) (actual time=381.005..382.585 rows=20.00 loops=1)
	                                                              ->  Merge Join  (cost=55005.85..55668.50 rows=17 width=16) (actual time=380.960..382.447 rows=20.00 loops=1)
	                                                                    Merge Cond: (cc.movie_id = mk.movie_id)
	                                                                    ->  Sort  (cost=14903.94..15241.65 rows=135086 width=12) (actual time=60.602..61.397 rows=9866.00 loops=1)
	                                                                          Sort Key: cc.movie_id
	                                                                          Sort Method: external merge  Disk: 2912kB
	                                                                          ->  Gather  (cost=0.01..1082.23 rows=135086 width=12) (actual time=0.216..22.975 rows=135086.00 loops=1)
	                                                                                Workers Planned: 4
	                                                                                Workers Launched: 3
	                                                                                ->  Parallel Seq Scan on complete_cast cc  (cost=0.00..1068.72 rows=33772 width=12) (actual time=0.012..11.740 rows=33771.50 loops=4)
	                                                                    ->  Sort  (cost=40101.91..40102.00 rows=34 width=4) (actual time=320.209..320.367 rows=56.00 loops=1)
	                                                                          Sort Key: mk.movie_id
	                                                                          Sort Method: quicksort  Memory: 385kB
	                                                                          ->  Gather  (cost=1368.30..40101.05 rows=34 width=4) (actual time=9.946..318.464 rows=10544.00 loops=1)
	                                                                                Workers Planned: 4
	                                                                                Workers Launched: 3
	                                                                                ->  Parallel Hash Join  (cost=1368.29..40101.04 rows=8 width=4) (actual time=6.163..314.888 rows=2636.00 loops=4)
	                                                                                      Hash Cond: (mk.keyword_id = k.id)
	                                                                                      ->  Parallel Seq Scan on movie_keyword mk  (cost=0.00..35763.82 rows=1130982 width=8) (actual time=0.093..100.097 rows=1130982.50 loops=4)
	                                                                                      ->  Parallel Hash  (cost=1368.28..1368.28 rows=1 width=4) (actual time=5.097..5.097 rows=0.25 loops=4)
	                                                                                            Buckets: 1024  Batches: 1  Memory Usage: 40kB
	                                                                                            ->  Parallel Seq Scan on keyword k  (cost=0.00..1368.28 rows=1 width=4) (actual time=3.310..5.039 rows=0.25 loops=4)
	                                                                                                  Filter: (keyword = 'sequel'::text)
	                                                                                                  Rows Removed by Filter: 33542
	                                                              ->  Memoize  (cost=0.14..0.16 rows=1 width=4) (actual time=0.004..0.004 rows=1.00 loops=20)
	                                                                    Cache Key: cc.status_id
	                                                                    Cache Mode: logical
	                                                                    Hits: 18  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
	                                                                    ->  Index Scan using comp_cast_type_pkey on comp_cast_type cct2  (cost=0.13..0.15 rows=1 width=4) (actual time=0.020..0.021 rows=1.00 loops=2)
	                                                                          Index Cond: (id = cc.status_id)
	                                                                          Filter: ((kind)::text ~~ 'complete%'::text)
	                                                                          Index Searches: 2
	                                                        ->  Materialize  (cost=0.03..228028.73 rows=935441 width=4) (actual time=0.033..179.972 rows=893312.80 loops=20)
	                                                              Storage: Disk  Maximum Storage: 12800kB
	                                                              ->  Gather  (cost=0.03..219696.53 rows=935441 width=4) (actual time=0.594..2281.102 rows=936223.00 loops=1)
	                                                                    Workers Planned: 4
	                                                                    Workers Launched: 0
	                                                                    ->  Parallel Seq Scan on movie_info mi  (cost=0.02..219602.97 rows=233860 width=4) (actual time=0.247..2229.600 rows=936223.00 loops=1)
	                                                                          Filter: (info = ANY ('{Sweden,Norway,Germany,Denmark,Swedish,Denish,Norwegian,German,English}'::text[]))
	                                                                          Rows Removed by Filter: 13899497
	                                                  ->  Sort  (cost=2471.66..2546.65 rows=29997 width=8) (actual time=10.718..11.750 rows=30267.00 loops=1)
	                                                        Sort Key: ml.movie_id
	                                                        Sort Method: quicksort  Memory: 1472kB
	                                                        ->  Gather  (cost=0.01..241.00 rows=29997 width=8) (actual time=0.269..4.695 rows=29997.00 loops=1)
	                                                              Workers Planned: 4
	                                                              Workers Launched: 3
	                                                              ->  Parallel Seq Scan on movie_link ml  (cost=0.00..237.99 rows=7499 width=8) (actual time=0.013..1.606 rows=7499.25 loops=4)
	                                            ->  Index Scan using title_pkey on title t  (cost=0.43..0.49 rows=1 width=21) (actual time=0.002..0.002 rows=1.00 loops=365)
	                                                  Index Cond: (id = mk.movie_id)
	                                                  Filter: ((production_year >= 1950) AND (production_year <= 2010))
	                                                  Index Searches: 365
	                          ->  Index Scan using company_type_pkey on company_type ct  (cost=0.13..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=0.95 loops=2729)
	                                Index Cond: (id = mc.company_type_id)
	                                Filter: ((kind)::text = 'production companies'::text)
	                                Rows Removed by Filter: 0
	                                Index Searches: 2729
	                    ->  Index Scan using company_name_pkey on company_name cn  (cost=0.42..0.46 rows=1 width=23) (actual time=0.003..0.003 rows=0.40 loops=2598)
	                          Index Cond: (id = mc.company_id)
	                          Filter: (((country_code)::text <> '[pl]'::text) AND ((name ~~ '%Film%'::text) OR (name ~~ '%Warner%'::text)))
	                          Rows Removed by Filter: 1
	                          Index Searches: 2598
	              ->  Index Scan using link_type_pkey on link_type lt  (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=1028)
	                    Index Cond: (id = ml.link_type_id)
	                    Filter: ((link)::text ~~ '%follow%'::text)
	                    Index Searches: 1028
	        ->  Index Scan using comp_cast_type_pkey on comp_cast_type cct1  (cost=0.13..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=0.72 loops=1028)
	              Index Cond: (id = cc.subject_id)
	              Filter: ((kind)::text = 'cast'::text)
	              Rows Removed by Filter: 0
	              Index Searches: 1028
*/

-- nfiltered3:
/*
                                                                                     QUERY PLAN                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Nested Loop (actual rows=743.00 loops=1)
         Join Filter: (t.id = ml.movie_id)
         ->  Nested Loop (actual rows=743.00 loops=1)
               ->  Nested Loop (actual rows=1808.00 loops=1)
                     ->  Hash Join (actual rows=1913.00 loops=1)
                           Hash Cond: (mc.movie_id = ml.movie_id)
                           Rows Removed by Hash Matching: 1271952
                           ->  Gather (actual rows=1271989.00 loops=1)
                                 Workers Planned: 4
                                 Workers Launched: 4
                                 ->  Parallel Seq Scan on movie_companies mc (actual rows=254397.80 loops=5)
                                       Filter: (note IS NULL)
                                       Rows Removed by Filter: 267428
                           ->  Hash (actual rows=251.00 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 23kB
                                 ->  Nested Loop (actual rows=251.00 loops=1)
                                       ->  Merge Join (actual rows=270.00 loops=1)
                                             Merge Cond: (mk.movie_id = ml.movie_id)
                                             ->  Nested Loop (actual rows=28.00 loops=1)
                                                   ->  Nested Loop (actual rows=28.00 loops=1)
                                                         Join Filter: (mk.movie_id = mi.movie_id)
                                                         Rows Removed by Join Filter: 15033824
                                                         ->  Nested Loop (actual rows=17.00 loops=1)
                                                               ->  Merge Join (actual rows=20.00 loops=1)
                                                                     Merge Cond: (mk.movie_id = cc.movie_id)
                                                                     ->  Sort (actual rows=53.00 loops=1)
                                                                           Sort Key: mk.movie_id
                                                                           Sort Method: quicksort  Memory: 385kB
                                                                           ->  Gather (actual rows=10544.00 loops=1)
                                                                                 Workers Planned: 4
                                                                                 Workers Launched: 3
                                                                                 ->  Parallel Hash Join (actual rows=2636.00 loops=4)
                                                                                       Hash Cond: (mk.keyword_id = k.id)
                                                                                       Rows Removed by Hash Matching: 1128346
                                                                                       ->  Parallel Seq Scan on movie_keyword mk (actual rows=1130982.50 loops=4)
                                                                                       ->  Parallel Hash (actual rows=0.25 loops=4)
                                                                                             Buckets: 1024  Batches: 1  Memory Usage: 40kB
                                                                                             ->  Parallel Seq Scan on keyword k (actual rows=0.25 loops=4)
                                                                                                   Filter: (keyword = 'sequel'::text)
                                                                                                   Rows Removed by Filter: 33542
                                                                     ->  Sort (actual rows=9867.00 loops=1)
                                                                           Sort Key: cc.movie_id
                                                                           Sort Method: quicksort  Memory: 10366kB
                                                                           ->  Gather (actual rows=135086.00 loops=1)
                                                                                 Workers Planned: 4
                                                                                 Workers Launched: 0
                                                                                 ->  Parallel Seq Scan on complete_cast cc (actual rows=135086.00 loops=1)
                                                               ->  Memoize (actual rows=0.85 loops=20)
                                                                     Cache Key: cc.subject_id
                                                                     Cache Mode: logical
                                                                     Hits: 18  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                                                                     ->  Index Scan using comp_cast_type_pkey on comp_cast_type cct1 (actual rows=0.50 loops=2)
                                                                           Index Cond: (id = cc.subject_id)
                                                                           Filter: ((kind)::text = 'cast'::text)
                                                                           Rows Removed by Filter: 0
                                                                           Index Searches: 2
                                                         ->  Materialize (actual rows=884344.24 loops=17)
                                                               Storage: Memory  Maximum Storage: 44764kB
                                                               ->  Gather (actual rows=936223.00 loops=1)
                                                                     Workers Planned: 4
                                                                     Workers Launched: 3
                                                                     ->  Parallel Seq Scan on movie_info mi (actual rows=234055.75 loops=4)
                                                                           Filter: (info = ANY ('{Sweden,Norway,Germany,Denmark,Swedish,Denish,Norwegian,German,English}'::text[]))
                                                                           Rows Removed by Filter: 3474874
                                                   ->  Memoize (actual rows=1.00 loops=28)
                                                         Cache Key: cc.status_id
                                                         Cache Mode: logical
                                                         Hits: 26  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                                                         ->  Index Scan using comp_cast_type_pkey on comp_cast_type cct2 (actual rows=1.00 loops=2)
                                                               Index Cond: (id = cc.status_id)
                                                               Filter: ((kind)::text ~~ 'complete%'::text)
                                                               Index Searches: 2
                                             ->  Sort (actual rows=30172.00 loops=1)
                                                   Sort Key: ml.movie_id
                                                   Sort Method: quicksort  Memory: 1472kB
                                                   ->  Gather (actual rows=29997.00 loops=1)
                                                         Workers Planned: 4
                                                         Workers Launched: 0
                                                         ->  Parallel Seq Scan on movie_link ml (actual rows=29997.00 loops=1)
                                       ->  Index Scan using link_type_pkey on link_type lt (actual rows=0.93 loops=270)
                                             Index Cond: (id = ml.link_type_id)
                                             Filter: ((link)::text ~~ '%follow%'::text)
                                             Rows Removed by Filter: 0
                                             Index Searches: 270
                     ->  Memoize (actual rows=0.95 loops=1913)
                           Cache Key: mc.company_type_id
                           Cache Mode: logical
                           Hits: 1911  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                           ->  Index Scan using company_type_pkey on company_type ct (actual rows=0.50 loops=2)
                                 Index Cond: (id = mc.company_type_id)
                                 Filter: ((kind)::text = 'production companies'::text)
                                 Rows Removed by Filter: 0
                                 Index Searches: 2
               ->  Index Scan using company_name_pkey on company_name cn (actual rows=0.41 loops=1808)
                     Index Cond: (id = mc.company_id)
                     Filter: (((country_code)::text <> '[pl]'::text) AND ((name ~~ '%Film%'::text) OR (name ~~ '%Warner%'::text)))
                     Rows Removed by Filter: 1
                     Index Searches: 1808
         ->  Index Scan using title_pkey on title t (actual rows=1.00 loops=743)
               Index Cond: (id = mk.movie_id)
               Filter: ((production_year >= 1950) AND (production_year <= 2010))
               Index Searches: 743
 Planning Time: 65.292 ms
 Execution Time: 3339.823 ms
(105 rows)
*/

-- Indexes detected:
CREATE INDEX ind_1 ON movie_info(movie_id);
CREATE INDEX ind_2 ON movie_companies(movie_id);
-- In patched version:
CREATE INDEX ind_3 ON movie_companies(movie_id,company_id,company_type_id);

-- Execution with indexes:
/*
 Aggregate (actual rows=1.00 loops=1)
   ->  Nested Loop (actual rows=743.00 loops=1)
         Join Filter: (mi.movie_id = ml.movie_id)
         ->  Gather (actual rows=172.00 loops=1)
               Workers Planned: 4
               Workers Launched: 4
               ->  Nested Loop (actual rows=34.40 loops=5)
                     ->  Nested Loop (actual rows=659.20 loops=5)
                           Join Filter: (mk.movie_id = ml.movie_id)
                           ->  Nested Loop (actual rows=55.40 loops=5)
                                 ->  Parallel Hash Join (actual rows=152.60 loops=5)
                                       Hash Cond: (mc.company_type_id = ct.id)
                                       Rows Removed by Hash Matching: 20
                                       ->  Nested Loop (actual rows=172.80 loops=5)
                                             Join Filter: (mc.movie_id = ml.movie_id)
                                             ->  Nested Loop (actual rows=45.60 loops=5)
                                                   Join Filter: (t.id = ml.movie_id)
                                                   ->  Parallel Hash Join (actual rows=45.60 loops=5)
                                                         Hash Cond: (cc.status_id = cct2.id)
                                                         ->  Parallel Hash Join (actual rows=45.60 loops=5)
                                                               Hash Cond: (cc.subject_id = cct1.id)
                                                               Rows Removed by Hash Matching: 6
                                                               ->  Parallel Hash Join (actual rows=51.40 loops=5)
                                                                     Hash Cond: (ml.movie_id = cc.movie_id)
                                                                     Rows Removed by Hash Matching: 417
                                                                     ->  Parallel Hash Join (actual rows=463.00 loops=5)
                                                                           Hash Cond: (ml.link_type_id = lt.id)
                                                                           Rows Removed by Hash Matching: 5536
                                                                           ->  Parallel Seq Scan on movie_link ml (actual rows=5999.40 loops=5)
                                                                           ->  Parallel Hash (actual rows=0.40 loops=5)
                                                                                 Buckets: 1024  Batches: 1  Memory Usage: 40kB
                                                                                 ->  Parallel Seq Scan on link_type lt (actual rows=2.00 loops=1)
                                                                                       Filter: ((link)::text ~~ '%follow%'::text)
                                                                                       Rows Removed by Filter: 16
                                                                     ->  Parallel Hash (actual rows=27017.20 loops=5)
                                                                           Buckets: 262144  Batches: 1  Memory Usage: 8480kB
                                                                           ->  Parallel Seq Scan on complete_cast cc (actual rows=27017.20 loops=5)
                                                               ->  Parallel Hash (actual rows=0.20 loops=5)
                                                                     Buckets: 1024  Batches: 1  Memory Usage: 40kB
                                                                     ->  Parallel Seq Scan on comp_cast_type cct1 (actual rows=1.00 loops=1)
                                                                           Filter: ((kind)::text = 'cast'::text)
                                                                           Rows Removed by Filter: 3
                                                         ->  Parallel Hash (actual rows=0.40 loops=5)
                                                               Buckets: 1024  Batches: 1  Memory Usage: 40kB
                                                               ->  Parallel Seq Scan on comp_cast_type cct2 (actual rows=2.00 loops=1)
                                                                     Filter: ((kind)::text ~~ 'complete%'::text)
                                                                     Rows Removed by Filter: 2
                                                   ->  Index Scan using title_pkey on title t (actual rows=1.00 loops=228)
                                                         Index Cond: (id = cc.movie_id)
                                                         Filter: ((production_year >= 1950) AND (production_year <= 2010))
                                                         Index Searches: 228
                                             ->  Index Scan using ind_9 on movie_companies mc (actual rows=3.79 loops=228)
                                                   Index Cond: (movie_id = t.id)
                                                   Filter: (note IS NULL)
                                                   Rows Removed by Filter: 3
                                                   Index Searches: 228
                                       ->  Parallel Hash (actual rows=0.20 loops=5)
                                             Buckets: 1024  Batches: 1  Memory Usage: 40kB
                                             ->  Parallel Seq Scan on company_type ct (actual rows=1.00 loops=1)
                                                   Filter: ((kind)::text = 'production companies'::text)
                                                   Rows Removed by Filter: 3
                                 ->  Index Scan using company_name_pkey on company_name cn (actual rows=0.36 loops=763)
                                       Index Cond: (id = mc.company_id)
                                       Filter: (((country_code)::text <> '[pl]'::text) AND ((name ~~ '%Film%'::text) OR (name ~~ '%Warner%'::text)))
                                       Rows Removed by Filter: 1
                                       Index Searches: 763
                           ->  Index Scan using ind_0 on movie_keyword mk (actual rows=11.90 loops=277)
                                 Index Cond: (movie_id = t.id)
                                 Index Searches: 277
                     ->  Index Scan using keyword_pkey on keyword k (actual rows=0.05 loops=3296)
                           Index Cond: (id = mk.keyword_id)
                           Filter: (keyword = 'sequel'::text)
                           Rows Removed by Filter: 1
                           Index Searches: 3296
         ->  Index Scan using ind_1 on movie_info mi (actual rows=4.32 loops=172)
               Index Cond: (movie_id = mk.movie_id)
               Filter: (info = ANY ('{Sweden,Norway,Germany,Denmark,Swedish,Denish,Norwegian,German,English}'::text[]))
               Rows Removed by Filter: 15
               Index Searches: 172
 Planning Time: 154.427 ms
 Execution Time: 31.977 ms
(81 rows)
*/