explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1sYx

Settings
# exclusive inclusive rows x rows loops node
1. 0.396 4.790 ↑ 1.5 32 1

Hash Left Join (cost=141.54..199.80 rows=48 width=2,985) (actual time=3.777..4.790 rows=32 loops=1)

  • Hash Cond: ((SubPlan 1) = itc.traffic_current_id)
2. 0.047 3.709 ↑ 1.5 32 1

Nested Loop Left Join (cost=129.97..164.79 rows=48 width=3,561) (actual time=3.558..3.709 rows=32 loops=1)

  • Join Filter: (v.vessel_id = itc_1.vessel_id)
3. 0.105 3.566 ↑ 1.5 32 1

Hash Right Join (cost=116.12..135.60 rows=48 width=3,529) (actual time=3.477..3.566 rows=32 loops=1)

  • Hash Cond: (vs.vessel_id = v.vessel_id)
4. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on vdb_vessel_search vs (cost=0.00..16.00 rows=600 width=74) (actual time=0.006..0.006 rows=0 loops=1)

5. 0.074 3.455 ↓ 2.0 32 1

Hash (cost=115.92..115.92 rows=16 width=3,459) (actual time=3.455..3.455 rows=32 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
6. 0.043 3.381 ↓ 2.0 32 1

Nested Loop Left Join (cost=41.57..115.92 rows=16 width=3,459) (actual time=3.218..3.381 rows=32 loops=1)

7. 0.074 3.306 ↓ 2.0 32 1

Nested Loop Left Join (cost=41.42..88.26 rows=16 width=3,452) (actual time=3.185..3.306 rows=32 loops=1)

8. 0.065 3.168 ↓ 2.0 32 1

Hash Left Join (cost=41.27..60.60 rows=16 width=3,445) (actual time=3.094..3.168 rows=32 loops=1)

  • Hash Cond: (a.asset_id = ass.asset_id)
  • Join Filter: (a.vessel_id IS NULL)
9. 0.094 1.427 ↑ 1.0 16 1

Hash Right Join (cost=38.55..57.48 rows=16 width=3,329) (actual time=1.394..1.427 rows=16 loops=1)

  • Hash Cond: (c.country_id = v.country_id)
10. 0.120 0.120 ↑ 1.0 251 1

Seq Scan on ovs_country c (cost=0.00..17.51 rows=251 width=7) (actual time=0.015..0.120 rows=251 loops=1)

11. 0.015 1.213 ↑ 1.0 16 1

Hash (cost=38.35..38.35 rows=16 width=3,326) (actual time=1.213..1.213 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 0.030 1.198 ↑ 1.0 16 1

Hash Join (cost=26.13..38.35 rows=16 width=3,326) (actual time=1.184..1.198 rows=16 loops=1)

  • Hash Cond: (at.asset_type_id = a.asset_type_id)
13. 0.397 0.397 ↑ 30.0 5 1

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

14. 0.011 0.771 ↑ 1.0 16 1

Hash (cost=25.93..25.93 rows=16 width=3,212) (actual time=0.770..0.771 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
15. 0.046 0.760 ↑ 1.0 16 1

Hash Right Join (cost=21.53..25.93 rows=16 width=3,212) (actual time=0.404..0.760 rows=16 loops=1)

  • Hash Cond: (cc.contact_card_id = a.contact_card_id)
16. 0.599 0.599 ↑ 1.0 90 1

Seq Scan on cd_contact_card cc (cost=0.00..3.90 rows=90 width=4) (actual time=0.280..0.599 rows=90 loops=1)

17. 0.015 0.115 ↑ 1.0 16 1

Hash (cost=21.33..21.33 rows=16 width=3,212) (actual time=0.109..0.115 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
18. 0.026 0.100 ↑ 1.0 16 1

Hash Right Join (cost=16.96..21.33 rows=16 width=3,212) (actual time=0.085..0.100 rows=16 loops=1)

  • Hash Cond: (v.asset_id = a.asset_id)
19. 0.010 0.038 ↓ 0.0 0 1

Hash Right Join (cost=15.60..19.93 rows=10 width=928) (actual time=0.037..0.038 rows=0 loops=1)

  • Hash Cond: (vcc.contact_card_id = v.contact_card_id)
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on cd_contact_card vcc (cost=0.00..3.90 rows=90 width=4) (never executed)

21. 0.000 0.028 ↓ 0.0 0 1

Hash (cost=15.47..15.47 rows=10 width=928) (actual time=0.027..0.028 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
22. 0.001 0.028 ↓ 0.0 0 1

Merge Right Join (cost=13.83..15.47 rows=10 width=928) (actual time=0.027..0.028 rows=0 loops=1)

  • Merge Cond: (vtp.vessel_type_id = vt.parent_vessel_type_id)
23. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

24. 0.007 0.019 ↓ 0.0 0 1

Sort (cost=13.69..13.71 rows=10 width=908) (actual time=0.018..0.019 rows=0 loops=1)

  • Sort Key: vt.parent_vessel_type_id
  • Sort Method: quicksort Memory: 25kB
25. 0.009 0.012 ↓ 0.0 0 1

Hash Right Join (cost=10.22..13.52 rows=10 width=908) (actual time=0.012..0.012 rows=0 loops=1)

  • Hash Cond: (vt.vessel_type_id = v.vessel_type_id)
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on vdb_vessel_type vt (cost=0.00..2.87 rows=87 width=44) (never executed)

27. 0.001 0.003 ↓ 0.0 0 1

Hash (cost=10.10..10.10 rows=10 width=872) (actual time=0.002..0.003 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
28. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on vdb_vessel v (cost=0.00..10.10 rows=10 width=872) (actual time=0.002..0.002 rows=0 loops=1)

29. 0.016 0.036 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=2,288) (actual time=0.035..0.036 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
30. 0.020 0.020 ↑ 1.0 16 1

Seq Scan on rm_asset a (cost=0.00..1.16 rows=16 width=2,288) (actual time=0.013..0.020 rows=16 loops=1)

31. 0.091 1.676 ↑ 1.0 32 1

Hash (cost=2.32..2.32 rows=32 width=124) (actual time=1.675..1.676 rows=32 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
32. 1.585 1.585 ↑ 1.0 32 1

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

33. 0.064 0.064 ↓ 0.0 0 32

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

  • Index Cond: (file_id = vt.image_id)
34. 0.032 0.032 ↓ 0.0 0 32

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

  • Index Cond: (file_id = vtp.image_id)
35. 0.022 0.096 ↓ 0.0 0 32

Materialize (cost=13.85..28.48 rows=1 width=36) (actual time=0.003..0.003 rows=0 loops=32)

36. 0.000 0.074 ↓ 0.0 0 1

Nested Loop (cost=13.85..28.47 rows=1 width=36) (actual time=0.074..0.074 rows=0 loops=1)

37. 0.022 0.074 ↓ 0.0 0 1

Hash Join (cost=13.85..25.10 rows=1 width=36) (actual time=0.073..0.074 rows=0 loops=1)

  • Hash Cond: ((itc_1.vessel_id = itci.vessel_id) AND (itc_1.timestamp_position_raw = (max(itci.timestamp_position_raw))) AND (itc_1.change_datetime = (min(itci.change_datetime))))
38. 0.015 0.015 ↑ 70.0 1 1

Seq Scan on imt_traffic_current itc_1 (cost=0.00..10.70 rows=70 width=52) (actual time=0.015..0.015 rows=1 loops=1)

39. 0.000 0.037 ↓ 0.0 0 1

Hash (cost=12.62..12.62 rows=70 width=20) (actual time=0.037..0.037 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
40. 0.000 0.037 ↑ 70.0 1 1

HashAggregate (cost=11.22..11.92 rows=70 width=20) (actual time=0.035..0.037 rows=1 loops=1)

  • Group Key: itci.vessel_id
41. 0.144 0.144 ↑ 3.3 21 1

Seq Scan on imt_traffic_current itci (cost=0.00..10.70 rows=70 width=20) (actual time=0.008..0.144 rows=21 loops=1)

42. 0.000 0.000 ↓ 0.0 0

Seq Scan on ovs_configuration cfg (cost=0.00..3.36 rows=1 width=0) (never executed)

  • Filter: ((property)::text = 'imtTrafficMinutes'::text)
43. 0.030 0.045 ↑ 3.3 21 1

Hash (cost=10.70..10.70 rows=70 width=36) (actual time=0.044..0.045 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
44. 0.015 0.015 ↑ 3.3 21 1

Seq Scan on imt_traffic_current itc (cost=0.00..10.70 rows=70 width=36) (actual time=0.007..0.015 rows=21 loops=1)

45.          

SubPlan (for Hash Left Join)

46. 0.064 0.640 ↑ 1.0 1 64

Limit (cost=8.17..8.18 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=64)

47. 0.320 0.576 ↑ 1.0 1 64

Sort (cost=8.17..8.18 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=64)

  • Sort Key: itch.timestamp_position_raw DESC
  • Sort Method: quicksort Memory: 25kB
48. 0.256 0.256 ↑ 1.0 1 64

Index Scan using idx_imttc_09 on imt_traffic_current itch (cost=0.14..8.16 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=64)

  • Index Cond: (traffic_entity_id = a.traffic_entity_id)
Planning time : 31.439 ms
Execution time : 5.584 ms