explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F9sF

Settings
# exclusive inclusive rows x rows loops node
1. 4.627 8,333.375 ↑ 6,444.4 9 1

Result (cost=85,910.93..601,165,451.38 rows=58,000 width=870) (actual time=1,022.487..8,333.375 rows=9 loops=1)

2. 1.192 116.311 ↑ 6,444.4 9 1

ProjectSet (cost=85,910.93..86,221.38 rows=58,000 width=1,186) (actual time=115.251..116.311 rows=9 loops=1)

3. 0.020 115.119 ↑ 6.4 9 1

Result (cost=85,910.93..85,914.12 rows=58 width=1,158) (actual time=115.095..115.119 rows=9 loops=1)

4. 0.048 115.099 ↑ 6.4 9 1

Sort (cost=85,910.93..85,911.08 rows=58 width=1,158) (actual time=115.092..115.099 rows=9 loops=1)

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

Hash Join (cost=2,636.90..85,909.23 rows=58 width=1,158) (actual time=93.782..115.051 rows=9 loops=1)

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

Hash Left Join (cost=2,632.09..85,893.90 rows=2,132 width=1,069) (actual time=93.558..114.758 rows=9 loops=1)

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

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

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

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

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

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

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

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

11. 0.614 2.432 ↓ 1.0 1,571 1

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

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

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

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

Seq Scan on devices d (cost=0.00..348.64 rows=1,570 width=33) (actual time=0.007..1.084 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. 4.713 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. 7.855 7.855 ↑ 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.005 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.002 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.010 0.010 ↓ 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.010 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.019 ↑ 1.0 29 1

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

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

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

28. 1.834 67.290 ↓ 9.0 8,350 1

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

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

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

30. 12.403 24.120 ↓ 1.0 50,869 1

Hash (cost=1,784.08..1,784.08 rows=50,808 width=84) (actual time=24.120..24.120 rows=50,869 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 6,393kB
31. 11.717 11.717 ↓ 1.0 50,869 1

Seq Scan on positions (cost=0.00..1,784.08 rows=50,808 width=84) (actual time=0.011..11.717 rows=50,869 loops=1)

32. 0.007 0.198 ↓ 10.0 10 1

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

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

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

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

Nested Loop (cost=1.04..3.72 rows=1 width=64) (actual time=0.108..0.138 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.046 0.108 ↓ 18.0 36 4

Hash Join (cost=1.04..2.67 rows=2 width=20) (actual time=0.010..0.027 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.003..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.002..0.002 rows=2 loops=10)

41.          

SubPlan (for Result)

42. 0.135 8,212.437 ↑ 1.0 1 9

Aggregate (cost=10,361.84..10,361.85 rows=1 width=32) (actual time=912.493..912.493 rows=1 loops=9)

43. 0.078 8,212.302 ↑ 1.0 1 9

Nested Loop (cost=0.00..10,361.84 rows=1 width=64) (actual time=566.221..912.478 rows=1 loops=9)

  • Join Filter: (rg.geofence_id = gvc.geofence_id)
  • Rows Removed by Join Filter: 6
44. 0.765 8,212.077 ↑ 1.0 1 9

Nested Loop (cost=0.00..10,358.89 rows=1 width=72) (actual time=443.839..912.453 rows=1 loops=9)

  • Join Filter: (rg.geofence_id = geo.geofence_id)
  • Rows Removed by Join Filter: 12
45. 0.252 0.252 ↓ 13.0 13 9

Seq Scan on geofences geo (cost=0.00..12.75 rows=1 width=68) (actual time=0.004..0.028 rows=13 loops=9)

  • Filter: (company_id = bsu.company_id)
  • Rows Removed by Filter: 10
46. 8,211.060 8,211.060 ↑ 7.0 1 117

Seq Scan on records_geofence rg (cost=0.00..10,346.05 rows=7 width=4) (actual time=68.130..70.180 rows=1 loops=117)

  • Filter: (((data_rge ->> 'time_in_sum'::text) IS NOT NULL) AND (record_id = rec.record_id))
  • Rows Removed by Filter: 339,272
47. 0.147 0.147 ↓ 1.3 8 7

Seq Scan on geofences_vehicles_companies gvc (cost=0.00..2.88 rows=6 width=4) (actual time=0.012..0.021 rows=8 loops=7)

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