#	exclusive	inclusive	rows x	rows	loops	node
1.	3656.647	10998.711	↑ 1.3	14	1	→ Hash Join (cost=46236.84..68594.52 rows=18 width=219) (actual time=8837.881..10998.711 rows=14 loops=1) Hash Cond: (m.id = sm.member_id) Join Filter: (circle(center(m.geom), (width(m.geom) / 2::double precision)) @> point(p.lon, p.lat))
2.	464.426	464.426	↓ 1.0	504076	1	→ Seq Scan on map_marker m (cost=0.00..18140.93 rows=503893 width=36) (actual time=0.003..464.426 rows=504076 loops=1)
3.	2501.487	6877.638	↓ 54.0	1381810	1	→ Hash (cost=45917.09..45917.09 rows=25580 width=239) (actual time=6877.638..6877.638 rows=1381810 loops=1) Buckets: 4096 Batches: 64 (originally 1) Memory Usage: 8193kB
4.	3076.307	4376.151	↓ 54.0	1381810	1	→ Hash Join (cost=12472.88..45917.09 rows=25580 width=239) (actual time=304.616..4376.151 rows=1381810 loops=1) Hash Cond: (sm.shared_item_id = si.id)
5.	995.754	995.754	↑ 1.0	1732030	1	→ Seq Scan on shared_members sm (cost=0.00..26693.30 rows=1732030 width=8) (actual time=0.003..995.754 rows=1732030 loops=1)
6.	2.510	304.090	↓ 5.9	2124	1	→ Hash (cost=12468.42..12468.42 rows=357 width=239) (actual time=304.090..304.090 rows=2124 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 648kB
7.	1.776	301.580	↓ 5.9	2124	1	→ Hash Join (cost=10892.88..12468.42 rows=357 width=239) (actual time=255.859..301.580 rows=2124 loops=1) Hash Cond: (u.customer_id = c.id)
8.	0.364	270.988	↑ 2.5	122	1	→ Nested Loop (cost=8910.06..10480.89 rows=307 width=239) (actual time=227.028..270.988 rows=122 loops=1)
9.	39.006	270.136	↑ 3.3	122	1	→ Merge Join (cost=8910.06..9083.92 rows=401 width=239) (actual time=226.986..270.136 rows=122 loops=1) Merge Cond: (msg.unit_id = p.unit_id) Join Filter: ((p."time" >= (msg.gmt_date - '03:30:00'::interval)) AND (p."time" <= (msg.gmt_date + '03:30:00'::interval)))
10.	3.558	16.804	↑ 4.1	995	1	→ Sort (cost=3590.40..3600.48 rows=4033 width=219) (actual time=16.101..16.804 rows=995 loops=1) Sort Key: msg.unit_id Sort Method: quicksort Memory: 1175kB
11.	13.246	13.246	↑ 1.7	2406	1	→ Index Scan using message_time_index on messages msg (cost=0.01..3348.87 rows=4033 width=219) (actual time=0.046..13.246 rows=2406 loops=1) Index Cond: ((gmt_date >= ((now() - '05:00:00'::interval) - '1 day'::interval)) AND (gmt_date <= now())) Filter: ((at_location IS NULL) AND (msgtype = 1100))
12.	101.616	214.326	↓ 3.4	62356	1	→ Sort (cost=5315.44..5360.69 rows=18099 width=28) (actual time=174.411..214.326 rows=62356 loops=1) Sort Key: p.unit_id Sort Method: quicksort Memory: 6413kB
13.	112.710	112.710	↓ 3.4	62417	1	→ Index Scan using insert_time_point on point p (cost=0.01..4035.51 rows=18099 width=28) (actual time=0.064..112.710 rows=62417 loops=1) Index Cond: ((insert_time >= (now() - '05:00:00'::interval)) AND (insert_time <= now())) Filter: (knots < 5)
14.	0.488	0.488	↑ 1.0	1	122	→ Index Scan using unit_pkey on unit u (cost=0.00..3.47 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=122) Index Cond: (u.id = msg.unit_id)
15.	4.632	28.816	↓ 7.5	6677	1	→ Hash (cost=1971.74..1971.74 rows=886 width=12) (actual time=28.816..28.816 rows=6677 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 287kB
16.	9.352	24.184	↓ 7.5	6677	1	→ Hash Join (cost=1035.37..1971.74 rows=886 width=12) (actual time=5.308..24.184 rows=6677 loops=1) Hash Cond: (si.owner_id = us.id)
17.	9.542	9.542	↓ 1.0	7031	1	→ Seq Scan on shared_item si (cost=0.00..901.16 rows=7026 width=8) (actual time=0.009..9.542 rows=7031 loops=1) Filter: (type = 'C'::bpchar)
18.	0.537	5.290	↑ 1.0	735	1	→ Hash (cost=1026.19..1026.19 rows=735 width=12) (actual time=5.290..5.290 rows=735 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 32kB
19.	0.987	4.753	↑ 1.0	735	1	→ Hash Join (cost=136.17..1026.19 rows=735 width=12) (actual time=1.246..4.753 rows=735 loops=1) Hash Cond: (us.customer_id = c.id)
20.	2.531	2.531	↑ 1.0	735	1	→ Seq Scan on users us (cost=0.00..879.91 rows=735 width=8) (actual time=0.003..2.531 rows=735 loops=1) Filter: ((name)::text = 'main'::text)
21.	0.495	1.235	↑ 1.0	763	1	→ Hash (cost=126.63..126.63 rows=763 width=4) (actual time=1.235..1.235 rows=763 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 27kB
22.	0.740	0.740	↑ 1.0	763	1	→ Seq Scan on customers c (cost=0.00..126.63 rows=763 width=4) (actual time=0.003..0.740 rows=763 loops=1)

 Hash Join  (cost=46236.84..68594.52 rows=18 width=219) (actual time=8837.881..10998.711 rows=14 loops=1)
   Hash Cond: (m.id = sm.member_id)
   Join Filter: (circle(center(m.geom), (width(m.geom) / 2::double precision)) @> point(p.lon, p.lat))
   ->  Seq Scan on map_marker m  (cost=0.00..18140.93 rows=503893 width=36) (actual time=0.003..464.426 rows=504076 loops=1)
   ->  Hash  (cost=45917.09..45917.09 rows=25580 width=239) (actual time=6877.638..6877.638 rows=1381810 loops=1)
         Buckets: 4096  Batches: 64 (originally 1)  Memory Usage: 8193kB
         ->  Hash Join  (cost=12472.88..45917.09 rows=25580 width=239) (actual time=304.616..4376.151 rows=1381810 loops=1)
               Hash Cond: (sm.shared_item_id = si.id)
               ->  Seq Scan on shared_members sm  (cost=0.00..26693.30 rows=1732030 width=8) (actual time=0.003..995.754 rows=1732030 loops=1)
               ->  Hash  (cost=12468.42..12468.42 rows=357 width=239) (actual time=304.090..304.090 rows=2124 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 648kB
                     ->  Hash Join  (cost=10892.88..12468.42 rows=357 width=239) (actual time=255.859..301.580 rows=2124 loops=1)
                           Hash Cond: (u.customer_id = c.id)
                           ->  Nested Loop  (cost=8910.06..10480.89 rows=307 width=239) (actual time=227.028..270.988 rows=122 loops=1)
                                 ->  Merge Join  (cost=8910.06..9083.92 rows=401 width=239) (actual time=226.986..270.136 rows=122 loops=1)
                                       Merge Cond: (msg.unit_id = p.unit_id)
                                       Join Filter: ((p."time" >= (msg.gmt_date - '03:30:00'::interval)) AND (p."time" <= (msg.gmt_date + '03:30:00'::interval)))
                                       ->  Sort  (cost=3590.40..3600.48 rows=4033 width=219) (actual time=16.101..16.804 rows=995 loops=1)
                                             Sort Key: msg.unit_id
                                             Sort Method:  quicksort  Memory: 1175kB
                                             ->  Index Scan using message_time_index on messages msg  (cost=0.01..3348.87 rows=4033 width=219) (actual time=0.046..13.246 rows=2406 loops=1)
                                                   Index Cond: ((gmt_date >= ((now() - '05:00:00'::interval) - '1 day'::interval)) AND (gmt_date <= now()))
                                                   Filter: ((at_location IS NULL) AND (msgtype = 1100))
                                       ->  Sort  (cost=5315.44..5360.69 rows=18099 width=28) (actual time=174.411..214.326 rows=62356 loops=1)
                                             Sort Key: p.unit_id
                                             Sort Method:  quicksort  Memory: 6413kB
                                             ->  Index Scan using insert_time_point on point p  (cost=0.01..4035.51 rows=18099 width=28) (actual time=0.064..112.710 rows=62417 loops=1)
                                                   Index Cond: ((insert_time >= (now() - '05:00:00'::interval)) AND (insert_time <= now()))
                                                   Filter: (knots < 5)
                                 ->  Index Scan using unit_pkey on unit u  (cost=0.00..3.47 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=122)
                                       Index Cond: (u.id = msg.unit_id)
                           ->  Hash  (cost=1971.74..1971.74 rows=886 width=12) (actual time=28.816..28.816 rows=6677 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 287kB
                                 ->  Hash Join  (cost=1035.37..1971.74 rows=886 width=12) (actual time=5.308..24.184 rows=6677 loops=1)
                                       Hash Cond: (si.owner_id = us.id)
                                       ->  Seq Scan on shared_item si  (cost=0.00..901.16 rows=7026 width=8) (actual time=0.009..9.542 rows=7031 loops=1)
                                             Filter: (type = 'C'::bpchar)
                                       ->  Hash  (cost=1026.19..1026.19 rows=735 width=12) (actual time=5.290..5.290 rows=735 loops=1)
                                             Buckets: 1024  Batches: 1  Memory Usage: 32kB
                                             ->  Hash Join  (cost=136.17..1026.19 rows=735 width=12) (actual time=1.246..4.753 rows=735 loops=1)
                                                   Hash Cond: (us.customer_id = c.id)
                                                   ->  Seq Scan on users us  (cost=0.00..879.91 rows=735 width=8) (actual time=0.003..2.531 rows=735 loops=1)
                                                         Filter: ((name)::text = 'main'::text)
                                                   ->  Hash  (cost=126.63..126.63 rows=763 width=4) (actual time=1.235..1.235 rows=763 loops=1)
                                                         Buckets: 1024  Batches: 1  Memory Usage: 27kB
                                                         ->  Seq Scan on customers c  (cost=0.00..126.63 rows=763 width=4) (actual time=0.003..0.740 rows=763 loops=1)
 Total runtime: 10998.940 ms
(47 rows)

Per node type stats

node type	count	sum of times	% of query
Hash	5	2509.661 ms	22.8 %
Hash Join	5	6745.069 ms	61.3 %
Index Scan	3	126.444 ms	1.1 %
Merge Join	1	39.006 ms	0.4 %
Nested Loop	1	0.364 ms	0.0 %
Seq Scan	5	1472.993 ms	13.4 %
Sort	2	105.174 ms	1.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
customers	1	0.740 ms	0.0 %
Seq Scan	1	0.740 ms	100.0 %
map_marker	1	464.426 ms	4.2 %
Seq Scan	1	464.426 ms	100.0 %
messages	1	13.246 ms	0.1 %
Index Scan	1	13.246 ms	100.0 %
point	1	112.710 ms	1.0 %
Index Scan	1	112.710 ms	100.0 %
shared_item	1	9.542 ms	0.1 %
Seq Scan	1	9.542 ms	100.0 %
shared_members	1	995.754 ms	9.1 %
Seq Scan	1	995.754 ms	100.0 %
unit	1	0.488 ms	0.0 %
Index Scan	1	0.488 ms	100.0 %
users	1	2.531 ms	0.0 %
Seq Scan	1	2.531 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: FGF

Colorize:

Columns visibility:

Per node type stats

Per table stats