explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TI6

Settings
# exclusive inclusive rows x rows loops node
1. 4.803 411,707.503 ↓ 340.1 2,381 1

Unique (cost=8,865.23..8,865.40 rows=7 width=32) (actual time=411,689.547..411,707.503 rows=2,381 loops=1)

2. 4.749 411,702.700 ↓ 476.1 3,333 1

Subquery Scan nfa_k20_network (cost=8,865.23..8,865.39 rows=7 width=32) (actual time=411,689.543..411,702.700 rows=3,333 loops=1)

3. 5.973 411,697.951 ↓ 476.1 3,333 1

Unique (cost=8,865.23..8,865.32 rows=7 width=55) (actual time=411,689.540..411,697.951 rows=3,333 loops=1)

4. 15.983 411,691.978 ↓ 480.6 3,364 1

Sort (cost=8,865.23..8,865.25 rows=7 width=55) (actual time=411,689.537..411,691.978 rows=3,364 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: 341kB
5. 5.668 411,675.995 ↓ 480.6 3,364 1

Append (cost=44.98..8,865.13 rows=7 width=55) (actual time=1.856..411,675.995 rows=3,364 loops=1)

6. 24.029 31.652 ↓ 454.8 2,274 1

Hash Join (cost=44.98..347.25 rows=5 width=52) (actual time=1.854..31.652 rows=2,274 loops=1)

  • Hash Cond: (nfa_network.info_site_id = public.info_site.id)
7. 6.191 6.191 ↓ 206.7 2,274 1

Seq Scan on nfa_network (cost=0.00..302.11 rows=11 width=11) (actual time=0.367..6.191 rows=2,274 loops=1)

  • Filter: (family((network)::inet) = 4)
8. 0.606 1.432 ↑ 1.0 453 1

Hash (cost=39.31..39.31 rows=453 width=49) (actual time=1.432..1.432 rows=453 loops=1)

9. 0.826 0.826 ↑ 1.0 453 1

Seq Scan on info_site (cost=0.00..39.31 rows=453 width=49) (actual time=0.016..0.826 rows=453 loops=1)

  • Filter: ((owner)::text = 'k20'::text)
10. 23.330 411,637.680 ↓ 1,057.0 1,057 1

Nested Loop (cost=1,851.83..8,508.02 rows=1 width=55) (actual time=589.820..411,637.680 rows=1,057 loops=1)

11. 6.016 411,600.609 ↓ 1,057.0 1,057 1

Nested Loop (cost=1,851.83..8,499.72 rows=1 width=79) (actual time=589.769..411,600.609 rows=1,057 loops=1)

12. 6.062 411,585.080 ↓ 1,057.0 1,057 1

Nested Loop (cost=1,851.83..8,498.19 rows=1 width=83) (actual time=589.756..411,585.080 rows=1,057 loops=1)

13. 10.815 411,568.448 ↓ 1,057.0 1,057 1

Nested Loop (cost=1,851.83..8,489.91 rows=1 width=75) (actual time=589.742..411,568.448 rows=1,057 loops=1)

14. 19,356.753 411,526.868 ↓ 1,465.0 1,465 1

Nested Loop (cost=1,851.83..8,488.08 rows=1 width=34) (actual time=305.403..411,526.868 rows=1,465 loops=1)

  • Join Filter: (interface.device_id = public.dev_state.device_id)
15. 22.773 997.535 ↓ 1,465.0 1,465 1

Nested Loop (cost=0.00..1,253.91 rows=1 width=22) (actual time=0.652..997.535 rows=1,465 loops=1)

16. 32.111 936.702 ↓ 3,806.0 7,612 1

Nested Loop (cost=0.00..1,253.34 rows=2 width=26) (actual time=0.183..936.702 rows=7,612 loops=1)

17. 28.118 851.307 ↓ 3,806.0 7,612 1

Nested Loop (cost=0.00..1,236.78 rows=2 width=18) (actual time=0.168..851.307 rows=7,612 loops=1)

18. 27.854 777.517 ↓ 3,806.0 7,612 1

Nested Loop (cost=0.00..1,233.73 rows=2 width=22) (actual time=0.154..777.517 rows=7,612 loops=1)

19. 30.458 688.703 ↓ 3,810.0 7,620 1

Nested Loop (cost=0.00..1,229.85 rows=2 width=14) (actual time=0.141..688.703 rows=7,620 loops=1)

20. 27.676 559.185 ↓ 3,810.0 7,620 1

Nested Loop (cost=0.00..1,213.22 rows=2 width=34) (actual time=0.125..559.185 rows=7,620 loops=1)

21. 32.315 417.209 ↓ 3,810.0 7,620 1

Nested Loop (cost=0.00..1,196.58 rows=2 width=30) (actual time=0.109..417.209 rows=7,620 loops=1)

22. 30.801 274.498 ↓ 2,830.7 8,492 1

Nested Loop (cost=0.00..1,171.62 rows=3 width=26) (actual time=0.091..274.498 rows=8,492 loops=1)

23. 37.301 175.713 ↓ 139.3 8,498 1

Nested Loop (cost=0.00..798.37 rows=61 width=22) (actual time=0.076..175.713 rows=8,498 loops=1)

24. 27.938 27.938 ↓ 139.3 8,498 1

Seq Scan on ifc_ip (cost=0.00..299.13 rows=61 width=14) (actual time=0.046..27.938 rows=8,498 loops=1)

  • Filter: ((ip IS NOT NULL) AND (family((network(ip))::inet) = 4))
25. 110.474 110.474 ↑ 1.0 1 8,498

Index Scan using interface_pkey on interface (cost=0.00..8.17 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=8,498)

  • Index Cond: (interface.id = ifc_ip.ifc_id)
26. 67.984 67.984 ↑ 1.0 1 8,498

Index Scan using ifc_monitor_pkey on ifc_monitor (cost=0.00..6.11 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=8,498)

  • Index Cond: (ifc_monitor.ifc_id = interface.id)
27. 110.396 110.396 ↑ 1.0 1 8,492

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

  • Index Cond: (ifc_state.ifc_id = interface.id)
  • Filter: (ifc_state.state = 1)
28. 114.300 114.300 ↑ 1.0 1 7,620

Index Scan using ifc_network_information_pkey on ifc_network_information (cost=0.00..8.31 rows=1 width=4) (actual time=0.013..0.015 rows=1 loops=7,620)

  • Index Cond: (ifc_network_information.ifc_id = interface.id)
29. 99.060 99.060 ↑ 1.0 1 7,620

Index Scan using ifc_information_pkey on ifc_information (cost=0.00..8.30 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=7,620)

  • Index Cond: (ifc_information.ifc_id = interface.id)
30. 60.960 60.960 ↑ 1.0 1 7,620

Index Scan using dev_state_pkey on dev_state (cost=0.00..1.92 rows=1 width=8) (actual time=0.006..0.008 rows=1 loops=7,620)

  • Index Cond: (public.dev_state.device_id = interface.device_id)
31. 45.672 45.672 ↑ 1.0 1 7,612

Index Scan using system_id_types_pkey on system_id_types (cost=0.00..1.51 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=7,612)

  • Index Cond: (public.system_id_types.id = public.dev_state.system_id)
32. 53.284 53.284 ↑ 1.0 1 7,612

Index Scan using dev_classification_pkey on dev_classification (cost=0.00..8.27 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=7,612)

  • Index Cond: (dev_classification.device_id = interface.device_id)
33. 38.060 38.060 ↓ 0.0 0 7,612

Index Scan using sector_pkey on sector (cost=0.00..0.27 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=7,612)

  • Index Cond: (sector.id = dev_classification.sector)
  • Filter: ((sector.owner)::text = 'k20'::text)
34. 57,339.875 391,172.580 ↑ 1.0 16,304 1,465

Hash Left Join (cost=1,851.83..7,030.37 rows=16,304 width=12) (actual time=0.097..267.012 rows=16,304 loops=1,465)

  • Hash Cond: (public.dev_state.device_id = dev_snmp.device_id)
35. 38,517.661 333,806.110 ↑ 1.0 16,304 1,465

Hash Left Join (cost=1,429.48..6,307.85 rows=16,304 width=12) (actual time=0.076..227.854 rows=16,304 loops=1,465)

  • Hash Cond: (public.dev_state.device_id = dev_exception.device_id)
36. 45,171.835 295,288.330 ↑ 1.0 16,304 1,465

Hash Join (cost=1,427.42..6,244.26 rows=16,304 width=12) (actual time=0.074..201.562 rows=16,304 loops=1,465)

  • Hash Cond: (public.dev_state.system_id = public.system_id_types.id)
37. 60,823.652 250,115.055 ↑ 1.0 16,304 1,465

Hash Left Join (cost=1,402.04..5,994.70 rows=16,304 width=16) (actual time=0.071..170.727 rows=16,304 loops=1,465)

  • Hash Cond: (public.dev_state.device_id = dev_monitor.device_id)
38. 52,358.325 189,258.955 ↑ 1.0 16,304 1,465

Hash Left Join (cost=865.94..5,110.78 rows=16,304 width=16) (actual time=0.046..129.187 rows=16,304 loops=1,465)

  • Hash Cond: (public.dev_state.device_id = dev_code.device_id)
39. 72,468.812 136,885.205 ↑ 1.0 16,304 1,465

Hash Join (cost=629.84..4,602.09 rows=16,304 width=16) (actual time=0.033..93.437 rows=16,304 loops=1,465)

  • Hash Cond: (public.dev_state.device_id = dev_location.device_id)
40. 64,382.355 64,382.355 ↑ 1.0 19,076 1,465

Seq Scan on dev_state (cost=0.00..3,570.76 rows=19,076 width=8) (actual time=0.007..43.947 rows=19,076 loops=1,465)

41. 15.732 34.038 ↑ 1.0 16,304 1

Hash (cost=426.04..426.04 rows=16,304 width=8) (actual time=34.038..34.038 rows=16,304 loops=1)

42. 18.306 18.306 ↑ 1.0 16,304 1

Seq Scan on dev_location (cost=0.00..426.04 rows=16,304 width=8) (actual time=0.027..18.306 rows=16,304 loops=1)

43. 7.529 15.425 ↓ 1.0 8,050 1

Hash (cost=135.49..135.49 rows=8,049 width=4) (actual time=15.425..15.425 rows=8,050 loops=1)

44. 7.896 7.896 ↓ 1.0 8,050 1

Seq Scan on dev_code (cost=0.00..135.49 rows=8,049 width=4) (actual time=0.038..7.896 rows=8,050 loops=1)

45. 15.566 32.448 ↑ 1.0 16,849 1

Hash (cost=325.49..325.49 rows=16,849 width=4) (actual time=32.448..32.448 rows=16,849 loops=1)

46. 16.882 16.882 ↑ 1.0 16,849 1

Seq Scan on dev_monitor (cost=0.00..325.49 rows=16,849 width=4) (actual time=0.030..16.882 rows=16,849 loops=1)

47. 0.677 1.440 ↑ 1.0 728 1

Hash (cost=16.28..16.28 rows=728 width=4) (actual time=1.440..1.440 rows=728 loops=1)

48. 0.763 0.763 ↑ 1.0 728 1

Seq Scan on system_id_types (cost=0.00..16.28 rows=728 width=4) (actual time=0.008..0.763 rows=728 loops=1)

49. 0.046 0.119 ↑ 1.0 47 1

Hash (cost=1.47..1.47 rows=47 width=4) (actual time=0.119..0.119 rows=47 loops=1)

50. 0.073 0.073 ↑ 1.0 47 1

Seq Scan on dev_exception (cost=0.00..1.47 rows=47 width=4) (actual time=0.033..0.073 rows=47 loops=1)

51. 12.809 26.595 ↓ 1.0 13,661 1

Hash (cost=251.60..251.60 rows=13,660 width=4) (actual time=26.595..26.595 rows=13,661 loops=1)

52. 13.786 13.786 ↓ 1.0 13,661 1

Seq Scan on dev_snmp (cost=0.00..251.60 rows=13,660 width=4) (actual time=0.026..13.786 rows=13,661 loops=1)

53. 30.765 30.765 ↑ 1.0 1 1,465

Index Scan using info_site_pkey on info_site (cost=0.00..1.82 rows=1 width=49) (actual time=0.020..0.021 rows=1 loops=1,465)

  • Index Cond: (public.info_site.id = dev_location.site_id)
  • Filter: ((public.info_site.siteid)::text !~~ 'k20%dis'::text)
54. 10.570 10.570 ↑ 1.0 1 1,057

Index Scan using dev_state_pkey on dev_state (cost=0.00..8.27 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1,057)

  • Index Cond: (public.dev_state.device_id = interface.device_id)
55. 9.513 9.513 ↑ 1.0 1 1,057

Index Scan using system_id_types_pkey on system_id_types (cost=0.00..1.51 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1,057)

  • Index Cond: (public.system_id_types.id = public.dev_state.system_id)
56. 13.741 13.741 ↑ 1.0 1 1,057

Index Scan using dev_pkey on dev (cost=0.00..8.27 rows=1 width=4) (actual time=0.011..0.013 rows=1 loops=1,057)

  • Index Cond: (dev.id = interface.device_id)
57. 0.612 0.995 ↓ 33.0 33 1

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

58. 0.086 0.086 ↓ 33.0 33 1

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

  • Filter: (family((network)::inet) = 4)
59. 0.297 0.297 ↑ 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.007..0.009 rows=1 loops=33)

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