explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UfTm

Settings
# exclusive inclusive rows x rows loops node
1. 0.726 22.463 ↑ 1.1 48 1

Nested Loop Left Join (cost=2,118.58..5,330.17 rows=54 width=726) (actual time=15.126..22.463 rows=48 loops=1)

2. 0.070 21.593 ↑ 1.1 48 1

Hash Left Join (cost=2,109.86..4,425.94 rows=54 width=698) (actual time=15.099..21.593 rows=48 loops=1)

  • Hash Cond: (v.vessel_id = itc_1.vessel_id)
3. 0.301 6.754 ↑ 1.1 48 1

Nested Loop Left Join (cost=36.20..2,352.03 rows=54 width=666) (actual time=0.318..6.754 rows=48 loops=1)

  • Join Filter: ((a.vessel_id IS NULL) AND (a.asset_id = ass.asset_id))
  • Rows Removed by Join Filter: 1440
4. 0.038 6.229 ↑ 1.2 32 1

Nested Loop Left Join (cost=36.20..2,331.39 rows=38 width=550) (actual time=0.267..6.229 rows=32 loops=1)

5. 0.038 5.999 ↓ 1.3 24 1

Nested Loop Left Join (cost=35.79..2,188.37 rows=19 width=426) (actual time=0.259..5.999 rows=24 loops=1)

6. 0.074 5.961 ↓ 1.3 24 1

Nested Loop Left Join (cost=35.64..2,155.34 rows=19 width=419) (actual time=0.258..5.961 rows=24 loops=1)

7. 0.041 5.863 ↓ 1.3 24 1

Nested Loop Left Join (cost=35.50..2,122.31 rows=19 width=412) (actual time=0.249..5.863 rows=24 loops=1)

8. 0.038 5.798 ↓ 1.3 24 1

Nested Loop Left Join (cost=35.23..2,115.83 rows=19 width=409) (actual time=0.244..5.798 rows=24 loops=1)

9. 0.029 5.760 ↓ 1.3 24 1

Nested Loop Left Join (cost=35.08..2,109.11 rows=19 width=389) (actual time=0.242..5.760 rows=24 loops=1)

10. 0.048 5.683 ↓ 1.3 24 1

Nested Loop Left Join (cost=34.94..2,105.71 rows=19 width=353) (actual time=0.237..5.683 rows=24 loops=1)

11. 0.046 5.443 ↓ 1.3 24 1

Hash Join (cost=34.66..2,093.17 rows=19 width=353) (actual time=0.194..5.443 rows=24 loops=1)

  • Hash Cond: (a.asset_type_id = at.asset_type_id)
12. 0.258 5.383 ↓ 1.3 24 1

Hash Right Join (cost=21.29..2,079.53 rows=19 width=239) (actual time=0.168..5.383 rows=24 loops=1)

  • Hash Cond: (v.asset_id = a.asset_id)
13. 5.007 5.007 ↑ 1.0 1,835 1

Seq Scan on vdb_vessel v (cost=0.00..2,051.35 rows=1,835 width=89) (actual time=0.013..5.007 rows=1,835 loops=1)

14. 0.017 0.118 ↓ 1.3 24 1

Hash (cost=21.05..21.05 rows=19 width=154) (actual time=0.118..0.118 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
15. 0.031 0.101 ↓ 1.3 24 1

Merge Left Join (cost=15.19..21.05 rows=19 width=154) (actual time=0.072..0.101 rows=24 loops=1)

  • Merge Cond: (a.contact_card_id = cc.contact_card_id)
16. 0.032 0.047 ↓ 1.3 24 1

Sort (cost=1.59..1.64 rows=19 width=154) (actual time=0.045..0.047 rows=24 loops=1)

  • Sort Key: a.contact_card_id
  • Sort Method: quicksort Memory: 30kB
17. 0.015 0.015 ↓ 1.3 24 1

Seq Scan on rm_asset a (cost=0.00..1.19 rows=19 width=154) (actual time=0.005..0.015 rows=24 loops=1)

18. 0.023 0.023 ↑ 35.0 55 1

Index Only Scan using pk_cd_contact_card on cd_contact_card cc (cost=0.28..670.12 rows=1,925 width=4) (actual time=0.013..0.023 rows=55 loops=1)

  • Heap Fetches: 25
19. 0.007 0.014 ↑ 30.0 5 1

Hash (cost=11.50..11.50 rows=150 width=122) (actual time=0.014..0.014 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
20. 0.007 0.007 ↑ 30.0 5 1

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

21. 0.192 0.192 ↓ 0.0 0 24

Index Only Scan using pk_cd_contact_card on cd_contact_card vcc (cost=0.28..0.65 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=24)

  • Index Cond: (contact_card_id = v.contact_card_id)
  • Heap Fetches: 6
22. 0.048 0.048 ↓ 0.0 0 24

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

  • Index Cond: (v.vessel_type_id = vessel_type_id)
23. 0.000 0.000 ↓ 0.0 0 24

Index Scan using pk_vdb_vessel_type on vdb_vessel_type vtp (cost=0.14..0.34 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=24)

  • Index Cond: (vt.parent_vessel_type_id = vessel_type_id)
24. 0.024 0.024 ↓ 0.0 0 24

Index Scan using pk_ovs_country on ovs_country c (cost=0.27..0.33 rows=1 width=7) (actual time=0.001..0.001 rows=0 loops=24)

  • Index Cond: (v.country_id = country_id)
25. 0.024 0.024 ↓ 0.0 0 24

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

  • Index Cond: (vt.image_id = file_id)
26. 0.000 0.000 ↓ 0.0 0 24

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

  • Index Cond: (vtp.image_id = file_id)
27. 0.192 0.192 ↑ 2.0 1 24

Index Scan using pk_vdb_vessel_search on vdb_vessel_search vs (cost=0.41..7.51 rows=2 width=128) (actual time=0.003..0.008 rows=1 loops=24)

  • Index Cond: (vessel_id = v.vessel_id)
28. 0.207 0.224 ↓ 1.4 46 32

Materialize (cost=0.00..2.48 rows=32 width=124) (actual time=0.001..0.007 rows=46 loops=32)

29. 0.017 0.017 ↓ 1.4 46 1

Seq Scan on rm_asset_search ass (cost=0.00..2.32 rows=32 width=124) (actual time=0.005..0.017 rows=46 loops=1)

30. 0.957 14.769 ↓ 8.0 1,724 1

Hash (cost=2,070.98..2,070.98 rows=215 width=36) (actual time=14.769..14.769 rows=1,724 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 115kB
31. 1.114 13.812 ↓ 8.0 1,724 1

Nested Loop (cost=1,999.29..2,070.98 rows=215 width=36) (actual time=4.868..13.812 rows=1,724 loops=1)

32. 0.334 5.798 ↓ 1,725.0 1,725 1

Nested Loop (cost=1,999.01..2,067.53 rows=1 width=20) (actual time=4.847..5.798 rows=1,725 loops=1)

33. 0.041 0.041 ↑ 1.0 1 1

Seq Scan on ovs_configuration cfg (cost=0.00..3.36 rows=1 width=0) (actual time=0.010..0.041 rows=1 loops=1)

  • Filter: ((property)::text = 'imtTrafficMinutes'::text)
  • Rows Removed by Filter: 108
34. 2.468 5.423 ↑ 1.3 1,725 1

HashAggregate (cost=1,999.01..2,020.73 rows=2,172 width=20) (actual time=4.836..5.423 rows=1,725 loops=1)

35. 2.955 2.955 ↑ 1.0 2,172 1

Seq Scan on imt_traffic_current itci (cost=0.00..1,982.72 rows=2,172 width=20) (actual time=0.003..2.955 rows=2,172 loops=1)

36. 6.900 6.900 ↑ 1.0 1 1,725

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

  • 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.000 0.144 ↑ 1.0 1 48

Index Scan using pk_imt_traffic_current on imt_traffic_current itc (cost=8.72..16.73 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=48)

  • Index Cond: (traffic_current_id = (SubPlan 1))
38.          

SubPlan (for Index Scan)

39. 0.048 0.528 ↑ 1.0 1 48

Limit (cost=8.43..8.44 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=48)

40. 0.240 0.480 ↑ 1.0 1 48

Sort (cost=8.43..8.44 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=48)

  • Sort Key: itch.timestamp_position_raw
  • Sort Method: quicksort Memory: 25kB
41. 0.240 0.240 ↑ 1.0 1 48

Index Scan using idx_imttc_09 on imt_traffic_current itch (cost=0.41..8.42 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=48)

  • Index Cond: (traffic_entity_id = a.traffic_entity_id)
42. 0.048 0.528 ↑ 1.0 1 48

Limit (cost=8.43..8.44 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=48)

43. 0.240 0.480 ↑ 1.0 1 48

Sort (cost=8.43..8.44 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=48)

  • Sort Key: itch.timestamp_position_raw
  • Sort Method: quicksort Memory: 25kB
44. 0.240 0.240 ↑ 1.0 1 48

Index Scan using idx_imttc_09 on imt_traffic_current itch (cost=0.41..8.42 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=48)

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