---CDP--- QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=1141.10..22451.19 rows=8918 width=195) (actual time=8.662..115.940 rows=5051 loops=1) Output: l.id, l.nodeid, n.nodelabel, n.nodesysoid, n.nodesyslocation, n.nodetype, l.cdpcacheifindex, l.cdpinterfacename, e.id, e.nodeid, np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype, l.cdpcachedeviceport -> Nested Loop (cost=1141.10..19589.50 rows=8918 width=125) (actual time=8.635..90.370 rows=5051 loops=1) Output: l.id, l.nodeid, l.cdpcacheifindex, l.cdpinterfacename, l.cdpcachedeviceport, e.id, e.nodeid, n.nodelabel, n.nodesysoid, n.nodesyslocation, n.nodetype -> Merge Right Join (cost=1141.10..3237.82 rows=6874 width=130) (actual time=8.582..18.505 rows=5686 loops=1) Output: l.id, l.nodeid, l.cdpcacheifindex, l.cdpinterfacename, l.cdpcachedeviceport, l.cdpcacheaddress, n.nodelabel, n.nodesysoid, n.nodesyslocation, n.nodetype Merge Cond: (n.nodeid = l.nodeid) -> Index Scan using pk_nodeid on node n (cost=0.00..7083.01 rows=28247 width=74) (actual time=0.064..6.411 rows=2113 loops=1) Output: n.nodeid, n.dpname, n.nodecreatetime, n.nodeparentid, n.nodetype, n.nodesysoid, n.nodesysname, n.nodesysdescription, n.nodesyslocation, n.nodesyscontact, n.nodelabel, n.nodelabelsource, n.nodenetbiosname, n.nodedomainname, n.operatingsystem, n.lastcapsdpoll, n.foreignsource, n.foreignid -> Sort (cost=1140.09..1157.27 rows=6874 width=60) (actual time=8.464..9.263 rows=5686 loops=1) Output: l.id, l.nodeid, l.cdpcacheifindex, l.cdpinterfacename, l.cdpcachedeviceport, l.cdpcacheaddress Sort Key: l.nodeid Sort Method: quicksort Memory: 949kB -> Seq Scan on cdplink l (cost=0.00..701.98 rows=6874 width=60) (actual time=0.066..5.816 rows=5686 loops=1) Output: l.id, l.nodeid, l.cdpcacheifindex, l.cdpinterfacename, l.cdpcachedeviceport, l.cdpcacheaddress Filter: (cdpcacheaddresstype = 1) -> Index Scan using ipinterface_ipaddr_idx on ipinterface e (cost=0.00..2.37 rows=1 width=20) (actual time=0.009..0.012 rows=1 loops=5686) Output: e.id, e.nodeid, e.ipaddr, e.ifindex, e.iphostname, e.ismanaged, e.ipstatus, e.iplastcapsdpoll, e.issnmpprimary, e.snmpinterfaceid Index Cond: (e.ipaddr = l.cdpcacheaddress) -> Index Scan using node_id_type_idx on node np (cost=0.00..0.31 rows=1 width=74) (actual time=0.002..0.004 rows=1 loops=5051) Output: np.nodeid, np.dpname, np.nodecreatetime, np.nodeparentid, np.nodetype, np.nodesysoid, np.nodesysname, np.nodesysdescription, np.nodesyslocation, np.nodesyscontact, np.nodelabel, np.nodelabelsource, np.nodenetbiosname, np.nodedomainname, np.operatingsystem, np.lastcapsdpoll, np.foreignsource, np.foreignid Index Cond: (e.nodeid = np.nodeid) Total runtime: 116.853 ms (23 rows) ---ISIS--- QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=108.57..108.58 rows=1 width=164) (actual time=0.028..0.028 rows=0 loops=1) Output: (LEAST(l1.id, l2.id)), l1.id, l1.nodeid, n.nodelabel, n.nodesysoid, n.nodesyslocation, n.nodetype, l1.isiscircifindex, l2.id, l2.nodeid, np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype, l2.isiscircifindex -> Sort (cost=108.57..108.57 rows=1 width=164) (actual time=0.028..0.028 rows=0 loops=1) Output: (LEAST(l1.id, l2.id)), l1.id, l1.nodeid, n.nodelabel, n.nodesysoid, n.nodesyslocation, n.nodetype, l1.isiscircifindex, l2.id, l2.nodeid, np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype, l2.isiscircifindex Sort Key: (LEAST(l1.id, l2.id)) Sort Method: quicksort Memory: 25kB -> Nested Loop Left Join (cost=41.27..108.56 rows=1 width=164) (actual time=0.003..0.003 rows=0 loops=1) Output: LEAST(l1.id, l2.id), l1.id, l1.nodeid, n.nodelabel, n.nodesysoid, n.nodesyslocation, n.nodetype, l1.isiscircifindex, l2.id, l2.nodeid, np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype, l2.isiscircifindex -> Nested Loop Left Join (cost=41.27..104.16 rows=1 width=98) (actual time=0.003..0.003 rows=0 loops=1) Output: l1.id, l1.nodeid, l1.isiscircifindex, l2.id, l2.nodeid, l2.isiscircifindex, np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype, e1.nodeid -> Nested Loop (cost=41.27..99.77 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1) Output: l1.id, l1.nodeid, l1.isiscircifindex, l2.id, l2.nodeid, l2.isiscircifindex, e2.nodeid, e1.nodeid Join Filter: (l1.nodeid = e1.nodeid) -> Hash Join (cost=41.27..97.71 rows=4 width=110) (actual time=0.003..0.003 rows=0 loops=1) Output: l1.id, l1.nodeid, l1.isiscircifindex, l2.id, l2.nodeid, l2.isiscircifindex, l2.isisisadjneighsysid, e2.nodeid Hash Cond: (((e2.isissysid)::text = (l1.isisisadjneighsysid)::text) AND (e2.nodeid = l2.nodeid)) -> Seq Scan on isiselement e2 (cost=0.00..15.80 rows=580 width=86) (actual time=0.000..0.000 rows=0 loops=1) Output: e2.id, e2.nodeid, e2.isissysid, e2.isissysadminstate, e2.isisnodecreatetime, e2.isisnodelastpolltime -> Hash (cost=37.31..37.31 rows=264 width=188) (never executed) Output: l1.id, l1.nodeid, l1.isiscircifindex, l1.isisisadjneighsysid, l2.id, l2.nodeid, l2.isiscircifindex, l2.isisisadjneighsysid -> Hash Join (cost=15.18..37.31 rows=264 width=188) (never executed) Output: l1.id, l1.nodeid, l1.isiscircifindex, l1.isisisadjneighsysid, l2.id, l2.nodeid, l2.isiscircifindex, l2.isisisadjneighsysid Hash Cond: (l1.isisisadjindex = l2.isisisadjindex) -> Seq Scan on isislink l1 (cost=0.00..12.30 rows=230 width=98) (never executed) Output: l1.id, l1.nodeid, l1.isiscircindex, l1.isisisadjindex, l1.isiscircifindex, l1.isiscircadminstate, l1.isisisadjstate, l1.isisisadjneighsnpaaddress, l1.isisisadjneighsystype, l1.isisisadjneighsysid, l1.isisisadjnbrextendedcircid, l1.isislinkcreatetime, l1.isislinklastpolltime -> Hash (cost=12.30..12.30 rows=230 width=98) (never executed) Output: l2.id, l2.nodeid, l2.isiscircifindex, l2.isisisadjindex, l2.isisisadjneighsysid -> Seq Scan on isislink l2 (cost=0.00..12.30 rows=230 width=98) (never executed) Output: l2.id, l2.nodeid, l2.isiscircifindex, l2.isisisadjindex, l2.isisisadjneighsysid -> Index Scan using isiselement_sysid_idx on isiselement e1 (cost=0.00..0.47 rows=3 width=86) (never executed) Output: e1.id, e1.nodeid, e1.isissysid, e1.isissysadminstate, e1.isisnodecreatetime, e1.isisnodelastpolltime Index Cond: ((e1.isissysid)::text = (l2.isisisadjneighsysid)::text) -> Index Scan using node_id_type_idx on node np (cost=0.00..4.38 rows=1 width=74) (never executed) Output: np.nodeid, np.dpname, np.nodecreatetime, np.nodeparentid, np.nodetype, np.nodesysoid, np.nodesysname, np.nodesysdescription, np.nodesyslocation, np.nodesyscontact, np.nodelabel, np.nodelabelsource, np.nodenetbiosname, np.nodedomainname, np.operatingsystem, np.lastcapsdpoll, np.foreignsource, np.foreignid Index Cond: (np.nodeid = e2.nodeid) -> Index Scan using node_id_type_idx on node n (cost=0.00..4.38 rows=1 width=74) (never executed) Output: n.nodeid, n.dpname, n.nodecreatetime, n.nodeparentid, n.nodetype, n.nodesysoid, n.nodesysname, n.nodesysdescription, n.nodesyslocation, n.nodesyscontact, n.nodelabel, n.nodelabelsource, n.nodenetbiosname, n.nodedomainname, n.operatingsystem, n.lastcapsdpoll, n.foreignsource, n.foreignid Index Cond: (n.nodeid = e1.nodeid) Total runtime: 0.422 ms (39 rows) ---BridgeMAC--- QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=1270694.65..1274617.93 rows=1569312 width=209) (actual time=4556.684..4557.289 rows=3766 loops=1) Output: mlink.id, mlink.nodeid, n.nodelabel, n.nodesysoid, n.nodesyslocation, n.nodetype, mlink.bridgeport, mlink.bridgeportifindex, mlink.bridgeportifname, mlink.vlan, ip.nodeid, np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype, ntm.netaddress, np.nodelabel Sort Key: mlink.bridgeport Sort Method: external merge Disk: 768kB -> Hash Left Join (cost=85958.78..626451.10 rows=1569312 width=209) (actual time=711.198..4552.222 rows=3766 loops=1) Output: mlink.id, mlink.nodeid, n.nodelabel, n.nodesysoid, n.nodesyslocation, n.nodetype, mlink.bridgeport, mlink.bridgeportifindex, mlink.bridgeportifname, mlink.vlan, ip.nodeid, np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype, ntm.netaddress, np.nodelabel Hash Cond: (mlink.nodeid = n.nodeid) -> Merge Join (cost=83344.23..525761.67 rows=1569312 width=139) (actual time=697.193..4539.500 rows=3766 loops=1) Output: mlink.id, mlink.nodeid, mlink.bridgeport, mlink.bridgeportifindex, mlink.bridgeportifname, mlink.vlan, ntm.netaddress, ip.nodeid, np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype Merge Cond: ((mlink.macaddress)::text = (ntm.physaddress)::text) -> Index Scan using bridgemaclink_pk_idx2 on bridgemaclink mlink (cost=0.00..403787.01 rows=5295101 width=65) (actual time=2.297..3739.716 rows=18621 loops=1) Output: mlink.id, mlink.nodeid, mlink.bridgeport, mlink.bridgeportifindex, mlink.bridgeportifname, mlink.vlan, mlink.macaddress, mlink.bridgemaclinkcreatetime, mlink.bridgemaclinklastpolltime -> Materialize (cost=83341.66..85712.94 rows=189702 width=100) (actual time=684.055..769.920 rows=41293 loops=1) Output: ntm.netaddress, ntm.physaddress, ip.nodeid, np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype -> Sort (cost=83341.66..83815.92 rows=189702 width=100) (actual time=684.051..762.253 rows=40104 loops=1) Output: ntm.netaddress, ntm.physaddress, ip.nodeid, np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype Sort Key: ntm.physaddress Sort Method: external merge Disk: 4368kB -> Hash Left Join (cost=3208.37..45956.08 rows=189702 width=100) (actual time=27.633..447.491 rows=40104 loops=1) Output: ntm.netaddress, ntm.physaddress, ip.nodeid, np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype Hash Cond: (ip.nodeid = np.nodeid) -> Merge Join (cost=593.81..36848.05 rows=189702 width=30) (actual time=11.299..404.846 rows=40104 loops=1) Output: ntm.netaddress, ntm.physaddress, ip.nodeid Merge Cond: (ip.ipaddr = ntm.netaddress) -> Index Scan using ipinterface_ipaddr_idx on ipinterface ip (cost=0.00..16888.73 rows=187755 width=16) (actual time=0.046..105.103 rows=34907 loops=1) Output: ip.id, ip.nodeid, ip.ipaddr, ip.ifindex, ip.iphostname, ip.ismanaged, ip.ipstatus, ip.iplastcapsdpoll, ip.issnmpprimary, ip.snmpinterfaceid Filter: (nodeid IS NOT NULL) -> Index Scan using ipnettomedia_pk_idx on ipnettomedia ntm (cost=0.00..17116.03 rows=191451 width=26) (actual time=0.044..197.423 rows=116914 loops=1) Output: ntm.id, ntm.netaddress, ntm.physaddress, ntm.sourcenodeid, ntm.sourceifindex, ntm.createtime, ntm.lastpolltime -> Hash (cost=1902.47..1902.47 rows=28247 width=74) (actual time=16.278..16.278 rows=7575 loops=1) Output: np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype, np.nodeid -> Seq Scan on node np (cost=0.00..1902.47 rows=28247 width=74) (actual time=0.053..13.083 rows=7575 loops=1) Output: np.nodelabel, np.nodesysoid, np.nodesyslocation, np.nodetype, np.nodeid -> Hash (cost=1902.47..1902.47 rows=28247 width=74) (actual time=8.809..8.809 rows=7575 loops=1) Output: n.nodelabel, n.nodesysoid, n.nodesyslocation, n.nodetype, n.nodeid -> Seq Scan on node n (cost=0.00..1902.47 rows=28247 width=74) (actual time=0.021..5.943 rows=7575 loops=1) Output: n.nodelabel, n.nodesysoid, n.nodesyslocation, n.nodetype, n.nodeid Total runtime: 4558.770 ms (38 rows)