#	exclusive	inclusive	rows x	rows	loops	node
1.	94.273	125,268.479	↓ 458.8	640,518	1	→ Unique (cost=595,785.00..595,791.98 rows=1,396 width=8) (actual time=125,135.858..125,268.479 rows=640,518 loops=1)
2.	197.804	125,174.206	↓ 458.8	640,518	1	→ Sort (cost=595,785.00..595,788.49 rows=1,396 width=8) (actual time=125,135.857..125,174.206 rows=640,518 loops=1) Sort Key: s.id Sort Method: quicksort Memory: 54601kB
3.	1,828.258	124,976.402	↓ 458.8	640,518	1	→ Merge Join (cost=593,421.78..595,712.08 rows=1,396 width=8) (actual time=121,675.460..124,976.402 rows=640,518 loops=1) Merge Cond: ((t.theater_id = s.id_theater) AND (t.country_id = rr.id_country))
4.	19.009	19.009	↑ 1.0	36,405	1	→ Index Only Scan using relevant_theater_id_country_idx on relevant_theater t (cost=0.29..1,758.82 rows=36,423 width=14) (actual time=0.021..19.009 rows=36,405 loops=1) Heap Fetches: 36405
5.	33,175.568	123,129.135	↓ 298.8	13,450,279	1	→ Sort (cost=593,421.49..593,534.03 rows=45,015 width=18) (actual time=121,675.356..123,129.135 rows=13,450,279 loops=1) Sort Key: s.id_theater, rr.id_country Sort Method: external sort Disk: 447528kB
6.	80,608.049	89,953.567	↓ 298.8	13,450,279	1	→ Merge Join (cost=562,903.40..589,942.26 rows=45,015 width=18) (actual time=3,891.767..89,953.567 rows=13,450,279 loops=1) Merge Cond: ((s.gid_entity)::ltree = (m.gid)::ltree) Join Filter: ((s.theater_day >= (rr.released_at)::date) AND (s.theater_day <= ((rr.released_at)::date + '7 days'::interval))) Rows Removed by Join Filter: 72450967
7.	3,914.155	4,889.643	↑ 1.0	2,774,813	1	→ Sort (cost=551,098.57..558,035.61 rows=2,774,814 width=59) (actual time=3,718.451..4,889.643 rows=2,774,813 loops=1) Sort Key: s.gid_entity Sort Method: external merge Disk: 200920kB
8.	975.488	975.488	↑ 1.0	2,774,814	1	→ Seq Scan on relevant_showtime s (cost=0.00..193,032.14 rows=2,774,814 width=59) (actual time=0.014..975.488 rows=2,774,814 loops=1)
9.	4,328.056	4,455.875	↓ 3,127.5	85,901,246	1	→ Sort (cost=11,804.27..11,872.94 rows=27,466 width=51) (actual time=170.030..4,455.875 rows=85,901,246 loops=1) Sort Key: m.gid Sort Method: quicksort Memory: 4633kB
10.	16.142	127.819	↓ 1.0	27,481	1	→ Merge Join (cost=0.80..9,779.29 rows=27,466 width=51) (actual time=6.006..127.819 rows=27,481 loops=1) Merge Cond: (m.id = rr.id_movie)
11.	47.673	47.673	↑ 1.0	188,070	1	→ Index Only Scan using relevant_movie_idx on relevant_movie m (cost=0.42..8,185.17 rows=188,118 width=45) (actual time=0.027..47.673 rows=188,070 loops=1) Heap Fetches: 188070
12.	64.004	64.004	↓ 1.0	27,481	1	→ Index Scan using relevant_release_idx on relevant_release rr (cost=0.29..782.76 rows=27,466 width=18) (actual time=5.974..64.004 rows=27,481 loops=1) Filter: (released_at ~ '^(?:[0-9]{4}-[0-9]{2}-[0-9]{2})$'::text) Rows Removed by Filter: 889

 Unique  (cost=595785.00..595791.98 rows=1396 width=8) (actual time=125135.858..125268.479 rows=640518 loops=1)
   ->  Sort  (cost=595785.00..595788.49 rows=1396 width=8) (actual time=125135.857..125174.206 rows=640518 loops=1)
         Sort Key: s.id
         Sort Method: quicksort  Memory: 54601kB
         ->  Merge Join  (cost=593421.78..595712.08 rows=1396 width=8) (actual time=121675.460..124976.402 rows=640518 loops=1)
               Merge Cond: ((t.theater_id = s.id_theater) AND (t.country_id = rr.id_country))
               ->  Index Only Scan using relevant_theater_id_country_idx on relevant_theater t  (cost=0.29..1758.82 rows=36423 width=14) (actual time=0.021..19.009 rows=36405 loops=1)
                     Heap Fetches: 36405
               ->  Sort  (cost=593421.49..593534.03 rows=45015 width=18) (actual time=121675.356..123129.135 rows=13450279 loops=1)
                     Sort Key: s.id_theater, rr.id_country
                     Sort Method: external sort  Disk: 447528kB
                     ->  Merge Join  (cost=562903.40..589942.26 rows=45015 width=18) (actual time=3891.767..89953.567 rows=13450279 loops=1)
                           Merge Cond: ((s.gid_entity)::ltree = (m.gid)::ltree)
                           Join Filter: ((s.theater_day >= (rr.released_at)::date) AND (s.theater_day <= ((rr.released_at)::date + '7 days'::interval)))
                           Rows Removed by Join Filter: 72450967
                           ->  Sort  (cost=551098.57..558035.61 rows=2774814 width=59) (actual time=3718.451..4889.643 rows=2774813 loops=1)
                                 Sort Key: s.gid_entity
                                 Sort Method: external merge  Disk: 200920kB
                                 ->  Seq Scan on relevant_showtime s  (cost=0.00..193032.14 rows=2774814 width=59) (actual time=0.014..975.488 rows=2774814 loops=1)
                           ->  Sort  (cost=11804.27..11872.94 rows=27466 width=51) (actual time=170.030..4455.875 rows=85901246 loops=1)
                                 Sort Key: m.gid
                                 Sort Method: quicksort  Memory: 4633kB
                                 ->  Merge Join  (cost=0.80..9779.29 rows=27466 width=51) (actual time=6.006..127.819 rows=27481 loops=1)
                                       Merge Cond: (m.id = rr.id_movie)
                                       ->  Index Only Scan using relevant_movie_idx on relevant_movie m  (cost=0.42..8185.17 rows=188118 width=45) (actual time=0.027..47.673 rows=188070 loops=1)
                                             Heap Fetches: 188070
                                       ->  Index Scan using relevant_release_idx on relevant_release rr  (cost=0.29..782.76 rows=27466 width=18) (actual time=5.974..64.004 rows=27481 loops=1)
                                             Filter: (released_at ~ '^(?:[0-9]{4}-[0-9]{2}-[0-9]{2})$'::text)
                                             Rows Removed by Filter: 889
 Planning time: 11.324 ms
 Execution time: 125387.055 ms
(31 rows)

Time: 125399.714 ms (02:05.400)

Per node type stats

node type	count	sum of times	% of query
Index Only Scan	2	66.682 ms	0.1 %
Index Scan	1	64.004 ms	0.1 %
Merge Join	3	82,452.449 ms	65.8 %
Seq Scan	1	975.488 ms	0.8 %
Sort	4	41,615.583 ms	33.2 %
Unique	1	94.273 ms	0.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
relevant_movie	1	47.673 ms	0.0 %
Index Only Scan	1	47.673 ms	100.0 %
relevant_release	1	64.004 ms	0.1 %
Index Scan	1	64.004 ms	100.0 %
relevant_showtime	1	975.488 ms	0.8 %
Seq Scan	1	975.488 ms	100.0 %
relevant_theater	1	19.009 ms	0.0 %
Index Only Scan	1	19.009 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G5y4

Color mode:

Visible columns:

Per node type stats

Per table stats