#	exclusive	inclusive	rows x	rows	loops	node
1.	0.102	6059.600	↓ 98.0	98	1	→ Sort (cost=100059579.93..100059579.94 rows=1 width=112) (actual time=6059.592..6059.600 rows=98 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: quicksort Memory: 38kB
2.	764.607	6059.498	↓ 98.0	98	1	→ HashAggregate (cost=100059579.78..100059579.92 rows=1 width=112) (actual time=6059.090..6059.498 rows=98 loops=1)
3.	1358.463	5294.891	↓ 2003.7	18033	1	→ Hash Join (cost=100055882.59..100059579.62 rows=9 width=112) (actual time=3953.795..5294.891 rows=18033 loops=1) Hash Cond: (gps_fms_data.gps_data_id = gps_data.id)
4.	32.609	32.609	↓ 1.0	122078	1	→ Seq Scan on gps_fms_data (cost=100000000.00..100002477.40 rows=121940 width=12) (actual time=0.013..32.609 rows=122078 loops=1)
5.	10.367	3903.819	↓ 2.9	18045	1	→ Hash (cost=55804.44..55804.44 rows=6252 width=108) (actual time=3903.819..3903.819 rows=18045 loops=1)
6.	2782.643	3893.452	↓ 2.9	18045	1	→ Bitmap Heap Scan on gps_data (cost=32013.03..55804.44 rows=6252 width=108) (actual time=1122.572..3893.452 rows=18045 loops=1) Recheck Cond: (("timestamp" >= 1333231200) AND ("timestamp" <= 1334008740) AND (g_device_id = 605))
7.	5.787	1110.809	↓ 0.0	0	1	→ BitmapAnd (cost=32013.03..32013.03 rows=6252 width=0) (actual time=1110.809..1110.809 rows=0 loops=1)
8.	729.896	729.896	↓ 6.7	2691454	1	→ Bitmap Index Scan on gps_data_timestamp_indx (cost=0.00..8566.25 rows=402494 width=0) (actual time=729.896..729.896 rows=2691454 loops=1) Index Cond: (("timestamp" >= 1333231200) AND ("timestamp" <= 1334008740))
9.	375.126	375.126	↑ 1.1	1179284	1	→ Bitmap Index Scan on device_id_index_gps_data (cost=0.00..23443.41 rows=1250415 width=0) (actual time=375.126..375.126 rows=1179284 loops=1) Index Cond: (g_device_id = 605)

                                                                                                     QUERY PLAN                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=100059579.93..100059579.94 rows=1 width=112) (actual time=6059.592..6059.600 rows=98 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:  quicksort  Memory: 38kB
   ->  HashAggregate  (cost=100059579.78..100059579.92 rows=1 width=112) (actual time=6059.090..6059.498 rows=98 loops=1)
         ->  Hash Join  (cost=100055882.59..100059579.62 rows=9 width=112) (actual time=3953.795..5294.891 rows=18033 loops=1)
               Hash Cond: (gps_fms_data.gps_data_id = gps_data.id)
               ->  Seq Scan on gps_fms_data  (cost=100000000.00..100002477.40 rows=121940 width=12) (actual time=0.013..32.609 rows=122078 loops=1)
               ->  Hash  (cost=55804.44..55804.44 rows=6252 width=108) (actual time=3903.819..3903.819 rows=18045 loops=1)
                     ->  Bitmap Heap Scan on gps_data  (cost=32013.03..55804.44 rows=6252 width=108) (actual time=1122.572..3893.452 rows=18045 loops=1)
                           Recheck Cond: (("timestamp" >= 1333231200) AND ("timestamp" <= 1334008740) AND (g_device_id = 605))
                           ->  BitmapAnd  (cost=32013.03..32013.03 rows=6252 width=0) (actual time=1110.809..1110.809 rows=0 loops=1)
                                 ->  Bitmap Index Scan on gps_data_timestamp_indx  (cost=0.00..8566.25 rows=402494 width=0) (actual time=729.896..729.896 rows=2691454 loops=1)
                                       Index Cond: (("timestamp" >= 1333231200) AND ("timestamp" <= 1334008740))
                                 ->  Bitmap Index Scan on device_id_index_gps_data  (cost=0.00..23443.41 rows=1250415 width=0) (actual time=375.126..375.126 rows=1179284 loops=1)
                                       Index Cond: (g_device_id = 605)
 Total runtime: 6059.885 ms
(16 rows)

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	2782.643 ms	45.9 %
Bitmap Index Scan	2	1105.022 ms	18.2 %
BitmapAnd	1	5.787 ms	0.1 %
Hash	1	10.367 ms	0.2 %
Hash Join	1	1358.463 ms	22.4 %
HashAggregate	1	764.607 ms	12.6 %
Seq Scan	1	32.609 ms	0.5 %
Sort	1	0.102 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
gps_data	1	2782.643 ms	45.9 %
Bitmap Heap Scan	1	2782.643 ms	100.0 %
gps_fms_data	1	32.609 ms	0.5 %
Seq Scan	1	32.609 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: rFY : nakon dump-restore

Colorize:

Columns visibility:

Per node type stats

Per table stats