explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3N5p

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 227,290.860 ↑ 1.0 1 1

Limit (cost=103.71..1,419.27 rows=1 width=509) (actual time=227,290.778..227,290.860 rows=1 loops=1)

2. 5.862 227,290.851 ↑ 1,000.0 1 1

Result (cost=103.71..1,315,670.15 rows=1,000 width=509) (actual time=227,290.770..227,290.851 rows=1 loops=1)

3. 10.979 227,284.877 ↑ 1,000.0 1 1

ProjectSet (cost=103.71..1,302,424.46 rows=1,000 width=737) (actual time=227,284.802..227,284.877 rows=1 loops=1)

4. 0.084 227,273.898 ↑ 1.0 1 1

Nested Loop Left Join (cost=103.71..1,302,419.16 rows=1 width=709) (actual time=227,273.826..227,273.898 rows=1 loops=1)

  • Join Filter: (rec.event_id = events.event_id)
5. 0.033 227,273.800 ↑ 1.0 1 1

Nested Loop (cost=103.71..1,302,417.29 rows=1 width=648) (actual time=227,273.729..227,273.800 rows=1 loops=1)

  • Join Filter: (vbsu.business_unit_id = bsu.business_unit_id)
  • Rows Removed by Join Filter: 9
6. 0.042 227,273.755 ↑ 1.0 1 1

Nested Loop (cost=103.71..1,302,416.04 rows=1 width=580) (actual time=227,273.685..227,273.755 rows=1 loops=1)

  • Join Filter: (vc.company_id = c.company_id)
  • Rows Removed by Join Filter: 1
7. 0.051 227,273.680 ↑ 1.0 1 1

Nested Loop (cost=103.71..1,302,414.93 rows=1 width=548) (actual time=227,273.611..227,273.680 rows=1 loops=1)

  • Join Filter: (v_d.vehicle_id = vbsu.vehicle_id)
  • Rows Removed by Join Filter: 12
8. 0.590 227,273.524 ↑ 10.0 1 1

Nested Loop Left Join (cost=103.71..1,302,413.68 rows=10 width=552) (actual time=227,273.456..227,273.524 rows=1 loops=1)

  • Join Filter: (d.serial_dev = (nw.idequipo)::text)
  • Rows Removed by Join Filter: 2,146
9. 0.016 227,200.064 ↑ 10.0 1 1

Nested Loop (cost=3.71..1,302,133.95 rows=10 width=520) (actual time=227,199.998..227,200.064 rows=1 loops=1)

  • Join Filter: (d.model_id = mod.model_id)
  • Rows Removed by Join Filter: 3
10. 0.022 227,199.999 ↑ 10.0 1 1

Nested Loop (cost=3.71..1,302,132.37 rows=10 width=488) (actual time=227,199.934..227,199.999 rows=1 loops=1)

  • Join Filter: (d.device_status_id = dst.device_status_id)
  • Rows Removed by Join Filter: 6
11. 0.358 227,199.938 ↑ 10.0 1 1

Nested Loop (cost=3.71..1,302,130.17 rows=10 width=456) (actual time=227,199.877..227,199.938 rows=1 loops=1)

  • Join Filter: (rec.device_id = d.device_id)
  • Rows Removed by Join Filter: 2,784
12. 0.020 227,195.201 ↑ 10.0 1 1

Nested Loop (cost=3.71..1,301,106.80 rows=10 width=436) (actual time=227,195.141..227,195.201 rows=1 loops=1)

  • Join Filter: (v_d.vehicle_id = veh.vehicle_id)
  • Rows Removed by Join Filter: 44
13. 0.019 227,195.086 ↑ 13.0 1 1

Nested Loop (cost=3.71..1,301,093.46 rows=13 width=417) (actual time=227,195.027..227,195.086 rows=1 loops=1)

  • Join Filter: (v_d.vehicle_id = vc.vehicle_id)
  • Rows Removed by Join Filter: 9
14. 0.015 227,195.007 ↑ 43.0 1 1

Nested Loop (cost=3.71..1,301,085.89 rows=43 width=405) (actual time=227,194.949..227,195.007 rows=1 loops=1)

15. 0.068 227,194.939 ↑ 43.0 1 1

Merge Append (cost=3.71..1,301,083.95 rows=43 width=394) (actual time=227,194.883..227,194.939 rows=1 loops=1)

  • Sort Key: rec.ts_rec DESC
16. 0.017 0.017 ↓ 0.0 0 1

Index Scan Backward using udx_ts__device__records_data on records_data rec (cost=0.12..3.15 rows=1 width=92) (actual time=0.013..0.017 rows=0 loops=1)

  • Index Cond: (ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone)
  • Filter: ((vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
17. 0.599 0.599 ↓ 0.0 0 1

Index Scan Backward using idx_ts_rec__vehicle_device__records_data_202006 on records_data_202006 rec_1 (cost=0.28..187.96 rows=1 width=438) (actual time=0.595..0.599 rows=0 loops=1)

  • Index Cond: ((ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone) AND (vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
18. 0.430 0.430 ↓ 0.0 0 1

Index Scan Backward using idx_ts_rec__vehicle_device__records_data_202005 on records_data_202005 rec_2 (cost=0.28..91.07 rows=1 width=622) (actual time=0.427..0.430 rows=0 loops=1)

  • Index Cond: ((ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone) AND (vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
19. 338.465 338.465 ↓ 0.0 0 1

Index Scan Backward using idx_ts_rec__vehicle_device__records_data_202008 on records_data_202008 rec_3 (cost=0.43..91,257.69 rows=1 width=410) (actual time=338.462..338.465 rows=0 loops=1)

  • Index Cond: ((ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone) AND (vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
20. 2.172 2.172 ↓ 0.0 0 1

Index Scan Backward using idx_ts_rec__vehicle_device__records_data_202007 on records_data_202007 rec_4 (cost=0.29..381.99 rows=1 width=512) (actual time=2.169..2.172 rows=0 loops=1)

  • Index Cond: ((ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone) AND (vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
21. 0.068 0.068 ↓ 0.0 0 1

Index Scan Backward using udx_ts__device__records_data_201912 on records_data_201912 rec_5 (cost=0.12..3.15 rows=1 width=92) (actual time=0.068..0.068 rows=0 loops=1)

  • Index Cond: (ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone)
  • Filter: ((vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
  • Rows Removed by Filter: 1
22. 0.055 0.055 ↓ 0.0 0 1

Index Scan Backward using idx_ts_rec__vehicle_device__records_data_202003 on records_data_202003 rec_6 (cost=0.14..3.21 rows=1 width=92) (actual time=0.055..0.055 rows=0 loops=1)

  • Index Cond: ((ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone) AND (vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
23. 0.153 0.153 ↓ 0.0 0 1

Index Scan Backward using idx_ts_rec__vehicle_device__records_data_202001 on records_data_202001 rec_7 (cost=0.28..26.21 rows=1 width=613) (actual time=0.153..0.153 rows=0 loops=1)

  • Index Cond: ((ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone) AND (vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
24. 0.035 0.035 ↓ 0.0 0 1

Index Scan Backward using idx_ts_rec__vehicle_device__records_data_202004 on records_data_202004 rec_8 (cost=0.14..5.58 rows=1 width=271) (actual time=0.032..0.035 rows=0 loops=1)

  • Index Cond: ((ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone) AND (vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
25. 226,755.288 226,755.288 ↓ 0.0 0 1

Index Scan Backward using idx_ts_rec__vehicle_device__records_data_202009 on records_data_202009 rec_9 (cost=0.56..816,254.26 rows=24 width=399) (actual time=226,755.288..226,755.288 rows=0 loops=1)

  • Index Cond: ((ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone) AND (vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
26. 77.857 77.857 ↓ 0.0 0 1

Index Scan Backward using idx_ts_rec__vehicle_device__records_data_202002 on records_data_202002 rec_10 (cost=0.28..23.81 rows=1 width=615) (actual time=77.857..77.857 rows=0 loops=1)

  • Index Cond: ((ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone) AND (vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
27. 19.732 19.732 ↑ 9.0 1 1

Index Scan Backward using idx_ts_rec__vehicle_device__records_data_202010 on records_data_202010 rec_11 (cost=0.56..392,844.68 rows=9 width=400) (actual time=19.732..19.732 rows=1 loops=1)

  • Index Cond: ((ts_rec < '2020-10-15 06:26:00-03'::timestamp with time zone) AND (vehicle_device_id IS NOT NULL) AND (vehicle_device_id = 64))
28. 0.011 0.053 ↑ 1.0 1 1

Materialize (cost=0.00..1.40 rows=1 width=16) (actual time=0.052..0.053 rows=1 loops=1)

29. 0.042 0.042 ↑ 1.0 1 1

Seq Scan on vehicles_devices v_d (cost=0.00..1.40 rows=1 width=16) (actual time=0.042..0.042 rows=1 loops=1)

  • Filter: (vehicle_device_id = 64)
  • Rows Removed by Filter: 31
30. 0.023 0.060 ↑ 1.0 10 1

Materialize (cost=0.00..1.15 rows=10 width=12) (actual time=0.041..0.060 rows=10 loops=1)

31. 0.037 0.037 ↑ 1.0 10 1

Seq Scan on vehicles_companies vc (cost=0.00..1.10 rows=10 width=12) (actual time=0.035..0.037 rows=10 loops=1)

32. 0.029 0.095 ↑ 1.0 45 1

Materialize (cost=0.00..4.67 rows=45 width=19) (actual time=0.042..0.095 rows=45 loops=1)

33. 0.066 0.066 ↑ 1.0 45 1

Seq Scan on vehicles veh (cost=0.00..4.45 rows=45 width=19) (actual time=0.036..0.066 rows=45 loops=1)

34. 1.606 4.379 ↑ 1.1 2,785 1

Materialize (cost=0.00..568.03 rows=3,087 width=24) (actual time=0.041..4.379 rows=2,785 loops=1)

35. 2.773 2.773 ↑ 1.1 2,785 1

Seq Scan on devices d (cost=0.00..552.60 rows=3,087 width=24) (actual time=0.036..2.773 rows=2,785 loops=1)

  • Filter: (device_status_id <> 7)
36. 0.006 0.039 ↑ 1.1 7 1

Materialize (cost=0.00..1.12 rows=8 width=36) (actual time=0.035..0.039 rows=7 loops=1)

37. 0.033 0.033 ↑ 1.1 7 1

Seq Scan on device_status dst (cost=0.00..1.08 rows=8 width=36) (actual time=0.032..0.033 rows=7 loops=1)

38. 0.010 0.049 ↑ 1.0 4 1

Materialize (cost=0.00..1.06 rows=4 width=36) (actual time=0.047..0.049 rows=4 loops=1)

39. 0.039 0.039 ↑ 1.0 4 1

Seq Scan on models mod (cost=0.00..1.04 rows=4 width=36) (actual time=0.038..0.039 rows=4 loops=1)

40. 1.567 72.870 ↓ 2.3 2,147 1

Materialize (cost=100.00..142.55 rows=930 width=64) (actual time=26.066..72.870 rows=2,147 loops=1)

41. 71.303 71.303 ↓ 2.3 2,147 1

Foreign Scan on frg_nwequipos nw (cost=100.00..137.90 rows=930 width=64) (actual time=26.050..71.303 rows=2,147 loops=1)

42. 0.036 0.105 ↓ 13.0 13 1

Materialize (cost=0.00..1.10 rows=1 width=8) (actual time=0.066..0.105 rows=13 loops=1)

43. 0.069 0.069 ↓ 13.0 13 1

Seq Scan on vehicles_business_units vbsu (cost=0.00..1.10 rows=1 width=8) (actual time=0.062..0.069 rows=13 loops=1)

  • Filter: (to_date_vbsu IS NULL)
44. 0.033 0.033 ↑ 2.5 2 1

Seq Scan on companies c (cost=0.00..1.05 rows=5 width=36) (actual time=0.033..0.033 rows=2 loops=1)

45. 0.012 0.012 ↑ 1.1 10 1

Seq Scan on business_units bsu (cost=0.00..1.11 rows=11 width=72) (actual time=0.011..0.012 rows=10 loops=1)

46. 0.014 0.014 ↑ 37.0 1 1

Seq Scan on events (cost=0.00..1.37 rows=37 width=36) (actual time=0.014..0.014 rows=1 loops=1)

47.          

SubPlan (for Result)

48. 0.014 0.112 ↑ 1.0 1 1

Aggregate (cost=11.66..11.67 rows=1 width=32) (actual time=0.107..0.112 rows=1 loops=1)

49. 0.013 0.098 ↓ 0.0 0 1

Nested Loop (cost=9.26..11.65 rows=1 width=64) (actual time=0.093..0.098 rows=0 loops=1)

  • Join Filter: (rg.geofence_id = geo.geofence_id)
50. 0.013 0.085 ↓ 0.0 0 1

Merge Join (cost=9.12..10.40 rows=1 width=8) (actual time=0.081..0.085 rows=0 loops=1)

  • Merge Cond: (rg.geofence_id = gvc.geofence_id)
51. 0.072 0.072 ↓ 0.0 0 1

Index Scan using idx_records_geofence__record_id on records_geofence rg (cost=0.29..6.28 rows=5 width=4) (actual time=0.072..0.072 rows=0 loops=1)

  • Index Cond: (record_id = rec.record_id)
  • Filter: ((data_rge ->> 'time_in_sum'::text) IS NOT NULL)
52. 0.000 0.000 ↓ 0.0 0

Sort (cost=8.83..8.86 rows=14 width=4) (never executed)

  • Sort Key: gvc.geofence_id
53. 0.000 0.000 ↓ 0.0 0

Seq Scan on geofences_vehicles_companies gvc (cost=0.00..8.56 rows=14 width=4) (never executed)

  • Filter: ((to_gvc IS NULL) AND (vehicle_company_id = vc.vehicle_company_id))
54. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_geofences on geofences geo (cost=0.14..1.23 rows=1 width=68) (never executed)

  • Index Cond: (geofence_id = gvc.geofence_id)
  • Filter: (company_id = bsu.company_id)
Planning time : 8.619 ms