explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5rWk

Settings
# exclusive inclusive rows x rows loops node
1. 73.693 1,815.842 ↑ 1.0 1 1

Aggregate (cost=3,974,795.30..3,974,795.37 rows=1 width=152) (actual time=1,815.842..1,815.842 rows=1 loops=1)

  • Output: '0018F52AABAC'::character varying, avg((CASE WHEN (gps_infos.speed = 0) THEN NULL::integer ELSE gps_infos.speed END)), sum((CASE WHEN ((gps_infos.speed = 0) AND (lag(gps_infos.speed) OVER (?) > 0)) THEN 1 ELSE 0 END)), COALESCE(((date_part('epoch'::text, sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.speed = 0)) THEN (gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) ELSE '00:00:00'::interval END))) / date_part('epoch'::text, (sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.speed = 0)) THEN (gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) ELSE '00:00:00'::interval END)) + sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.speed > 0)) THEN (gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) ELSE '00:00:00'::interval END))))) * '100'::double precision), '0'::double precision), ((sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.lat <> '0'::double precision) AND (gps_infos.lng <> '0'::double precision) AND (lag(gps_infos.lat) OVER (?) <> '0'::double precision) AND (lag(gps_infos.lng) OVER (?) <> '0'::double precision)) THEN st_distance(geography(st_geomfromtext((((('POINT('::text || (gps_infos.lng)::text) || ' '::text) || (gps_infos.lat)::text) || ')'::text), 4326)), geography(st_geomfromtext((((('POINT('::text || (lag(gps_infos.lng) OVER (?))::text) || ' '::text) || (lag(gps_infos.lat) OVER (?))::text) || ')'::text), 4326)), false) ELSE '0'::double precision END)) / '1000'::double precision) * '0.621371'::double precision), date_part('epoch'::text, (sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.speed > 0)) THEN (gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) ELSE '00:00:00'::interval END)) + sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.speed = 0)) THEN (gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) ELSE '00:00:00'::interval END)))), date_part('epoch'::text, sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.speed > 0)) THEN (gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) ELSE '00:00:00'::interval END))), date_part('epoch'::text, sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.speed = 0)) THEN (gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) ELSE '00:00:00'::interval END))), ($0 * ((sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.lat <> '0'::double precision) AND (gps_infos.lng <> '0'::double precision) AND (lag(gps_infos.lat) OVER (?) <> '0'::double precision) AND (lag(gps_infos.lng) OVER (?) <> '0'::double precision)) THEN st_distance(geography(st_geomfromtext((((('POINT('::text || (gps_infos.lng)::text) || ' '::text) || (gps_infos.lat)::text) || ')'::text), 4326)), geography(st_geomfromtext((((('POINT('::text || (lag(gps_infos.lng) OVER (?))::text) || ' '::text) || (lag(gps_infos.lat) OVER (?))::text) || ')'::text), 4326)), false) ELSE '0'::double precision END)) / '1000'::double precision) * '0.621371'::double precision)), '0'::double precision, ((($1)::double precision * (date_part('epoch'::text, sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.speed = 0)) THEN (gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) ELSE '00:00:00'::interval END))) / '3600'::double precision)) + ((date_part('epoch'::text, sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.speed = 0)) THEN (gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) ELSE '00:00:00'::interval END))) / '3600'::double precision) * '2.33'::double precision)), $2, $3
  • Filter: (sum((CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.lat <> '0'::double precision) AND (gps_infos.lng <> '0'::double precision) AND (lag(gps_infos.lat) OVER (?) <> '0'::double precision) AND (lag(gps_infos.lng) OVER (?) <> '0'::double precision)) THEN st_distance(geography(st_geomfromtext((((('POINT('::text || (gps_infos.lng)::text) || ' '::text) || (gps_infos.lat)::text) || ')'::text), 4326)), geography(st_geomfromtext((((('POINT('::text || (lag(gps_infos.lng) OVER (?))::text) || ' '::text) || (lag(gps_infos.lat) OVER (?))::text) || ')'::text), 4326)), false) ELSE '0'::double precision END)) > '0'::double precision)
  • Buffers: shared hit=285625
  • Settings: effective_cache_size = '84GB', effective_io_concurrency = '200', jit = 'off', max_parallel_workers = '54', max_parallel_workers_per_gather = '27', random_page_cost = '1', work_mem = '16MB'
2.          

Initplan (for Aggregate)

3. 0.014 0.039 ↑ 1.0 1 1

Hash Right Join (cost=2.33..3.45 rows=1 width=8) (actual time=0.035..0.039 rows=1 loops=1)

  • Output: CASE WHEN (vehicles.mpg = '0'::double precision) THEN '0'::double precision ELSE ((fuel_types.ppg)::double precision / vehicles.mpg) END
  • Inner Unique: true
  • Hash Cond: ((fuel_types.code)::text = (vehicles.fuel_type_code)::text)
  • Buffers: shared hit=4
4. 0.009 0.009 ↑ 1.0 8 1

Seq Scan on public.fuel_types (cost=0.00..1.08 rows=8 width=12) (actual time=0.008..0.009 rows=8 loops=1)

  • Output: fuel_types.code, fuel_types.name, fuel_types.ppg, fuel_types.created_at, fuel_types.updated_at
  • Buffers: shared hit=1
5. 0.003 0.016 ↑ 1.0 1 1

Hash (cost=2.31..2.31 rows=1 width=15) (actual time=0.016..0.016 rows=1 loops=1)

  • Output: vehicles.mpg, vehicles.fuel_type_code
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
6. 0.013 0.013 ↑ 1.0 1 1

Index Scan using events_mac_id_organization_id_idx on public.vehicles (cost=0.28..2.31 rows=1 width=15) (actual time=0.012..0.013 rows=1 loops=1)

  • Output: vehicles.mpg, vehicles.fuel_type_code
  • Index Cond: ((vehicles.mac_id)::text = '0018F52AABAC'::text)
  • Buffers: shared hit=3
7. 0.008 0.019 ↑ 1.0 1 1

Hash Right Join (cost=2.33..3.45 rows=1 width=32) (actual time=0.016..0.019 rows=1 loops=1)

  • Output: ((fuel_types_1.ppg * (vehicles_1.engine_size)::numeric) * 0.04)
  • Inner Unique: true
  • Hash Cond: ((fuel_types_1.code)::text = (vehicles_1.fuel_type_code)::text)
  • Buffers: shared hit=4
8. 0.003 0.003 ↑ 1.0 8 1

Seq Scan on public.fuel_types fuel_types_1 (cost=0.00..1.08 rows=8 width=12) (actual time=0.002..0.003 rows=8 loops=1)

  • Output: fuel_types_1.code, fuel_types_1.name, fuel_types_1.ppg, fuel_types_1.created_at, fuel_types_1.updated_at
  • Buffers: shared hit=1
9. 0.001 0.008 ↑ 1.0 1 1

Hash (cost=2.31..2.31 rows=1 width=11) (actual time=0.008..0.008 rows=1 loops=1)

  • Output: vehicles_1.engine_size, vehicles_1.fuel_type_code
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
10. 0.007 0.007 ↑ 1.0 1 1

Index Scan using events_mac_id_organization_id_idx on public.vehicles vehicles_1 (cost=0.28..2.31 rows=1 width=11) (actual time=0.006..0.007 rows=1 loops=1)

  • Output: vehicles_1.engine_size, vehicles_1.fuel_type_code
  • Index Cond: ((vehicles_1.mac_id)::text = '0018F52AABAC'::text)
  • Buffers: shared hit=3
11. 0.002 0.017 ↑ 1.0 1 1

Aggregate (cost=817.09..817.10 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=1)

  • Output: count(event_history.id)
  • Buffers: shared hit=4
12. 0.015 0.015 ↓ 0.0 0 1

Index Scan using event_history_vehicle_id_type_device_date_time_idx on public.event_history (cost=0.57..816.74 rows=141 width=8) (actual time=0.015..0.015 rows=0 loops=1)

  • Output: event_history.id, event_history.vehicle_id, event_history.type, event_history.date_time, event_history.location, event_history.follow_up, event_history.notes, event_history.road_number, event_history.road, event_history.city, event_history.state, event_history.zip, event_history.county, event_history.country, event_history.country_code, event_history.details, event_history.created_at, event_history.updated_at, event_history.orig_time_zone, event_history.organization_id, event_history.device_date_time, event_history.speed_mph, event_history.driver_person_id
  • Index Cond: ((event_history.vehicle_id = 6368) AND ((event_history.type)::text = 'geo_fencing'::text))
  • Filter: ((event_history.created_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (event_history.created_at <= '2020-06-01 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=4
13. 0.001 0.010 ↑ 1.0 1 1

Aggregate (cost=853.74..853.75 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)

  • Output: count(event_history_1.id)
  • Buffers: shared hit=4
14. 0.009 0.009 ↓ 0.0 0 1

Index Scan using event_history_vehicle_id_type_device_date_time_idx on public.event_history event_history_1 (cost=0.57..853.37 rows=147 width=8) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: event_history_1.id, event_history_1.vehicle_id, event_history_1.type, event_history_1.date_time, event_history_1.location, event_history_1.follow_up, event_history_1.notes, event_history_1.road_number, event_history_1.road, event_history_1.city, event_history_1.state, event_history_1.zip, event_history_1.county, event_history_1.country, event_history_1.country_code, event_history_1.details, event_history_1.created_at, event_history_1.updated_at, event_history_1.orig_time_zone, event_history_1.organization_id, event_history_1.device_date_time, event_history_1.speed_mph, event_history_1.driver_person_id
  • Index Cond: ((event_history_1.vehicle_id = 6368) AND ((event_history_1.type)::text = 'speed_limit'::text))
  • Filter: ((event_history_1.created_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (event_history_1.created_at <= '2020-06-01 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=4
15. 1,445.912 1,742.064 ↓ 2.2 283,674 1

WindowAgg (cost=0.70..3,967,382.76 rows=127,440 width=56) (actual time=0.082..1,742.064 rows=283,674 loops=1)

  • Output: CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.speed = 0)) THEN (gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) ELSE '00:00:00'::interval END, CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.speed > 0)) THEN (gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) ELSE '00:00:00'::interval END, CASE WHEN ((gps_infos.speed = 0) AND (lag(gps_infos.speed) OVER (?) > 0)) THEN 1 ELSE 0 END, CASE WHEN (((gps_infos.date_time - lag(gps_infos.date_time) OVER (?)) < '00:05:00'::interval) AND (gps_infos.lat <> '0'::double precision) AND (gps_infos.lng <> '0'::double precision) AND (lag(gps_infos.lat) OVER (?) <> '0'::double precision) AND (lag(gps_infos.lng) OVER (?) <> '0'::double precision)) THEN st_distance(geography(st_geomfromtext((((('POINT('::text || (gps_infos.lng)::text) || ' '::text) || (gps_infos.lat)::text) || ')'::text), 4326)), geography(st_geomfromtext((((('POINT('::text || (lag(gps_infos.lng) OVER (?))::text) || ' '::text) || (lag(gps_infos.lat) OVER (?))::text) || ')'::text), 4326)), false) ELSE '0'::double precision END, CASE WHEN (gps_infos.speed = 0) THEN NULL::integer ELSE gps_infos.speed END, gps_infos.date_time
  • Buffers: shared hit=285609
16. 296.152 296.152 ↓ 2.2 283,674 1

Index Scan using gps_infos_mac_id_date_time_idx on public.gps_infos (cost=0.70..130,164.36 rows=127,440 width=28) (actual time=0.054..296.152 rows=283,674 loops=1)

  • Output: gps_infos.id, gps_infos.mac_id, gps_infos.date_time, gps_infos.lat, gps_infos.lng, gps_infos.created_at, gps_infos.updated_at, gps_infos.time_zone, gps_infos.speed, gps_infos.timezone, gps_infos.road_number, gps_infos.road, gps_infos.city, gps_infos.state, gps_infos.zip, gps_infos.county, gps_infos.country, gps_infos.country_code, gps_infos.vehicle_id
  • Index Cond: (((gps_infos.mac_id)::text = '0018F52AABAC'::text) AND (gps_infos.date_time >= '2020-01-01 00:00:00'::timestamp without time zone) AND (gps_infos.date_time <= '2020-06-01 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=285603
Planning time : 1.692 ms
Execution time : 1,816.053 ms