#	exclusive	inclusive	rows x	rows	loops	node
1.	470.926	9922.481	↓ 1.0	890094	1	→ Hash Join (cost=23355.19..116327.78 rows=868674 width=16) (actual time=4137.366..9922.481 rows=890094 loops=1) Hash Cond: (t.dp_para_id = p.dp_para_id)
2.	5321.187	9389.564	↓ 1.0	890094	1	→ Bitmap Heap Scan on glt_dp_para_trend_10sec t (cost=21681.47..97280.58 rows=868674 width=20) (actual time=4074.376..9389.564 rows=890094 loops=1) Recheck Cond: ((trend_date >= '2012-03-01 00:00:00+01'::timestamp with time zone) AND (trend_date < '2012-03-08 00:00:00+01'::timestamp with time zone))
3.	4068.377	4068.377	↓ 1.0	895972	1	→ Bitmap Index Scan on glt_dp_para_trend_10sec_trend_date_idx (cost=0.00..21464.30 rows=868674 width=0) (actual time=4068.377..4068.377 rows=895972 loops=1) Index Cond: ((trend_date >= '2012-03-01 00:00:00+01'::timestamp with time zone) AND (trend_date < '2012-03-08 00:00:00+01'::timestamp with time zone))
4.	3.000	61.991	↑ 1.0	11994	1	→ Hash (cost=1523.80..1523.80 rows=11994 width=8) (actual time=61.991..61.991 rows=11994 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 328kB
5.	5.999	58.991	↑ 1.0	11994	1	→ Hash Join (cost=726.68..1523.80 rows=11994 width=8) (actual time=21.997..58.991 rows=11994 loops=1) Hash Cond: (d.unit_id = u.unit_id)
6.	24.996	52.992	↑ 1.0	11994	1	→ Hash Join (cost=597.87..1230.07 rows=11994 width=12) (actual time=21.997..52.992 rows=11994 loops=1) Hash Cond: (d.dp_id = p.dp_para_id)
7.	5.999	5.999	↑ 1.0	13963	1	→ Seq Scan on glt_cfg_dp d (cost=0.00..372.63 rows=13963 width=8) (actual time=0.000..5.999 rows=13963 loops=1)
8.	5.000	21.997	↑ 1.0	11994	1	→ Hash (cost=447.94..447.94 rows=11994 width=4) (actual time=21.997..21.997 rows=11994 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 282kB
9.	16.997	16.997	↑ 1.0	11994	1	→ Seq Scan on glt_dp_para p (cost=0.00..447.94 rows=11994 width=4) (actual time=0.000..16.997 rows=11994 loops=1)
10.	0.000	0.000	↑ 1.0	310	1	→ Hash (cost=124.94..124.94 rows=310 width=4) (actual time=0.000..0.000 rows=310 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB
11.	0.000	0.000	↑ 1.0	310	1	→ Hash Join (cost=1.16..124.94 rows=310 width=4) (actual time=0.000..0.000 rows=310 loops=1) Hash Cond: (u.prot_type_no = x.prot_type_no)
12.	0.000	0.000	↑ 1.0	310	1	→ Nested Loop (cost=0.00..119.52 rows=310 width=8) (actual time=0.000..0.000 rows=310 loops=1) Join Filter: (n.node_id = u.node_id)
13.	0.000	0.000	↑ 1.0	1	1	→ Nested Loop (cost=0.00..16.54 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1) Join Filter: (n.site_id = s.site_id)
14.	0.000	0.000	↑ 1.0	1	1	→ Index Scan using glt_cfg_node_pkey on glt_cfg_node n (cost=0.00..8.27 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
15.	0.000	0.000	↑ 1.0	1	1	→ Index Scan using glt_cfg_site_pkey on glt_cfg_site s (cost=0.00..8.27 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)
16.	0.000	0.000	↑ 1.0	310	1	→ Seq Scan on glt_cfg_unit u (cost=0.00..99.10 rows=310 width=12) (actual time=0.000..0.000 rows=310 loops=1)
17.	0.000	0.000	↑ 1.0	7	1	→ Hash (cost=1.07..1.07 rows=7 width=4) (actual time=0.000..0.000 rows=7 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
18.	0.000	0.000	↑ 1.0	7	1	→ Seq Scan on glt_sys_prot_type x (cost=0.00..1.07 rows=7 width=4) (actual time=0.000..0.000 rows=7 loops=1)

 Hash Join  (cost=23355.19..116327.78 rows=868674 width=16) (actual time=4137.366..9922.481 rows=890094 loops=1)
   Hash Cond: (t.dp_para_id = p.dp_para_id)
   ->  Bitmap Heap Scan on glt_dp_para_trend_10sec t  (cost=21681.47..97280.58 rows=868674 width=20) (actual time=4074.376..9389.564 rows=890094 loops=1)
         Recheck Cond: ((trend_date >= '2012-03-01 00:00:00+01'::timestamp with time zone) AND (trend_date < '2012-03-08 00:00:00+01'::timestamp with time zone))
         ->  Bitmap Index Scan on glt_dp_para_trend_10sec_trend_date_idx  (cost=0.00..21464.30 rows=868674 width=0) (actual time=4068.377..4068.377 rows=895972 loops=1)
               Index Cond: ((trend_date >= '2012-03-01 00:00:00+01'::timestamp with time zone) AND (trend_date < '2012-03-08 00:00:00+01'::timestamp with time zone))
   ->  Hash  (cost=1523.80..1523.80 rows=11994 width=8) (actual time=61.991..61.991 rows=11994 loops=1)
         Buckets: 2048  Batches: 1  Memory Usage: 328kB
         ->  Hash Join  (cost=726.68..1523.80 rows=11994 width=8) (actual time=21.997..58.991 rows=11994 loops=1)
               Hash Cond: (d.unit_id = u.unit_id)
               ->  Hash Join  (cost=597.87..1230.07 rows=11994 width=12) (actual time=21.997..52.992 rows=11994 loops=1)
                     Hash Cond: (d.dp_id = p.dp_para_id)
                     ->  Seq Scan on glt_cfg_dp d  (cost=0.00..372.63 rows=13963 width=8) (actual time=0.000..5.999 rows=13963 loops=1)
                     ->  Hash  (cost=447.94..447.94 rows=11994 width=4) (actual time=21.997..21.997 rows=11994 loops=1)
                           Buckets: 2048  Batches: 1  Memory Usage: 282kB
                           ->  Seq Scan on glt_dp_para p  (cost=0.00..447.94 rows=11994 width=4) (actual time=0.000..16.997 rows=11994 loops=1)
               ->  Hash  (cost=124.94..124.94 rows=310 width=4) (actual time=0.000..0.000 rows=310 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 8kB
                     ->  Hash Join  (cost=1.16..124.94 rows=310 width=4) (actual time=0.000..0.000 rows=310 loops=1)
                           Hash Cond: (u.prot_type_no = x.prot_type_no)
                           ->  Nested Loop  (cost=0.00..119.52 rows=310 width=8) (actual time=0.000..0.000 rows=310 loops=1)
                                 Join Filter: (n.node_id = u.node_id)
                                 ->  Nested Loop  (cost=0.00..16.54 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)
                                       Join Filter: (n.site_id = s.site_id)
                                       ->  Index Scan using glt_cfg_node_pkey on glt_cfg_node n  (cost=0.00..8.27 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
                                       ->  Index Scan using glt_cfg_site_pkey on glt_cfg_site s  (cost=0.00..8.27 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)
                                 ->  Seq Scan on glt_cfg_unit u  (cost=0.00..99.10 rows=310 width=12) (actual time=0.000..0.000 rows=310 loops=1)
                           ->  Hash  (cost=1.07..1.07 rows=7 width=4) (actual time=0.000..0.000 rows=7 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                 ->  Seq Scan on glt_sys_prot_type x  (cost=0.00..1.07 rows=7 width=4) (actual time=0.000..0.000 rows=7 loops=1)
 Total runtime: 9974.473 ms
(31 rows)

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	5321.187 ms	53.6 %
Bitmap Index Scan	1	4068.377 ms	41.0 %
Hash	4	8.000 ms	0.1 %
Hash Join	4	501.921 ms	5.1 %
Index Scan	2	0.000 ms	0.0 %
Nested Loop	2	0.000 ms	0.0 %
Seq Scan	4	22.996 ms	0.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
glt_cfg_dp	1	5.999 ms	0.1 %
Seq Scan	1	5.999 ms	100.0 %
glt_cfg_node	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
glt_cfg_site	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
glt_cfg_unit	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
glt_dp_para	1	16.997 ms	0.2 %
Seq Scan	1	16.997 ms	100.0 %
glt_dp_para_trend_10sec	1	5321.187 ms	53.6 %
Bitmap Heap Scan	1	5321.187 ms	100.0 %
glt_sys_prot_type	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: m4s

Colorize:

Columns visibility:

Per node type stats

Per table stats