#	exclusive	inclusive	rows x	rows	loops	node
1.	278.174	3767.821	↓ 1.2	98	1	→ GroupAggregate (cost=100223350.66..100223363.86 rows=80 width=112) (actual time=3496.689..3767.821 rows=98 loops=1)
2.	33.501	3489.647	↓ 225.4	18033	1	→ Sort (cost=100223350.66..100223350.86 rows=80 width=112) (actual time=3480.639..3489.647 rows=18033 loops=1) Sort Key: ((date_part('epoch'::text, date_trunc('hour'::text, ('1970-01-01 01:00:00+01'::timestamp with time zone + ((gps_data."timestamp")::double precision * '00:00:01'::interval)))) * 1000::double precision)) Sort Method: external merge Disk: 1128kB
3.	114.078	3456.146	↓ 225.4	18033	1	→ Hash Join (cost=100216488.52..100223348.14 rows=80 width=112) (actual time=3305.942..3456.146 rows=18033 loops=1) Hash Cond: (gps_fms_data.gps_data_id = gps_data.id)
4.	139.516	139.516	↑ 1.0	159155	1	→ Seq Scan on gps_fms_data (cost=100000000.00..100003242.86 rows=159286 width=12) (actual time=3.344..139.516 rows=159155 loops=1)
5.	11.409	3202.552	↑ 2.2	18045	1	→ Hash (cost=215323.41..215323.41 rows=40009 width=108) (actual time=3202.552..3202.552 rows=18045 loops=1)
6.	2157.776	3191.143	↑ 2.2	18045	1	→ Bitmap Heap Scan on gps_data (cost=77956.14..215323.41 rows=40009 width=108) (actual time=1041.980..3191.143 rows=18045 loops=1) Recheck Cond: ((g_device_id = 605) AND ("timestamp" >= 1333231200) AND ("timestamp" <= 1334008740))
7.	37.900	1033.367	↓ 0.0	0	1	→ BitmapAnd (cost=77956.14..77956.14 rows=40009 width=0) (actual time=1033.367..1033.367 rows=0 loops=1)
8.	295.804	295.804	↓ 1.0	1209938	1	→ Bitmap Index Scan on device_id_index_gps_data (cost=0.00..22358.56 rows=1207135 width=0) (actual time=295.804..295.804 rows=1209938 loops=1) Index Cond: (g_device_id = 605)
9.	699.663	699.663	↓ 1.0	2691326	1	→ Bitmap Index Scan on gps_data_timestamp_indx (cost=0.00..55577.32 rows=2648430 width=0) (actual time=699.663..699.663 rows=2691326 loops=1) Index Cond: (("timestamp" >= 1333231200) AND ("timestamp" <= 1334008740))

                                                                                                         QUERY PLAN                                                                                                          
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=100223350.66..100223363.86 rows=80 width=112) (actual time=3496.689..3767.821 rows=98 loops=1)
   ->  Sort  (cost=100223350.66..100223350.86 rows=80 width=112) (actual time=3480.639..3489.647 rows=18033 loops=1)
         Sort Key: ((date_part('epoch'::text, date_trunc('hour'::text, ('1970-01-01 01:00:00+01'::timestamp with time zone + ((gps_data."timestamp")::double precision * '00:00:01'::interval)))) * 1000::double precision))
         Sort Method:  external merge  Disk: 1128kB
         ->  Hash Join  (cost=100216488.52..100223348.14 rows=80 width=112) (actual time=3305.942..3456.146 rows=18033 loops=1)
               Hash Cond: (gps_fms_data.gps_data_id = gps_data.id)
               ->  Seq Scan on gps_fms_data  (cost=100000000.00..100003242.86 rows=159286 width=12) (actual time=3.344..139.516 rows=159155 loops=1)
               ->  Hash  (cost=215323.41..215323.41 rows=40009 width=108) (actual time=3202.552..3202.552 rows=18045 loops=1)
                     ->  Bitmap Heap Scan on gps_data  (cost=77956.14..215323.41 rows=40009 width=108) (actual time=1041.980..3191.143 rows=18045 loops=1)
                           Recheck Cond: ((g_device_id = 605) AND ("timestamp" >= 1333231200) AND ("timestamp" <= 1334008740))
                           ->  BitmapAnd  (cost=77956.14..77956.14 rows=40009 width=0) (actual time=1033.367..1033.367 rows=0 loops=1)
                                 ->  Bitmap Index Scan on device_id_index_gps_data  (cost=0.00..22358.56 rows=1207135 width=0) (actual time=295.804..295.804 rows=1209938 loops=1)
                                       Index Cond: (g_device_id = 605)
                                 ->  Bitmap Index Scan on gps_data_timestamp_indx  (cost=0.00..55577.32 rows=2648430 width=0) (actual time=699.663..699.663 rows=2691326 loops=1)
                                       Index Cond: (("timestamp" >= 1333231200) AND ("timestamp" <= 1334008740))
 Total runtime: 3768.706 ms
(16 rows)

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	2157.776 ms	57.3 %
Bitmap Index Scan	2	995.467 ms	26.4 %
BitmapAnd	1	37.900 ms	1.0 %
GroupAggregate	1	278.174 ms	7.4 %
Hash	1	11.409 ms	0.3 %
Hash Join	1	114.078 ms	3.0 %
Seq Scan	1	139.516 ms	3.7 %
Sort	1	33.501 ms	0.9 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
gps_data	1	2157.776 ms	57.3 %
Bitmap Heap Scan	1	2157.776 ms	100.0 %
gps_fms_data	1	139.516 ms	3.7 %
Seq Scan	1	139.516 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: Wo7 : nakon clustera

Colorize:

Columns visibility:

Per node type stats

Per table stats