explain.depesz.com

A tool for finding a real cause for slow queries.

Result: TI6

options
exclusive inclusive rows x rows loops node
4.803 411707.503 ↓ 340.1 2381 1
→

Unique (cost=8865.23..8865.40 rows=7 width=32) (actual time=411689.547..411707.503 rows=2381 loops=1)

4.749 411702.700 ↓ 476.1 3333 1
→

Subquery Scan nfa_k20_network (cost=8865.23..8865.39 rows=7 width=32) (actual time=411689.543..411702.700 rows=3333 loops=1)

5.973 411697.951 ↓ 476.1 3333 1
→

Unique (cost=8865.23..8865.32 rows=7 width=55) (actual time=411689.540..411697.951 rows=3333 loops=1)

15.983 411691.978 ↓ 480.6 3364 1
→

Sort (cost=8865.23..8865.25 rows=7 width=55) (actual time=411689.537..411691.978 rows=3364 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.668 411675.995 ↓ 480.6 3364 1
→

Append (cost=44.98..8865.13 rows=7 width=55) (actual time=1.856..411675.995 rows=3364 loops=1)

24.029 31.652 ↓ 454.8 2274 1
→

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

  • Hash Cond: (nfa_network.info_site_id = public.info_site.id)
6.191 6.191 ↓ 206.7 2274 1
→

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

  • Filter: (family((network)::inet) = 4)
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)

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)
23.330 411637.680 ↓ 1057.0 1057 1
→

Nested Loop (cost=1851.83..8508.02 rows=1 width=55) (actual time=589.820..411637.680 rows=1057 loops=1)

6.016 411600.609 ↓ 1057.0 1057 1
→

Nested Loop (cost=1851.83..8499.72 rows=1 width=79) (actual time=589.769..411600.609 rows=1057 loops=1)

6.062 411585.080 ↓ 1057.0 1057 1
→

Nested Loop (cost=1851.83..8498.19 rows=1 width=83) (actual time=589.756..411585.080 rows=1057 loops=1)

10.815 411568.448 ↓ 1057.0 1057 1
→

Nested Loop (cost=1851.83..8489.91 rows=1 width=75) (actual time=589.742..411568.448 rows=1057 loops=1)

19356.753 411526.868 ↓ 1465.0 1465 1
→

Nested Loop (cost=1851.83..8488.08 rows=1 width=34) (actual time=305.403..411526.868 rows=1465 loops=1)

  • Join Filter: (interface.device_id = public.dev_state.device_id)
22.773 997.535 ↓ 1465.0 1465 1
→

Nested Loop (cost=0.00..1253.91 rows=1 width=22) (actual time=0.652..997.535 rows=1465 loops=1)

32.111 936.702 ↓ 3806.0 7612 1
→

Nested Loop (cost=0.00..1253.34 rows=2 width=26) (actual time=0.183..936.702 rows=7612 loops=1)

28.118 851.307 ↓ 3806.0 7612 1
→

Nested Loop (cost=0.00..1236.78 rows=2 width=18) (actual time=0.168..851.307 rows=7612 loops=1)

27.854 777.517 ↓ 3806.0 7612 1
→

Nested Loop (cost=0.00..1233.73 rows=2 width=22) (actual time=0.154..777.517 rows=7612 loops=1)

30.458 688.703 ↓ 3810.0 7620 1
→

Nested Loop (cost=0.00..1229.85 rows=2 width=14) (actual time=0.141..688.703 rows=7620 loops=1)

27.676 559.185 ↓ 3810.0 7620 1
→

Nested Loop (cost=0.00..1213.22 rows=2 width=34) (actual time=0.125..559.185 rows=7620 loops=1)

32.315 417.209 ↓ 3810.0 7620 1
→

Nested Loop (cost=0.00..1196.58 rows=2 width=30) (actual time=0.109..417.209 rows=7620 loops=1)

30.801 274.498 ↓ 2830.7 8492 1
→

Nested Loop (cost=0.00..1171.62 rows=3 width=26) (actual time=0.091..274.498 rows=8492 loops=1)

37.301 175.713 ↓ 139.3 8498 1
→

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

27.938 27.938 ↓ 139.3 8498 1
→

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

  • Filter: ((ip IS NOT NULL) AND (family((network(ip))::inet) = 4))
110.474 110.474 ↑ 1.0 1 8498
→

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=8498)

  • Index Cond: (interface.id = ifc_ip.ifc_id)
67.984 67.984 ↑ 1.0 1 8498
→

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=8498)

  • Index Cond: (ifc_monitor.ifc_id = interface.id)
110.396 110.396 ↑ 1.0 1 8492
→

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=8492)

  • Index Cond: (ifc_state.ifc_id = interface.id)
  • Filter: (ifc_state.state = 1)
114.300 114.300 ↑ 1.0 1 7620
→

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=7620)

  • Index Cond: (ifc_network_information.ifc_id = interface.id)
99.060 99.060 ↑ 1.0 1 7620
→

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=7620)

  • Index Cond: (ifc_information.ifc_id = interface.id)
60.960 60.960 ↑ 1.0 1 7620
→

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=7620)

  • Index Cond: (public.dev_state.device_id = interface.device_id)
45.672 45.672 ↑ 1.0 1 7612
→

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=7612)

  • Index Cond: (public.system_id_types.id = public.dev_state.system_id)
53.284 53.284 ↑ 1.0 1 7612
→

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=7612)

  • Index Cond: (dev_classification.device_id = interface.device_id)
38.060 38.060 ↓ 0.0 0 7612
→

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=7612)

  • Index Cond: (sector.id = dev_classification.sector)
  • Filter: ((sector.owner)::text = 'k20'::text)
57339.875 391172.580 ↑ 1.0 16304 1465
→

Hash Left Join (cost=1851.83..7030.37 rows=16304 width=12) (actual time=0.097..267.012 rows=16304 loops=1465)

  • Hash Cond: (public.dev_state.device_id = dev_snmp.device_id)
38517.661 333806.110 ↑ 1.0 16304 1465
→

Hash Left Join (cost=1429.48..6307.85 rows=16304 width=12) (actual time=0.076..227.854 rows=16304 loops=1465)

  • Hash Cond: (public.dev_state.device_id = dev_exception.device_id)
45171.835 295288.330 ↑ 1.0 16304 1465
→

Hash Join (cost=1427.42..6244.26 rows=16304 width=12) (actual time=0.074..201.562 rows=16304 loops=1465)

  • Hash Cond: (public.dev_state.system_id = public.system_id_types.id)
60823.652 250115.055 ↑ 1.0 16304 1465
→

Hash Left Join (cost=1402.04..5994.70 rows=16304 width=16) (actual time=0.071..170.727 rows=16304 loops=1465)

  • Hash Cond: (public.dev_state.device_id = dev_monitor.device_id)
52358.325 189258.955 ↑ 1.0 16304 1465
→

Hash Left Join (cost=865.94..5110.78 rows=16304 width=16) (actual time=0.046..129.187 rows=16304 loops=1465)

  • Hash Cond: (public.dev_state.device_id = dev_code.device_id)
72468.812 136885.205 ↑ 1.0 16304 1465
→

Hash Join (cost=629.84..4602.09 rows=16304 width=16) (actual time=0.033..93.437 rows=16304 loops=1465)

  • Hash Cond: (public.dev_state.device_id = dev_location.device_id)
64382.355 64382.355 ↑ 1.0 19076 1465
→

Seq Scan on dev_state (cost=0.00..3570.76 rows=19076 width=8) (actual time=0.007..43.947 rows=19076 loops=1465)

15.732 34.038 ↑ 1.0 16304 1
→

Hash (cost=426.04..426.04 rows=16304 width=8) (actual time=34.038..34.038 rows=16304 loops=1)

18.306 18.306 ↑ 1.0 16304 1
→

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

7.529 15.425 ↓ 1.0 8050 1
→

Hash (cost=135.49..135.49 rows=8049 width=4) (actual time=15.425..15.425 rows=8050 loops=1)

7.896 7.896 ↓ 1.0 8050 1
→

Seq Scan on dev_code (cost=0.00..135.49 rows=8049 width=4) (actual time=0.038..7.896 rows=8050 loops=1)

15.566 32.448 ↑ 1.0 16849 1
→

Hash (cost=325.49..325.49 rows=16849 width=4) (actual time=32.448..32.448 rows=16849 loops=1)

16.882 16.882 ↑ 1.0 16849 1
→

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

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)

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)

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)

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)

12.809 26.595 ↓ 1.0 13661 1
→

Hash (cost=251.60..251.60 rows=13660 width=4) (actual time=26.595..26.595 rows=13661 loops=1)

13.786 13.786 ↓ 1.0 13661 1
→

Seq Scan on dev_snmp (cost=0.00..251.60 rows=13660 width=4) (actual time=0.026..13.786 rows=13661 loops=1)

30.765 30.765 ↑ 1.0 1 1465
→

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=1465)

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

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=1057)

  • Index Cond: (public.dev_state.device_id = interface.device_id)
9.513 9.513 ↑ 1.0 1 1057
→

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=1057)

  • Index Cond: (public.system_id_types.id = public.dev_state.system_id)
13.741 13.741 ↑ 1.0 1 1057
→

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=1057)

  • Index Cond: (dev.id = interface.device_id)
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)

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)
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)