explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mYqE

Settings
# exclusive inclusive rows x rows loops node
1. 2.063 109.909 ↑ 6,444.4 9 1

Result (cost=85,937.49..1,579,892.93 rows=58,000 width=870) (actual time=107.295..109.909 rows=9 loops=1)

2. 0.529 107.252 ↑ 6,444.4 9 1

ProjectSet (cost=85,937.49..86,247.93 rows=58,000 width=1,186) (actual time=106.835..107.252 rows=9 loops=1)

3. 0.013 106.723 ↑ 6.4 9 1

Result (cost=85,937.49..85,940.68 rows=58 width=1,158) (actual time=106.709..106.723 rows=9 loops=1)

4. 0.042 106.710 ↑ 6.4 9 1

Sort (cost=85,937.49..85,937.63 rows=58 width=1,158) (actual time=106.706..106.710 rows=9 loops=1)

  • Sort Key: rec.ts_rec DESC
  • Sort Method: quicksort Memory: 40kB
5. 0.102 106.668 ↑ 6.4 9 1

Hash Join (cost=2,663.45..85,935.79 rows=58 width=1,158) (actual time=86.751..106.668 rows=9 loops=1)

  • Hash Cond: (v_d.vehicle_id = vc.vehicle_id)
6. 0.099 106.385 ↑ 236.9 9 1

Hash Left Join (cost=2,658.65..85,920.46 rows=2,132 width=1,069) (actual time=86.548..106.385 rows=9 loops=1)

  • Hash Cond: (rec.position_id = positions.position_id)
7. 0.022 83.835 ↑ 236.9 9 1

Hash Left Join (cost=212.92..83,469.12 rows=2,132 width=993) (actual time=64.005..83.835 rows=9 loops=1)

  • Hash Cond: (d.serial_dev = (nw.idequipo)::text)
8. 0.012 21.804 ↑ 236.9 9 1

Hash Join (cost=63.39..83,053.80 rows=2,132 width=961) (actual time=1.985..21.804 rows=9 loops=1)

  • Hash Cond: (v_d.vehicle_id = veh.vehicle_id)
9. 0.207 21.775 ↑ 2,418.0 9 1

Hash Join (cost=61.74..82,994.20 rows=21,762 width=943) (actual time=1.963..21.775 rows=9 loops=1)

  • Hash Cond: (rec.vehicle_device_id = v_d.vehicle_device_id)
10. 0.391 21.556 ↑ 9,625.5 1,560 1

Nested Loop (cost=36.20..43,310.27 rows=15,015,729 width=939) (actual time=0.060..21.556 rows=1,560 loops=1)

11. 0.554 2.313 ↓ 1.0 1,571 1

Hash Join (cost=36.20..393.22 rows=1,570 width=97) (actual time=0.034..2.313 rows=1,571 loops=1)

  • Hash Cond: (d.model_id = mod.model_id)
12. 0.689 1.752 ↓ 1.0 1,571 1

Hash Join (cost=22.60..375.39 rows=1,570 width=65) (actual time=0.022..1.752 rows=1,571 loops=1)

  • Hash Cond: (d.device_status_id = dst.device_status_id)
13. 1.055 1.055 ↓ 1.0 1,571 1

Seq Scan on devices d (cost=0.00..348.64 rows=1,570 width=33) (actual time=0.009..1.055 rows=1,571 loops=1)

  • Filter: (device_status_id <> 7)
14. 0.002 0.008 ↑ 70.0 8 1

Hash (cost=15.60..15.60 rows=560 width=36) (actual time=0.008..0.008 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.006 0.006 ↑ 70.0 8 1

Seq Scan on device_status dst (cost=0.00..15.60 rows=560 width=36) (actual time=0.005..0.006 rows=8 loops=1)

16. 0.002 0.007 ↑ 40.0 4 1

Hash (cost=11.60..11.60 rows=160 width=36) (actual time=0.007..0.007 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.005 0.005 ↑ 40.0 4 1

Seq Scan on models mod (cost=0.00..11.60 rows=160 width=36) (actual time=0.004..0.005 rows=4 loops=1)

18. 6.284 18.852 ↑ 5.0 1 1,571

Append (cost=0.00..27.29 rows=5 width=851) (actual time=0.009..0.012 rows=1 loops=1,571)

19. 0.000 0.000 ↓ 0.0 0 1,571

Seq Scan on records_data rec (cost=0.00..0.00 rows=1 width=120) (actual time=0.000..0.000 rows=0 loops=1,571)

  • Filter: (d.record_id = record_id)
20. 3.142 3.142 ↓ 0.0 0 1,571

Index Scan using pk_records_data_202005 on records_data_202005 rec_1 (cost=0.43..8.23 rows=1 width=666) (actual time=0.002..0.002 rows=0 loops=1,571)

  • Index Cond: (record_id = d.record_id)
21. 6.284 6.284 ↑ 1.0 1 1,571

Index Scan using pk_records_data_202006 on records_data_202006 rec_2 (cost=0.43..8.42 rows=1 width=928) (actual time=0.004..0.004 rows=1 loops=1,571)

  • Index Cond: (record_id = d.record_id)
22. 1.571 1.571 ↓ 0.0 0 1,571

Index Scan using pk_records_data_202004 on records_data_202004 rec_3 (cost=0.28..5.64 rows=1 width=564) (actual time=0.001..0.001 rows=0 loops=1,571)

  • Index Cond: (record_id = d.record_id)
23. 1.571 1.571 ↓ 0.0 0 1,571

Index Scan using pk_records_data_202003 on records_data_202003 rec_4 (cost=0.28..4.98 rows=1 width=568) (actual time=0.001..0.001 rows=0 loops=1,571)

  • Index Cond: (record_id = d.record_id)
24. 0.003 0.012 ↓ 9.0 9 1

Hash (cost=25.52..25.52 rows=1 width=8) (actual time=0.012..0.012 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 0.009 0.009 ↓ 9.0 9 1

Seq Scan on vehicles_devices v_d (cost=0.00..25.52 rows=1 width=8) (actual time=0.006..0.009 rows=9 loops=1)

  • Filter: ((to_date_ved IS NULL) AND (vehicle_id = ANY ('{16,15,8,9,13,60,11,18,17,10}'::integer[])))
  • Rows Removed by Filter: 7
26. 0.007 0.017 ↑ 1.0 29 1

Hash (cost=1.29..1.29 rows=29 width=18) (actual time=0.017..0.017 rows=29 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
27. 0.010 0.010 ↑ 1.0 29 1

Seq Scan on vehicles veh (cost=0.00..1.29 rows=29 width=18) (actual time=0.005..0.010 rows=29 loops=1)

28. 1.877 62.009 ↓ 9.0 8,350 1

Hash (cost=137.90..137.90 rows=930 width=64) (actual time=62.009..62.009 rows=8,350 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 537kB
29. 60.132 60.132 ↓ 9.0 8,350 1

Foreign Scan on frg_nwequipos nw (cost=100.00..137.90 rows=930 width=64) (actual time=1.388..60.132 rows=8,350 loops=1)

30. 11.515 22.451 ↑ 1.0 50,891 1

Hash (cost=1,803.66..1,803.66 rows=51,366 width=84) (actual time=22.450..22.451 rows=50,891 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 6,395kB
31. 10.936 10.936 ↑ 1.0 50,891 1

Seq Scan on positions (cost=0.00..1,803.66 rows=51,366 width=84) (actual time=0.012..10.936 rows=50,891 loops=1)

32. 0.013 0.181 ↓ 10.0 10 1

Hash (cost=4.79..4.79 rows=1 width=72) (actual time=0.181..0.181 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.014 0.168 ↓ 10.0 10 1

Nested Loop (cost=1.04..4.79 rows=1 width=72) (actual time=0.117..0.168 rows=10 loops=1)

  • Join Filter: (vc.company_id = c.company_id)
  • Rows Removed by Join Filter: 10
34. 0.019 0.134 ↓ 10.0 10 1

Nested Loop (cost=1.04..3.72 rows=1 width=64) (actual time=0.112..0.134 rows=10 loops=1)

  • Join Filter: (vbsu.business_unit_id = bsu.business_unit_id)
  • Rows Removed by Join Filter: 134
35. 0.011 0.011 ↓ 4.0 4 1

Seq Scan on business_units bsu (cost=0.00..1.02 rows=1 width=48) (actual time=0.009..0.011 rows=4 loops=1)

  • Filter: (company_id = 2)
  • Rows Removed by Filter: 11
36. 0.042 0.104 ↓ 18.0 36 4

Hash Join (cost=1.04..2.67 rows=2 width=20) (actual time=0.010..0.026 rows=36 loops=4)

  • Hash Cond: (vbsu.vehicle_id = vc.vehicle_id)
37. 0.044 0.044 ↓ 1.6 37 4

Seq Scan on vehicles_business_units vbsu (cost=0.00..1.52 rows=23 width=8) (actual time=0.004..0.011 rows=37 loops=4)

  • Filter: (to_date_vbsu IS NULL)
  • Rows Removed by Filter: 36
38. 0.007 0.018 ↓ 14.0 28 1

Hash (cost=1.02..1.02 rows=2 width=12) (actual time=0.017..0.018 rows=28 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
39. 0.011 0.011 ↓ 14.0 28 1

Seq Scan on vehicles_companies vc (cost=0.00..1.02 rows=2 width=12) (actual time=0.005..0.011 rows=28 loops=1)

  • Filter: (to_date_veco IS NULL)
  • Rows Removed by Filter: 1
40. 0.020 0.020 ↑ 1.5 2 10

Seq Scan on companies c (cost=0.00..1.03 rows=3 width=12) (actual time=0.001..0.002 rows=2 loops=10)

41.          

SubPlan (for Result)

42. 0.045 0.594 ↑ 1.0 1 9

Aggregate (cost=24.16..24.17 rows=1 width=32) (actual time=0.066..0.066 rows=1 loops=9)

43. 0.019 0.549 ↑ 1.0 1 9

Nested Loop (cost=0.42..24.16 rows=1 width=64) (actual time=0.037..0.061 rows=1 loops=9)

  • Join Filter: (rg.geofence_id = gvc.geofence_id)
  • Rows Removed by Join Filter: 7
44. 0.108 0.432 ↑ 1.0 1 9

Nested Loop (cost=0.42..21.21 rows=1 width=72) (actual time=0.028..0.048 rows=1 loops=9)

45. 0.072 0.072 ↓ 14.0 14 9

Seq Scan on geofences geo (cost=0.00..12.75 rows=1 width=68) (actual time=0.003..0.008 rows=14 loops=9)

  • Filter: (company_id = bsu.company_id)
  • Rows Removed by Filter: 10
46. 0.252 0.252 ↓ 0.0 0 126

Index Scan using idx_records_geofence__record_id on records_geofence rg (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=126)

  • Index Cond: ((record_id = rec.record_id) AND (geofence_id = geo.geofence_id))
  • Filter: ((data_rge ->> 'time_in_sum'::text) IS NOT NULL)
  • Rows Removed by Filter: 1
47. 0.098 0.098 ↓ 1.7 10 7

Seq Scan on geofences_vehicles_companies gvc (cost=0.00..2.88 rows=6 width=4) (actual time=0.005..0.014 rows=10 loops=7)

  • Filter: ((to_gvc IS NULL) AND (vehicle_company_id = vc.vehicle_company_id))
  • Rows Removed by Filter: 89