explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gy5M

Settings
# exclusive inclusive rows x rows loops node
1. 7,466.060 7,467.208 ↑ 1.0 1 1

Function Scan on central._generate_daily_machine_report (cost=0.25..0.28 rows=1 width=168) (actual time=7,467.208..7,467.208 rows=1 loops=1)

  • Output: result_report_id, result_profile1, result_profile2, result_profile3, result_profile4, result_profile5
  • Function Call: central._generate_daily_machine_report('33 days'::interval, 'Pacific/Auckland'::character varying(32), '2019-05-14 12:00:00'::timestamp without time zone, '2019-05-15 11:59:59.999999'::timestamp without time zone, $287, $267, $272, true, 40, 1000, 10000, 100000, 5, 2, '2019-05-15'::date, false)
  • Output: ee.ebox_event_type_id, ee.event_timestamp, ee.longitude, ee.latitude, ee.course_over_ground, ee.speed_gps_kph, ee.odometer_total, ee.*, ee.road_segment_id, ee.distance_from_road_segment, ee.gps_accuracy, ee.id, ee.machine_id
2. 0.016 0.016 ↑ 1.0 6 1

Seq Scan on central.ebox_event_type (cost=0.00..5.25 rows=6 width=4) (actual time=0.005..0.016 rows=6 loops=1)

  • Output: ebox_event_type.id, ebox_event_type.name, ebox_event_type.ebox_event_type_group_id, ebox_event_type.description
  • Filter: (ebox_event_type.ebox_event_type_group_id = 2)
  • Rows Removed by Filter: 94
3. 0.012 0.222 ↑ 8.0 2 6

Append (cost=0.00..128.44 rows=16 width=109) (actual time=0.030..0.037 rows=2 loops=6)

4. 0.000 0.000 ↓ 0.0 0 6

Seq Scan on central.ebox_event ee (cost=0.00..0.00 rows=1 width=870) (actual time=0.000..0.000 rows=0 loops=6)

  • Output: ee.ebox_event_type_id, ee.event_timestamp, ee.longitude, ee.latitude, ee.course_over_ground, ee.speed_gps_kph, ee.odometer_total, ee.*, ee.road_segment_id, ee.distance_from_road_segment, ee.gps_accuracy, ee.id, ee.machine_id
  • Filter: ((ee.distance_from_public_road_segment < 20) AND (ee.gps_accuracy < 25) AND (ee.event_timestamp >= '2019-05-12 12:00:00'::timestamp without time zone) AND (ee.event_timestamp <= '2019-05-13 11:59:59.999999'::timestamp without time zone) AND (ee.speed_gps_kph > 50) AND (ee.speed_gps_kph < 180) AND (ee.machine_id = '47370'::bigint) AND (ebox_event_type.id = ee.ebox_event_type_id))
5. 0.210 0.210 ↑ 7.5 2 6

Index Scan using idx_machine_event_timestamp_type_201905 on central.ebox_event_201905 ee_1 (cost=0.70..128.44 rows=15 width=108) (actual time=0.029..0.035 rows=2 loops=6)

  • Output: ee_1.ebox_event_type_id, ee_1.event_timestamp, ee_1.longitude, ee_1.latitude, ee_1.course_over_ground, ee_1.speed_gps_kph, ee_1.odometer_total, ee_1.*, ee_1.road_segment_id, ee_1.distance_from_road_segment, ee_1.gps_accuracy, ee_1.id, ee_1.machine_id
  • Index Cond: ((ee_1.machine_id = '47370'::bigint) AND (ee_1.event_timestamp >= '2019-05-12 12:00:00'::timestamp without time zone) AND (ee_1.event_timestamp <= '2019-05-13 11:59:59.999999'::timestamp without time zone) AND (ee_1.ebox_event_type_id = ebox_event_type.id))
  • Filter: ((ee_1.distance_from_public_road_segment < 20) AND (ee_1.gps_accuracy < 25) AND (ee_1.speed_gps_kph > 50) AND (ee_1.speed_gps_kph < 180))
  • Rows Removed by Filter: 16
6. 0.910 0.910 ↑ 1.0 1 14

Index Scan using cc1207087229650 on central.machine m (cost=0.42..5.46 rows=1 width=13) (actual time=0.065..0.065 rows=1 loops=14)

  • Output: m.id, m.version, m.created, m.last_modified, m.make, m.model, m.machine_type, m.asset_type_id, m.status, m.status_last_modified, m.icon_name, m.piggyback_enabled, m.engine_hours, m.vin, m.year_of_manufacture, m.serial_number, m.email, m.vehicle_weight_type, m.is_ruc_vehicle, m.axle_count, m.gid, m.fuel_type, m.unladen_weight, m.gross_vehicle_weight, m.gross_combination_weight, m.hvut_taxable_gross_weight, m.hvut_category, m.seats, m.vehicle_type, m.body_style, m.title_number, m.factory_price
  • Index Cond: (m.id = '47370'::bigint)
  • Filter: ((m.vehicle_weight_type IS NULL) OR ((m.vehicle_weight_type)::text = ANY ('{HEAVY,LIGHT}'::text[])))
7. 0.000 0.000 ↓ 0.0 0

Materialize (cost=100.00..3,397,148.98 rows=362,869 width=139) (never executed)

  • Output: rs.geometry, rs.speed
8. 0.000 0.000 ↓ 0.0 0

Foreign Scan on central.road_segment rs (cost=100.00..3,387,892.63 rows=362,869 width=139) (never executed)

  • Output: rs.geometry, rs.speed
  • Remote SQL: SELECT speed, geometry FROM central.road_segment_copy WHERE ((road_segment_load_id = ANY ('{16013}'::bigint[])))
9.          

SubPlan (forFunction Scan)

10. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.56..7.54 rows=1 width=16) (never executed)

  • Output: dl.driver_id, dl.login_timestamp
11. 0.000 0.000 ↓ 0.0 0

Index Scan Backward using idx_driver_log_machine_time on central.driver_log dl (cost=0.56..1,305.48 rows=187 width=16) (never executed)

  • Output: dl.driver_id, dl.login_timestamp
  • Index Cond: ((dl.organisation_machine_id = ohm.id) AND (dl.login_timestamp <= ee.event_timestamp))
  • Filter: ((dl.logout_timestamp IS NULL) OR (dl.logout_timestamp >= ee.event_timestamp))