#	rows x	node
1.	↓ 0.0	→ Subquery Scan on x (cost=196839.11..196839.13 rows=1 width=204) (actual time=.. rows= loops=) Filter: (x.rank = 1)
2.	↓ 0.0	→ Sort (cost=196839.11..196839.12 rows=1 width=162) (actual time=.. rows= loops=) Sort Key: tr.begin_time, f.dataset_id
3.	↓ 0.0	→ WindowAgg (cost=196839.07..196839.10 rows=1 width=162) (actual time=.. rows= loops=)
4.	↓ 0.0	→ Sort (cost=196839.07..196839.08 rows=1 width=162) (actual time=.. rows= loops=) Sort Key: f.dataset_id, f.coordinate_1_id, f.shadow_coordinate_2_id, f.produced_id
5.	↓ 0.0	→ Nested Loop (cost=0.00..196839.06 rows=1 width=162) (actual time=.. rows= loops=) Join Filter: (s.type_id = st.id)
6.	↓ 0.0	→ Nested Loop (cost=0.00..196838.04 rows=1 width=160) (actual time=.. rows= loops=) Join Filter: (f.dataset_id = d.id)
7.	↓ 0.0	→ Nested Loop (cost=0.00..196818.55 rows=25 width=142) (actual time=.. rows= loops=)
8.	↓ 0.0	→ Seq Scan on time_ranges tr (cost=0.00..4632.38 rows=1 width=20) (actual time=.. rows= loops=) Filter: ((begin_time >= '2012-03-22 00:00:00'::timestamp without time zone) AND (begin_time <= '2012-03-2
9.	↓ 0.0	→ Index Scan using files_pkey on files f (cost=0.00..192185.43 rows=59 width=126) (actual time=.. rows= loops=) Index Cond: (coordinate_1_id = tr.id)
10.	↓ 0.0	→ Materialize (cost=0.00..18.74 rows=2 width=18) (actual time=.. rows= loops=)
11.	↓ 0.0	→ Nested Loop (cost=0.00..18.73 rows=2 width=18) (actual time=.. rows= loops=) Join Filter: (s.id = d.sensor_id)
12.	↓ 0.0	→ Nested Loop (cost=0.00..2.03 rows=1 width=12) (actual time=.. rows= loops=) Join Filter: (s.platform_id = p.id)
13.	↓ 0.0	→ Seq Scan on sensors s (cost=0.00..1.01 rows=1 width=12) (actual time=.. rows= loops=)
14.	↓ 0.0	→ Seq Scan on platforms p (cost=0.00..1.01 rows=1 width=8) (actual time=.. rows= loops=)
15.	↓ 0.0	→ Seq Scan on ingested_datasets d (cost=0.00..16.62 rows=6 width=14) (actual time=.. rows= loops=) Filter: ((ingest_file_type)::text = ANY ('{GITCO,SVI01,SVI02,SVI03,SVI04,SVI05}'::text[]))
16.	↓ 0.0	→ Seq Scan on sensor_types st (cost=0.00..1.01 rows=1 width=10) (actual time=.. rows= loops=)

                                                                                           QUERY PLAN                                                 
                                           
------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
 Subquery Scan on x  (cost=196839.11..196839.13 rows=1 width=204)
   Filter: (x.rank = 1)
   ->  Sort  (cost=196839.11..196839.12 rows=1 width=162)
         Sort Key: tr.begin_time, f.dataset_id
         ->  WindowAgg  (cost=196839.07..196839.10 rows=1 width=162)
               ->  Sort  (cost=196839.07..196839.08 rows=1 width=162)
                     Sort Key: f.dataset_id, f.coordinate_1_id, f.shadow_coordinate_2_id, f.produced_id
                     ->  Nested Loop  (cost=0.00..196839.06 rows=1 width=162)
                           Join Filter: (s.type_id = st.id)
                           ->  Nested Loop  (cost=0.00..196838.04 rows=1 width=160)
                                 Join Filter: (f.dataset_id = d.id)
                                 ->  Nested Loop  (cost=0.00..196818.55 rows=25 width=142)
                                       ->  Seq Scan on time_ranges tr  (cost=0.00..4632.38 rows=1 width=20)
                                             Filter: ((begin_time >= '2012-03-22 00:00:00'::timestamp without time zone) AND (begin_time <= '2012-03-2
3 00:00:00'::timestamp without time zone))
                                       ->  Index Scan using files_pkey on files f  (cost=0.00..192185.43 rows=59 width=126)
                                             Index Cond: (coordinate_1_id = tr.id)
                                 ->  Materialize  (cost=0.00..18.74 rows=2 width=18)
                                       ->  Nested Loop  (cost=0.00..18.73 rows=2 width=18)
                                             Join Filter: (s.id = d.sensor_id)
                                             ->  Nested Loop  (cost=0.00..2.03 rows=1 width=12)
                                                   Join Filter: (s.platform_id = p.id)
                                                   ->  Seq Scan on sensors s  (cost=0.00..1.01 rows=1 width=12)
                                                   ->  Seq Scan on platforms p  (cost=0.00..1.01 rows=1 width=8)
                                             ->  Seq Scan on ingested_datasets d  (cost=0.00..16.62 rows=6 width=14)
                                                   Filter: ((ingest_file_type)::text = ANY ('{GITCO,SVI01,SVI02,SVI03,SVI04,SVI05}'::text[]))
                           ->  Seq Scan on sensor_types st  (cost=0.00..1.01 rows=1 width=10)
(26 rows)

Per node type stats

node type	count	sum of times	% of query
Index Scan	1	0.000 ms	0.0 %
Materialize	1	0.000 ms	0.0 %
Nested Loop	5	0.000 ms	0.0 %
Seq Scan	5	0.000 ms	0.0 %
Sort	2	0.000 ms	0.0 %
Subquery Scan on x	1	0.000 ms	0.0 %
WindowAgg	1	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
files	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
ingested_datasets	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
platforms	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
sensor_types	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
sensors	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
time_ranges	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: kAY

Colorize:

Columns visibility:

Per node type stats

Per table stats