#	exclusive	inclusive	rows x	rows	loops	node
1.	88.057	160,537.497	↓ 699,215.0	699,215	1	→ Unique (cost=7,407.35..7,407.36 rows=1 width=8) (actual time=160,412.617..160,537.497 rows=699,215 loops=1)
2.	228.732	160,449.440	↓ 699,215.0	699,215	1	→ Sort (cost=7,407.35..7,407.36 rows=1 width=8) (actual time=160,412.615..160,449.440 rows=699,215 loops=1) Sort Key: s.id Sort Method: quicksort Memory: 57352kB
3.	14,335.238	160,220.708	↓ 699,215.0	699,215	1	→ Nested Loop (cost=644.12..7,407.34 rows=1 width=8) (actual time=194.263..160,220.708 rows=699,215 loops=1) 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: 1524754
4.	5,094.422	5,291.766	↓ 14,743.5	35,148,426	1	→ Hash Join (cost=643.57..2,153.74 rows=2,384 width=75) (actual time=184.293..5,291.766 rows=35,148,426 loops=1) Hash Cond: (t.country_id = rr.id_country)
5.	13.072	13.072	↑ 1.0	36,679	1	→ Seq Scan on relevant_theater t (cost=0.00..1,348.79 rows=36,679 width=14) (actual time=0.009..13.072 rows=36,679 loops=1)
6.	8.900	184.272	↓ 2,403.5	31,245	1	→ Hash (cost=643.40..643.40 rows=13 width=73) (actual time=184.272..184.272 rows=31,245 loops=1) Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2911kB
7.	22.954	175.372	↓ 2,403.5	31,245	1	→ Nested Loop (cost=0.42..643.40 rows=13 width=73) (actual time=0.345..175.372 rows=31,245 loops=1)
8.	58.683	58.683	↓ 2,403.5	31,245	1	→ Seq Scan on relevant_release rr (cost=0.00..608.99 rows=13 width=40) (actual time=0.287..58.683 rows=31,245 loops=1) Filter: (released_at ~ '^(?:[0-9]{4}-[0-9]{2}-[0-9]{2})$'::text) Rows Removed by Filter: 1074
9.	93.735	93.735	↑ 1.0	1	31,245	→ Index Only Scan using relevant_movie_idx on relevant_movie m (cost=0.42..2.64 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=31,245) Index Cond: (id = rr.id_movie) Heap Fetches: 31245
10.	140,593.704	140,593.704	↓ 0.0	0	35,148,426	→ Index Scan using relevant_showtime_idx on relevant_showtime s (cost=0.56..2.18 rows=1 width=59) (actual time=0.004..0.004 rows=0 loops=35,148,426) Index Cond: ((id_theater = t.theater_id) AND ((gid_entity)::ltree = (m.gid)::ltree))

 Unique  (cost=7407.35..7407.36 rows=1 width=8) (actual time=160412.617..160537.497 rows=699215 loops=1)
   ->  Sort  (cost=7407.35..7407.36 rows=1 width=8) (actual time=160412.615..160449.440 rows=699215 loops=1)
         Sort Key: s.id
         Sort Method: quicksort  Memory: 57352kB
         ->  Nested Loop  (cost=644.12..7407.34 rows=1 width=8) (actual time=194.263..160220.708 rows=699215 loops=1)
               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: 1524754
               ->  Hash Join  (cost=643.57..2153.74 rows=2384 width=75) (actual time=184.293..5291.766 rows=35148426 loops=1)
                     Hash Cond: (t.country_id = rr.id_country)
                     ->  Seq Scan on relevant_theater t  (cost=0.00..1348.79 rows=36679 width=14) (actual time=0.009..13.072 rows=36679 loops=1)
                     ->  Hash  (cost=643.40..643.40 rows=13 width=73) (actual time=184.272..184.272 rows=31245 loops=1)
                           Buckets: 32768 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 2911kB
                           ->  Nested Loop  (cost=0.42..643.40 rows=13 width=73) (actual time=0.345..175.372 rows=31245 loops=1)
                                 ->  Seq Scan on relevant_release rr  (cost=0.00..608.99 rows=13 width=40) (actual time=0.287..58.683 rows=31245 loops=1)
                                       Filter: (released_at ~ '^(?:[0-9]{4}-[0-9]{2}-[0-9]{2})$'::text)
                                       Rows Removed by Filter: 1074
                                 ->  Index Only Scan using relevant_movie_idx on relevant_movie m  (cost=0.42..2.64 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=31245)
                                       Index Cond: (id = rr.id_movie)
                                       Heap Fetches: 31245
               ->  Index Scan using relevant_showtime_idx on relevant_showtime s  (cost=0.56..2.18 rows=1 width=59) (actual time=0.004..0.004 rows=0 loops=35148426)
                     Index Cond: ((id_theater = t.theater_id) AND ((gid_entity)::ltree = (m.gid)::ltree))
 Planning time: 5.436 ms
 Execution time: 160560.246 ms
(23 rows)

Time: 160567.914 ms (02:40.568)

Per node type stats

node type	count	sum of times	% of query
Hash	1	8.900 ms	0.0 %
Hash Join	1	5,094.422 ms	3.2 %
Index Only Scan	1	93.735 ms	0.1 %
Index Scan	1	140,593.704 ms	87.6 %
Nested Loop	2	14,358.192 ms	8.9 %
Seq Scan	2	71.755 ms	0.0 %
Sort	1	228.732 ms	0.1 %
Unique	1	88.057 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	93.735 ms	0.1 %
Index Only Scan	1	93.735 ms	100.0 %
relevant_release	1	58.683 ms	0.0 %
Seq Scan	1	58.683 ms	100.0 %
relevant_showtime	1	140,593.704 ms	87.6 %
Index Scan	1	140,593.704 ms	100.0 %
relevant_theater	1	13.072 ms	0.0 %
Seq Scan	1	13.072 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4TVo

Color mode:

Visible columns:

Per node type stats

Per table stats