explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ukE

Settings
# exclusive inclusive rows x rows loops node
1. 5.339 1,539.595 ↓ 181.2 2,356 1

Unique (cost=5,195.16..5,195.49 rows=13 width=32) (actual time=1,520.364..1,539.595 rows=2,356 loops=1)

2. 5.244 1,534.256 ↓ 251.7 3,272 1

Subquery Scan nfa_k20_network (cost=5,195.16..5,195.46 rows=13 width=32) (actual time=1,520.362..1,534.256 rows=3,272 loops=1)

3. 6.155 1,529.012 ↓ 251.7 3,272 1

Unique (cost=5,195.16..5,195.33 rows=13 width=53) (actual time=1,520.358..1,529.012 rows=3,272 loops=1)

4. 13.407 1,522.857 ↓ 254.1 3,303 1

Sort (cost=5,195.16..5,195.20 rows=13 width=53) (actual time=1,520.355..1,522.857 rows=3,303 loops=1)

  • Sort Key: nfa_network.network, public.info_site.siteid, (substr(translate(((public.info_site.shipaddr)::text || ';'::text), ','::text, ';'::text), 0, strpos(translate(((public.info_site.shipaddr)::text || ';'::text), ','::text, ';'::text), ';'::text))), ('poller'::text)
  • Sort Method: quicksort Memory: 335kB
5. 5.547 1,509.450 ↓ 254.1 3,303 1

Append (cost=43.82..5,194.92 rows=13 width=53) (actual time=1.887..1,509.450 rows=3,303 loops=1)

6. 24.558 30.442 ↓ 204.8 2,253 1

Hash Join (cost=43.82..162.84 rows=11 width=52) (actual time=1.884..30.442 rows=2,253 loops=1)

  • Hash Cond: (nfa_network.info_site_id = public.info_site.id)
7. 4.104 4.104 ↓ 98.0 2,253 1

Seq Scan on nfa_network (cost=0.00..118.65 rows=23 width=11) (actual time=0.046..4.104 rows=2,253 loops=1)

  • Filter: (family((network)::inet) = 4)
8. 0.658 1.780 ↑ 1.0 449 1

Hash (cost=38.21..38.21 rows=449 width=49) (actual time=1.780..1.780 rows=449 loops=1)

9. 1.122 1.122 ↑ 1.0 449 1

Seq Scan on info_site (cost=0.00..38.21 rows=449 width=49) (actual time=0.034..1.122 rows=449 loops=1)

  • Filter: ((owner)::text = 'k20'::text)
10. 14.305 1,472.494 ↓ 1,017.0 1,017 1

Nested Loop (cost=3,185.37..5,022.16 rows=1 width=53) (actual time=953.470..1,472.494 rows=1,017 loops=1)

11. 4.115 1,451.070 ↓ 1,017.0 1,017 1

Nested Loop (cost=3,185.37..5,013.86 rows=1 width=77) (actual time=953.363..1,451.070 rows=1,017 loops=1)

12. 4.213 1,441.870 ↓ 1,017.0 1,017 1

Nested Loop (cost=3,185.37..5,013.58 rows=1 width=81) (actual time=953.348..1,441.870 rows=1,017 loops=1)

13. 17.613 1,426.470 ↓ 1,017.0 1,017 1

Nested Loop (cost=3,185.37..5,005.28 rows=1 width=73) (actual time=953.314..1,426.470 rows=1,017 loops=1)

14. 30.621 1,380.973 ↓ 6,971.0 6,971 1

Nested Loop (cost=3,185.37..5,005.00 rows=1 width=77) (actual time=953.243..1,380.973 rows=6,971 loops=1)

15. 29.501 1,308.526 ↓ 6,971.0 6,971 1

Nested Loop (cost=3,185.37..4,996.71 rows=1 width=69) (actual time=953.158..1,308.526 rows=6,971 loops=1)

16. 35.599 1,227.372 ↓ 7,379.0 7,379 1

Hash Join (cost=3,185.37..4,996.42 rows=1 width=28) (actual time=953.056..1,227.372 rows=7,379 loops=1)

  • Hash Cond: (public.dev_state.device_id = interface.device_id)
17. 37.533 360.700 ↑ 1.0 16,054 1

Hash Left Join (cost=2,174.32..3,925.14 rows=16,058 width=12) (actual time=121.850..360.700 rows=16,054 loops=1)

  • Hash Cond: (public.dev_state.device_id = dev_code.device_id)
18. 40.187 307.048 ↑ 1.0 16,054 1

Hash Left Join (cost=1,941.08..3,432.46 rows=16,058 width=12) (actual time=105.707..307.048 rows=16,054 loops=1)

  • Hash Cond: (public.dev_state.device_id = dev_snmp.device_id)
19. 41.366 239.588 ↑ 1.0 16,054 1

Hash Left Join (cost=1,457.21..2,625.69 rows=16,058 width=12) (actual time=78.392..239.588 rows=16,054 loops=1)

  • Hash Cond: (public.dev_state.device_id = dev_monitor.device_id)
20. 27.684 164.930 ↑ 1.0 16,054 1

Hash Left Join (cost=967.87..1,813.95 rows=16,058 width=12) (actual time=45.067..164.930 rows=16,054 loops=1)

  • Hash Cond: (public.dev_state.device_id = dev_exception.device_id)
21. 31.287 137.109 ↑ 1.0 16,054 1

Hash Join (cost=965.66..1,751.12 rows=16,058 width=12) (actual time=44.893..137.109 rows=16,054 loops=1)

  • Hash Cond: (public.dev_state.system_id = public.system_id_types.id)
22. 44.720 104.156 ↑ 1.0 16,054 1

Hash Join (cost=939.10..1,503.76 rows=16,058 width=16) (actual time=43.189..104.156 rows=16,054 loops=1)

  • Hash Cond: (dev_location.device_id = public.dev_state.device_id)
23. 16.320 16.320 ↑ 1.0 16,054 1

Seq Scan on dev_location (cost=0.00..263.58 rows=16,058 width=8) (actual time=0.032..16.320 rows=16,054 loops=1)

24. 19.250 43.116 ↑ 1.2 18,688 1

Hash (cost=666.82..666.82 rows=21,782 width=8) (actual time=43.116..43.116 rows=18,688 loops=1)

25. 23.866 23.866 ↑ 1.2 18,688 1

Seq Scan on dev_state (cost=0.00..666.82 rows=21,782 width=8) (actual time=0.009..23.866 rows=18,688 loops=1)

26. 0.831 1.666 ↓ 1.0 827 1

Hash (cost=16.25..16.25 rows=825 width=4) (actual time=1.666..1.666 rows=827 loops=1)

27. 0.835 0.835 ↓ 1.0 827 1

Seq Scan on system_id_types (cost=0.00..16.25 rows=825 width=4) (actual time=0.011..0.835 rows=827 loops=1)

28. 0.054 0.137 ↑ 1.0 54 1

Hash (cost=1.54..1.54 rows=54 width=4) (actual time=0.137..0.137 rows=54 loops=1)

29. 0.083 0.083 ↑ 1.0 54 1

Seq Scan on dev_exception (cost=0.00..1.54 rows=54 width=4) (actual time=0.033..0.083 rows=54 loops=1)

30. 16.542 33.292 ↓ 1.0 16,508 1

Hash (cost=283.04..283.04 rows=16,504 width=4) (actual time=33.292..33.292 rows=16,508 loops=1)

31. 16.750 16.750 ↓ 1.0 16,508 1

Seq Scan on dev_monitor (cost=0.00..283.04 rows=16,504 width=4) (actual time=0.027..16.750 rows=16,508 loops=1)

32. 13.179 27.273 ↑ 1.2 13,331 1

Hash (cost=276.72..276.72 rows=16,572 width=4) (actual time=27.273..27.273 rows=13,331 loops=1)

33. 14.094 14.094 ↑ 1.2 13,331 1

Seq Scan on dev_snmp (cost=0.00..276.72 rows=16,572 width=4) (actual time=0.032..14.094 rows=13,331 loops=1)

34. 7.882 16.119 ↓ 1.0 7,968 1

Hash (cost=133.66..133.66 rows=7,966 width=4) (actual time=16.119..16.119 rows=7,968 loops=1)

35. 8.237 8.237 ↓ 1.0 7,968 1

Seq Scan on dev_code (cost=0.00..133.66 rows=7,966 width=4) (actual time=0.031..8.237 rows=7,968 loops=1)

36. 15.898 831.073 ↓ 3,690.0 7,380 1

Hash (cost=1,011.03..1,011.03 rows=2 width=16) (actual time=831.073..831.073 rows=7,380 loops=1)

37. 31.686 815.175 ↓ 3,690.0 7,380 1

Nested Loop (cost=0.00..1,011.03 rows=2 width=16) (actual time=5.482..815.175 rows=7,380 loops=1)

38. 31.436 683.853 ↓ 4,151.5 8,303 1

Nested Loop (cost=0.00..994.39 rows=2 width=36) (actual time=5.385..683.853 rows=8,303 loops=1)

39. 36.078 610.902 ↓ 4,151.5 8,303 1

Nested Loop (cost=0.00..993.82 rows=2 width=40) (actual time=5.320..610.902 rows=8,303 loops=1)

40. 36.768 508.312 ↓ 4,157.0 8,314 1

Nested Loop (cost=0.00..993.20 rows=2 width=32) (actual time=5.259..508.312 rows=8,314 loops=1)

41. 30.905 380.090 ↓ 4,157.0 8,314 1

Nested Loop (cost=0.00..976.57 rows=2 width=28) (actual time=5.159..380.090 rows=8,314 loops=1)

42. 29.779 241.103 ↓ 4,157.0 8,314 1

Nested Loop (cost=0.00..959.93 rows=2 width=24) (actual time=5.064..241.103 rows=8,314 loops=1)

43. 36.007 153.084 ↓ 169.8 8,320 1

Nested Loop (cost=0.00..642.09 rows=49 width=20) (actual time=5.009..153.084 rows=8,320 loops=1)

44. 25.557 25.557 ↓ 169.8 8,320 1

Seq Scan on ifc_ip (cost=0.00..238.69 rows=49 width=12) (actual time=4.890..25.557 rows=8,320 loops=1)

  • Filter: ((ip IS NOT NULL) AND (family((network(ip))::inet) = 4))
45. 91.520 91.520 ↑ 1.0 1 8,320

Index Scan using interface_pkey on interface (cost=0.00..8.22 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=8,320)

  • Index Cond: (interface.id = ifc_ip.ifc_id)
46. 58.240 58.240 ↑ 1.0 1 8,320

Index Scan using ifc_monitor_pkey on ifc_monitor (cost=0.00..6.47 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=8,320)

  • Index Cond: (ifc_monitor.ifc_id = interface.id)
47. 108.082 108.082 ↑ 1.0 1 8,314

Index Scan using ifc_network_information_pkey on ifc_network_information (cost=0.00..8.31 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=8,314)

  • Index Cond: (ifc_network_information.ifc_id = interface.id)
48. 91.454 91.454 ↑ 1.0 1 8,314

Index Scan using ifc_information_pkey on ifc_information (cost=0.00..8.30 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=8,314)

  • Index Cond: (ifc_information.ifc_id = interface.id)
49. 66.512 66.512 ↑ 1.0 1 8,314

Index Scan using dev_state_pkey on dev_state (cost=0.00..0.30 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=8,314)

  • Index Cond: (public.dev_state.device_id = interface.device_id)
50. 41.515 41.515 ↑ 1.0 1 8,303

Index Scan using system_id_types_pkey on system_id_types (cost=0.00..0.27 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=8,303)

  • Index Cond: (public.system_id_types.id = public.dev_state.system_id)
51. 99.636 99.636 ↑ 1.0 1 8,303

Index Scan using ifc_state_pkey on ifc_state (cost=0.00..8.31 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=8,303)

  • Index Cond: (ifc_state.ifc_id = interface.id)
  • Filter: (ifc_state.state = 1)
52. 51.653 51.653 ↑ 1.0 1 7,379

Index Scan using info_site_pkey on info_site (cost=0.00..0.28 rows=1 width=49) (actual time=0.006..0.007 rows=1 loops=7,379)

  • Index Cond: (public.info_site.id = dev_location.site_id)
  • Filter: ((public.info_site.siteid)::text !~~ 'k20%dis'::text)
53. 41.826 41.826 ↑ 1.0 1 6,971

Index Scan using dev_classification_pkey on dev_classification (cost=0.00..8.27 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=6,971)

  • Index Cond: (dev_classification.device_id = interface.device_id)
54. 27.884 27.884 ↓ 0.0 0 6,971

Index Scan using sector_pkey on sector (cost=0.00..0.27 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=6,971)

  • Index Cond: (sector.id = dev_classification.sector)
  • Filter: ((sector.owner)::text = 'k20'::text)
55. 11.187 11.187 ↑ 1.0 1 1,017

Index Scan using dev_state_pkey on dev_state (cost=0.00..8.29 rows=1 width=8) (actual time=0.009..0.011 rows=1 loops=1,017)

  • Index Cond: (public.dev_state.device_id = interface.device_id)
56. 5.085 5.085 ↑ 1.0 1 1,017

Index Scan using system_id_types_pkey on system_id_types (cost=0.00..0.27 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1,017)

  • Index Cond: (public.system_id_types.id = public.dev_state.system_id)
57. 7.119 7.119 ↑ 1.0 1 1,017

Index Scan using dev_pkey on dev (cost=0.00..8.27 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=1,017)

  • Index Cond: (dev.id = interface.device_id)
58. 0.615 0.967 ↓ 33.0 33 1

Nested Loop (cost=0.00..9.79 rows=1 width=52) (actual time=0.067..0.967 rows=33 loops=1)

59. 0.088 0.088 ↓ 33.0 33 1

Seq Scan on nfa_static_network (cost=0.00..1.50 rows=1 width=11) (actual time=0.032..0.088 rows=33 loops=1)

  • Filter: (family((network)::inet) = 4)
60. 0.264 0.264 ↑ 1.0 1 33

Index Scan using info_site_pkey on info_site (cost=0.00..8.27 rows=1 width=49) (actual time=0.006..0.008 rows=1 loops=33)

  • Index Cond: (public.info_site.id = nfa_static_network.site_id)
  • Filter: ((public.info_site.owner)::text = 'k20'::text)