explain (analyze, costs) SELECT filmid, title, count(*) FROM film NATURAL JOIN filmgenre GROUP BY filmid, title ORDER BY count(*) DESC LIMIT 25; Limit (cost=3118.16..3118.22 rows=25 width=32) (actual time=130.368..130.383 rows=25 loops=1) -> Sort (cost=3118.16..3198.41 rows=32100 width=32) (actual time=130.365..130.368 rows=25 loops=1) Sort Key: (count(*)) DESC Sort Method: top-N heapsort Memory: 27kB -> HashAggregate (cost=1891.32..2212.32 rows=32100 width=32) (actual time=110.248..121.549 rows=24014 loops=1) Group Key: film.filmid, film.title -> Hash Join (cost=960.25..1617.76 rows=36475 width=24) (actual time=46.470..88.327 rows=33877 loops=1) Hash Cond: (filmgenre.filmid = film.filmid) -> Seq Scan on filmgenre (cost=0.00..561.75 rows=36475 width=4) (actual time=0.015..21.969 rows=36475 loops=1) -> Hash (cost=559.00..559.00 rows=32100 width=24) (actual time=46.125..46.126 rows=32100 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 2033kB -> Seq Scan on film (cost=0.00..559.00 rows=32100 width=24) (actual time=0.014..30.026 rows=32100 loops=1) Planning Time: 94.753 ms Execution Time: 132.680 ms (14 rows) explain analyze SELECT fid, title, ant FROM film JOIN (SELECT filmid as fid, count(*) as ant from filmgenre group by filmid) as aggRes ON film.filmid = aggRes.fid ORDER BY ant DESC LIMIT 25; Limit (cost=2895.97..2896.03 rows=25 width=32) (actual time=99.461..99.479 rows=25 loops=1) -> Sort (cost=2895.97..2958.08 rows=24845 width=32) (actual time=99.458..99.463 rows=25 loops=1) Sort Key: (count(*)) DESC Sort Method: top-N heapsort Memory: 27kB -> Hash Join (cost=1551.59..2194.86 rows=24845 width=32) (actual time=55.424..87.333 rows=24014 loops=1) Hash Cond: (film.filmid = filmgenre.filmid) -> Seq Scan on film (cost=0.00..559.00 rows=32100 width=24) (actual time=0.019..7.873 rows=32100 loops=1) -> Hash (cost=1241.03..1241.03 rows=24845 width=12) (actual time=55.226..55.226 rows=25750 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1464kB -> HashAggregate (cost=744.12..992.58 rows=24845 width=12) (actual time=30.976..43.057 rows=25750 loops=1) Group Key: filmgenre.filmid -> Seq Scan on filmgenre (cost=0.00..561.75 rows=36475 width=4) (actual time=0.016..7.997 rows=36475 loops=1) Planning Time: 0.517 ms Execution Time: 99.831 ms (14 rows) explain analyze SELECT fid, title, ant FROM film JOIN (SELECT filmid as fid, count(*) as ant from filmgenre group by filmid ORDER BY ant DESC LIMIT 25) as aggRes ON film.filmid = aggRes.fid; Nested Loop (cost=1693.97..1793.62 rows=25 width=32) (actual time=31.151..58.285 rows=17 loops=1) -> Limit (cost=1693.68..1693.75 rows=25 width=12) (actual time=19.962..19.979 rows=25 loops=1) -> Sort (cost=1693.68..1755.80 rows=24845 width=12) (actual time=19.961..19.969 rows=25 loops=1) Sort Key: (count(*)) DESC Sort Method: top-N heapsort Memory: 26kB -> HashAggregate (cost=744.12..992.58 rows=24845 width=12) (actual time=12.601..16.514 rows=25750 loops=1) Group Key: filmgenre.filmid -> Seq Scan on filmgenre (cost=0.00..561.75 rows=36475 width=4) (actual time=0.012..3.248 rows=36475 loops=1) -> Index Scan using filmpkey on film (cost=0.29..3.98 rows=1 width=24) (actual time=1.530..1.530 rows=1 loops=25) Index Cond: (filmid = filmgenre.filmid) Planning Time: 0.219 ms Execution Time: 58.479 ms (12 rows) select title, rank from film inner join filmrating on film.filmid = filmrating.filmid where votes > 1000 and rank >= ( select avg(rank) from filmrating where votes > 1000 ); Nested Loop (cost=211.10..993.36 rows=215 width=24) InitPlan 1 (returns $0) -> Aggregate (cost=210.80..210.81 rows=1 width=8) -> Seq Scan on filmrating filmrating_1 (cost=0.00..209.19 rows=644 width=4) Filter: (votes > 1000) -> Seq Scan on filmrating (cost=0.00..236.82 rows=215 width=8) Filter: ((votes > 1000) AND (rank >= $0)) -> Index Scan using filmpkey on film (cost=0.29..2.53 rows=1 width=24) Index Cond: (filmid = filmrating.filmid) (9 rows) Nested Loop (cost=211.10..993.36 rows=215 width=24) (actual time=1.394..4.084 rows=380 loops=1) InitPlan 1 (returns $0) -> Aggregate (cost=210.80..210.81 rows=1 width=8) (actual time=1.352..1.352 rows=1 loops=1) -> Seq Scan on filmrating filmrating_1 (cost=0.00..209.19 rows=644 width=4) (actual time=0.017..1.235 rows=645 loops=1) Filter: (votes > 1000) Rows Removed by Filter: 10410 -> Seq Scan on filmrating (cost=0.00..236.82 rows=215 width=8) (actual time=1.385..2.594 rows=383 loops=1) Filter: ((votes > 1000) AND (rank >= $0)) Rows Removed by Filter: 10672 -> Index Scan using filmpkey on film (cost=0.29..2.53 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=383) Index Cond: (filmid = filmrating.filmid) Planning time: 0.475 ms Execution time: 4.203 ms (13 rows) SELECT * FROM filmitem JOIN filmparticipation ON filmitem.filmid = filmparticipation.filmid JOIN filmcharacter ON filmparticipation.partid = filmcharacter.partid LIMIT 5; Limit (cost=0.71..4.70 rows=5 width=45) (actual time=0.031..0.057 rows=5 loops=1) -> Nested Loop (cost=0.71..262734.77 rows=329315 width=45) (actual time=0.030..0.053 rows=5 loops=1) -> Nested Loop (cost=0.42..161172.99 rows=329315 width=39) (actual time=0.021..0.035 rows=5 loops=1) -> Seq Scan on filmcharacter (cost=0.00..5303.15 rows=329315 width=21) (actual time=0.007..0.008 rows=5 loops=1) -> Index Scan using filmparticipationpkey on filmparticipation (cost=0.42..0.47 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=5) Index Cond: (partid = filmcharacter.partid) -> Index Scan using filmitempkey on filmitem (cost=0.29..0.31 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=5) Index Cond: (filmid = filmparticipation.filmid) Planning Time: 0.559 ms Execution Time: 0.095 ms (10 rows) select film.title, person.firstname || ' ' || person.lastname as fullname from filmcountry natural join film natural join filmparticipation natural join person where filmcountry.country = 'Norway' and parttype = 'director' and prodyear < 1960; Gather (cost=1001.71..12741.56 rows=776 width=52) (actual time=0.712..41.272 rows=269 loops=1) Workers Planned: 2 Workers Launched: 2 -> Nested Loop (cost=1.71..11663.96 rows=323 width=52) (actual time=0.623..30.889 rows=90 loops=3) -> Nested Loop (cost=1.28..11503.06 rows=323 width=24) (actual time=0.585..29.860 rows=90 loops=3) Join Filter: (filmcountry.filmid = filmparticipation.filmid) -> Nested Loop (cost=0.85..11012.55 rows=245 width=28) (actual time=0.503..23.586 rows=90 loops=3) -> Parallel Index Only Scan using filmcountrypkey on filmcountry (cost=0.42..8084.41 rows=860 width=4) (actual time=0.064..16.941 rows=748 loops=3) Index Cond: (country = 'Norway'::text) Heap Fetches: 0 -> Index Scan using filmpkey on film (cost=0.42..3.40 rows=1 width=24) (actual time=0.008..0.008 rows=0 loops=2245) Index Cond: (filmid = filmcountry.filmid) Filter: (prodyear < 1960) Rows Removed by Filter: 1 -> Index Scan using filmparticipationfilmidindex on filmparticipation (cost=0.43..1.98 rows=2 width=8) (actual time=0.021..0.069 rows=1 loops=269) Index Cond: (filmid = film.filmid) Filter: (parttype = 'director'::text) Rows Removed by Filter: 13 -> Index Scan using personpkey on person (cost=0.43..0.49 rows=1 width=17) (actual time=0.009..0.009 rows=1 loops=269) Index Cond: (personid = filmparticipation.personid) Planning Time: 1.362 ms Execution Time: 41.382 ms (22 rows)