explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sg4g

Settings
# exclusive inclusive rows x rows loops node
1. 0.133 8.293 ↑ 1.0 34 1

Hash Left Join (cost=265.22..570.25 rows=34 width=2,855) (actual time=7.486..8.293 rows=34 loops=1)

  • Hash Cond: ((SubPlan 1) = itc.traffic_current_id)
2. 0.037 7.027 ↑ 1.0 34 1

Hash Left Join (cost=192.39..355.72 rows=34 width=2,827) (actual time=6.695..7.027 rows=34 loops=1)

  • Hash Cond: (v.vessel_id = itc_1.vessel_id)
3. 0.015 2.371 ↑ 1.0 34 1

Nested Loop Left Join (cost=93.41..256.61 rows=34 width=2,795) (actual time=2.059..2.371 rows=34 loops=1)

4. 0.033 2.323 ↓ 1.9 33 1

Nested Loop Left Join (cost=93.01..212.31 rows=17 width=2,677) (actual time=2.058..2.323 rows=33 loops=1)

5. 0.029 2.290 ↓ 1.9 33 1

Nested Loop Left Join (cost=92.86..182.76 rows=17 width=2,669) (actual time=2.056..2.290 rows=33 loops=1)

6. 0.044 2.228 ↓ 1.9 33 1

Nested Loop Left Join (cost=92.71..153.21 rows=17 width=2,661) (actual time=2.055..2.228 rows=33 loops=1)

7. 0.043 2.184 ↓ 1.9 33 1

Hash Left Join (cost=92.44..147.31 rows=17 width=2,658) (actual time=2.053..2.184 rows=33 loops=1)

  • Hash Cond: (a.asset_id = ass.asset_id)
  • Join Filter: (a.vessel_id IS NULL)
  • Rows Removed by Join Filter: 2
8. 0.017 2.098 ↑ 1.0 17 1

Nested Loop Left Join (cost=89.72..144.19 rows=17 width=2,542) (actual time=1.992..2.098 rows=17 loops=1)

9. 0.015 2.064 ↑ 1.0 17 1

Nested Loop Left Join (cost=89.44..138.58 rows=17 width=2,542) (actual time=1.986..2.064 rows=17 loops=1)

10. 0.044 1.947 ↑ 1.0 17 1

Hash Join (cost=89.17..101.40 rows=17 width=2,542) (actual time=1.927..1.947 rows=17 loops=1)

  • Hash Cond: (at.asset_type_id = a.asset_type_id)
11. 0.012 0.012 ↑ 30.0 5 1

Seq Scan on rm_asset_type at (cost=0.00..11.50 rows=150 width=122) (actual time=0.008..0.012 rows=5 loops=1)

12. 0.015 1.891 ↑ 1.0 17 1

Hash (cost=88.95..88.95 rows=17 width=2,428) (actual time=1.891..1.891 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
13. 0.016 1.876 ↑ 1.0 17 1

Merge Right Join (cost=87.38..88.95 rows=17 width=2,428) (actual time=1.864..1.876 rows=17 loops=1)

  • Merge Cond: (vtp.vessel_type_id = vt.parent_vessel_type_id)
14. 0.004 0.004 ↑ 87.0 1 1

Index Scan using pk_vdb_vessel_type on vdb_vessel_type vtp (cost=0.14..14.45 rows=87 width=28) (actual time=0.004..0.004 rows=1 loops=1)

15. 0.043 1.856 ↑ 1.0 17 1

Sort (cost=87.07..87.12 rows=17 width=2,408) (actual time=1.854..1.856 rows=17 loops=1)

  • Sort Key: vt.parent_vessel_type_id
  • Sort Method: quicksort Memory: 29kB
16. 0.022 1.813 ↑ 1.0 17 1

Nested Loop Left Join (cost=1.52..86.73 rows=17 width=2,408) (actual time=1.172..1.813 rows=17 loops=1)

17. 0.144 1.791 ↑ 1.0 17 1

Hash Right Join (cost=1.38..83.67 rows=17 width=2,372) (actual time=1.163..1.791 rows=17 loops=1)

  • Hash Cond: (v.asset_id = a.asset_id)
18. 1.374 1.374 ↑ 1.0 1,616 1

Seq Scan on vdb_vessel v (cost=0.00..76.20 rows=1,620 width=88) (actual time=0.014..1.374 rows=1,616 loops=1)

19. 0.252 0.273 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=2,288) (actual time=0.273..0.273 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
20. 0.021 0.021 ↑ 1.0 17 1

Seq Scan on rm_asset a (cost=0.00..1.17 rows=17 width=2,288) (actual time=0.007..0.021 rows=17 loops=1)

21. 0.000 0.000 ↓ 0.0 0 17

Index Scan using pk_vdb_vessel_type on vdb_vessel_type vt (cost=0.14..0.17 rows=1 width=44) (actual time=0.000..0.000 rows=0 loops=17)

  • Index Cond: (v.vessel_type_id = vessel_type_id)
22. 0.102 0.102 ↑ 1.0 1 17

Index Only Scan using pk_cd_contact_card on cd_contact_card cc (cost=0.28..2.18 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=17)

  • Index Cond: (contact_card_id = a.contact_card_id)
  • Heap Fetches: 16
23. 0.017 0.017 ↓ 0.0 0 17

Index Only Scan using pk_cd_contact_card on cd_contact_card vcc (cost=0.28..0.32 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=17)

  • Index Cond: (contact_card_id = v.contact_card_id)
  • Heap Fetches: 1
24. 0.020 0.043 ↓ 1.1 34 1

Hash (cost=2.32..2.32 rows=32 width=124) (actual time=0.043..0.043 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
25. 0.023 0.023 ↓ 1.1 34 1

Seq Scan on rm_asset_search ass (cost=0.00..2.32 rows=32 width=124) (actual time=0.010..0.023 rows=34 loops=1)

26. 0.000 0.000 ↓ 0.0 0 33

Index Scan using pk_ovs_country on ovs_country c (cost=0.27..0.34 rows=1 width=7) (actual time=0.000..0.000 rows=0 loops=33)

  • Index Cond: (v.country_id = country_id)
27. 0.033 0.033 ↓ 0.0 0 33

Index Scan using pk_ovs_file on ovs_file fvt (cost=0.15..1.73 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=33)

  • Index Cond: (vt.image_id = file_id)
28. 0.000 0.000 ↓ 0.0 0 33

Index Scan using pk_ovs_file on ovs_file fvtp (cost=0.15..1.73 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=33)

  • Index Cond: (vtp.image_id = file_id)
29. 0.033 0.033 ↓ 0.0 0 33

Index Scan using pk_vdb_vessel_search on vdb_vessel_search vs (cost=0.41..2.59 rows=2 width=122) (actual time=0.001..0.001 rows=0 loops=33)

  • Index Cond: (vessel_id = v.vessel_id)
30. 0.345 4.619 ↓ 8.0 595 1

Hash (cost=98.05..98.05 rows=74 width=36) (actual time=4.619..4.619 rows=595 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
31. 0.438 4.274 ↓ 8.0 595 1

Nested Loop (cost=68.92..98.05 rows=74 width=36) (actual time=1.210..4.274 rows=595 loops=1)

32. 0.115 1.452 ↓ 596.0 596 1

Nested Loop (cost=68.65..96.96 rows=1 width=20) (actual time=1.143..1.452 rows=596 loops=1)

33. 0.068 0.068 ↑ 1.0 1 1

Seq Scan on ovs_configuration cfg (cost=0.00..3.20 rows=1 width=0) (actual time=0.037..0.068 rows=1 loops=1)

  • Filter: ((property)::text = 'imtTrafficMinutes'::text)
  • Rows Removed by Filter: 108
34. 0.858 1.269 ↑ 1.4 596 1

HashAggregate (cost=68.65..77.02 rows=837 width=20) (actual time=1.104..1.269 rows=596 loops=1)

35. 0.411 0.411 ↑ 1.0 837 1

Seq Scan on imt_traffic_current itci (cost=0.00..62.37 rows=837 width=20) (actual time=0.007..0.411 rows=837 loops=1)

36. 2.384 2.384 ↑ 1.0 1 596

Index Scan using uk_imttc_01 on imt_traffic_current itc_1 (cost=0.28..1.08 rows=1 width=52) (actual time=0.003..0.004 rows=1 loops=596)

  • Index Cond: (vessel_id = itci.vessel_id)
  • Filter: (((max(itci.timestamp_position_raw)) = timestamp_position_raw) AND ((min(itci.change_datetime)) = change_datetime))
37. 0.301 0.671 ↑ 1.0 837 1

Hash (cost=62.37..62.37 rows=837 width=36) (actual time=0.671..0.671 rows=837 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
38. 0.370 0.370 ↑ 1.0 837 1

Seq Scan on imt_traffic_current itc (cost=0.00..62.37 rows=837 width=36) (actual time=0.003..0.370 rows=837 loops=1)

39.          

SubPlan (for Hash Left Join)

40. 0.000 0.462 ↑ 1.0 1 66

Limit (cost=8.30..8.31 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=66)

41. 0.264 0.462 ↑ 1.0 1 66

Sort (cost=8.30..8.31 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=66)

  • Sort Key: itch.timestamp_position_raw
  • Sort Method: quicksort Memory: 25kB
42. 0.198 0.198 ↑ 1.0 1 66

Index Scan using idx_imttc_09 on imt_traffic_current itch (cost=0.28..8.29 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=66)

  • Index Cond: (traffic_entity_id = a.traffic_entity_id)
Total runtime : 9.362 ms