#	exclusive	inclusive	rows x	rows	loops	node
1.	3.233	3627.180	↑ 2.4	469	1	→ Limit (cost=7364.69..7384.10 rows=1109 width=48) (actual time=3622.157..3627.180 rows=469 loops=1)
2.	645.297	3623.947	↑ 2.4	469	1	→ HashAggregate (cost=7364.69..7384.10 rows=1109 width=48) (actual time=3622.148..3623.947 rows=469 loops=1)
3.	531.729	2978.650	↓ 4.7	66122	1	→ Merge Join (cost=6163.19..6522.65 rows=14034 width=48) (actual time=2207.216..2978.650 rows=66122 loops=1) Merge Cond: (((weather_station.id)::bigint) = ((core.post_proc_result.fk_weather_station_id)::bigint))
4.	7.922	13.103	↑ 1.5	784	1	→ Sort (cost=82.71..85.58 rows=1148 width=27) (actual time=10.425..13.103 rows=784 loops=1) Sort Key: ((weather_station.id)::bigint) Sort Method: quicksort Memory: 140kB
5.	5.181	5.181	↑ 1.0	1126	1	→ Seq Scan on weather_station (cost=0.00..24.36 rows=1148 width=27) (actual time=0.015..5.181 rows=1126 loops=1) Filter: ((friendly_name)::text <> ''::text)
6.	501.405	2433.818	↓ 28.1	68789	1	→ Sort (cost=6080.48..6086.59 rows=2445 width=33) (actual time=2196.766..2433.818 rows=68789 loops=1) Sort Key: ((core.post_proc_result.fk_weather_station_id)::bigint) Sort Method: quicksort Memory: 8447kB
7.	267.655	1932.413	↓ 28.1	68789	1	→ Hash Join (cost=5884.58..5942.88 rows=2445 width=33) (actual time=1660.685..1932.413 rows=68789 loops=1) Hash Cond: ((geo_location.id)::bigint = core.post_proc_result.fk_weather_station_id)
8.	4.114	4.114	↑ 1.0	1149	1	→ Seq Scan on geo_location (cost=0.00..19.49 rows=1149 width=21) (actual time=0.011..4.114 rows=1149 loops=1)
9.	267.038	1660.644	↓ 66.7	68789	1	→ Hash (cost=5871.68..5871.68 rows=1032 width=28) (actual time=1660.644..1660.644 rows=68789 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 4031kB
10.	275.066	1393.606	↓ 66.7	68789	1	→ Hash Join (cost=4897.42..5871.68 rows=1032 width=28) (actual time=1115.949..1393.606 rows=68789 loops=1) Hash Cond: ((((core.post_proc_result.fk_weather_parameter_id)::bigint) = core.post_proc_result.fk_weather_parameter_id) AND (((core.post_proc_result.fk_weather_station_id)::bigint) = core.post_proc_result.fk_weather_station_id))
11.	267.068	533.690	↑ 2.0	719	1	→ HashAggregate (cost=2362.72..2388.19 rows=1455 width=8) (actual time=531.057..533.690 rows=719 loops=1)
12.	266.622	266.622	↑ 1.0	68789	1	→ Seq Scan on post_proc_result (cost=0.00..1846.81 rows=68789 width=8) (actual time=0.012..266.622 rows=68789 loops=1)
13.	287.075	584.850	↑ 1.0	68789	1	→ Hash (cost=1502.86..1502.86 rows=68789 width=16) (actual time=584.850..584.850 rows=68789 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 3225kB
14.	297.775	297.775	↑ 1.0	68789	1	→ Seq Scan on post_proc_result (cost=0.00..1502.86 rows=68789 width=16) (actual time=0.014..297.775 rows=68789 loops=1) Filter: (calc_type = 'avg'::bpchar)

"Limit  (cost=7364.69..7384.10 rows=1109 width=48) (actual time=3622.157..3627.180 rows=469 loops=1)"
"  ->  HashAggregate  (cost=7364.69..7384.10 rows=1109 width=48) (actual time=3622.148..3623.947 rows=469 loops=1)"
"        ->  Merge Join  (cost=6163.19..6522.65 rows=14034 width=48) (actual time=2207.216..2978.650 rows=66122 loops=1)"
"              Merge Cond: (((weather_station.id)::bigint) = ((core.post_proc_result.fk_weather_station_id)::bigint))"
"              ->  Sort  (cost=82.71..85.58 rows=1148 width=27) (actual time=10.425..13.103 rows=784 loops=1)"
"                    Sort Key: ((weather_station.id)::bigint)"
"                    Sort Method: quicksort  Memory: 140kB"
"                    ->  Seq Scan on weather_station  (cost=0.00..24.36 rows=1148 width=27) (actual time=0.015..5.181 rows=1126 loops=1)"
"                          Filter: ((friendly_name)::text <> ''::text)"
"              ->  Sort  (cost=6080.48..6086.59 rows=2445 width=33) (actual time=2196.766..2433.818 rows=68789 loops=1)"
"                    Sort Key: ((core.post_proc_result.fk_weather_station_id)::bigint)"
"                    Sort Method: quicksort  Memory: 8447kB"
"                    ->  Hash Join  (cost=5884.58..5942.88 rows=2445 width=33) (actual time=1660.685..1932.413 rows=68789 loops=1)"
"                          Hash Cond: ((geo_location.id)::bigint = core.post_proc_result.fk_weather_station_id)"
"                          ->  Seq Scan on geo_location  (cost=0.00..19.49 rows=1149 width=21) (actual time=0.011..4.114 rows=1149 loops=1)"
"                          ->  Hash  (cost=5871.68..5871.68 rows=1032 width=28) (actual time=1660.644..1660.644 rows=68789 loops=1)"
"                                Buckets: 1024  Batches: 1  Memory Usage: 4031kB"
"                                ->  Hash Join  (cost=4897.42..5871.68 rows=1032 width=28) (actual time=1115.949..1393.606 rows=68789 loops=1)"
"                                      Hash Cond: ((((core.post_proc_result.fk_weather_parameter_id)::bigint) = core.post_proc_result.fk_weather_parameter_id) AND (((core.post_proc_result.fk_weather_station_id)::bigint) = core.post_proc_result.fk_weather_station_id))"
"                                      ->  HashAggregate  (cost=2362.72..2388.19 rows=1455 width=8) (actual time=531.057..533.690 rows=719 loops=1)"
"                                            ->  Seq Scan on post_proc_result  (cost=0.00..1846.81 rows=68789 width=8) (actual time=0.012..266.622 rows=68789 loops=1)"
"                                      ->  Hash  (cost=1502.86..1502.86 rows=68789 width=16) (actual time=584.850..584.850 rows=68789 loops=1)"
"                                            Buckets: 8192  Batches: 1  Memory Usage: 3225kB"
"                                            ->  Seq Scan on post_proc_result  (cost=0.00..1502.86 rows=68789 width=16) (actual time=0.014..297.775 rows=68789 loops=1)"
"                                                  Filter: (calc_type = 'avg'::bpchar)"
"Total runtime: 3630.072 ms"

Per node type stats

node type	count	sum of times	% of query
Hash	2	554.113 ms	15.3 %
Hash Join	2	542.721 ms	15.0 %
HashAggregate	2	912.365 ms	25.2 %
Limit	1	3.233 ms	0.1 %
Merge Join	1	531.729 ms	14.7 %
Seq Scan	4	573.692 ms	15.8 %
Sort	2	509.327 ms	14.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
geo_location	1	4.114 ms	0.1 %
Seq Scan	1	4.114 ms	100.0 %
post_proc_result	2	564.397 ms	15.6 %
Seq Scan	2	564.397 ms	100.0 %
weather_station	1	5.181 ms	0.1 %
Seq Scan	1	5.181 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: KE8

Colorize:

Columns visibility:

Per node type stats

Per table stats