Skip to content

Instantly share code, notes, and snippets.

/gist:1001317

Created May 31, 2011 21:27
Show Gist options
  • Save anonymous/1001317 to your computer and use it in GitHub Desktop.
Save anonymous/1001317 to your computer and use it in GitHub Desktop.
Download ZIP
claar-postgres-plan
Raw
gistfile1.txt
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2155317.04..2155317.17 rows=50 width=592) (actual time=8299.726..8299.760 rows=50 loops=1)
-> Sort (cost=2155317.04..2155317.45 rows=162 width=592) (actual time=8299.719..8299.736 rows=50 loops=1)
Sort Key: inv_asset_view.asset_id
Sort Method: top-N heapsort Memory: 26kB
-> Subquery Scan on inv_asset_view (cost=97.06..2155311.66 rows=162 width=592) (actual time=27.112..8294.374 rows=2703 loops=1)
-> Nested Loop Left Join (cost=97.06..2155310.04 rows=162 width=2430) (actual time=27.110..8292.226 rows=2703 loops=1)
-> Hash Left Join (cost=97.06..743.44 rows=162 width=2286) (actual time=9.989..75.682 rows=2703 loops=1)
Hash Cond: (inv_asset_full.asset_id = inv_monitor.mon_asset_id)
-> Nested Loop Left Join (cost=47.81..693.23 rows=162 width=2008) (actual time=6.376..67.044 rows=2703 loops=1)
-> Hash Left Join (cost=47.81..155.84 rows=162 width=506) (actual time=6.220..41.250 rows=2703 loops=1)
Hash Cond: (inv_asset_full.asset_vendor_id = inv_vendor.vendor_id)
-> Hash Left Join (cost=42.79..150.13 rows=162 width=310) (actual time=5.990..38.113 rows=2703 loops=1)
Hash Cond: (inv_asset_full.asset_dept_id = admin_dept.dept_id)
-> Nested Loop Left Join (cost=41.23..146.34 rows=162 width=250) (actual time=5.928..34.657 rows=2703 loops=1)
Join Filter: (inv_status.status_id = inv_asset_full.asset_status_id)
-> Hash Left Join (cost=41.23..142.80 rows=162 width=238) (actual time=5.888..28.738 rows=2703 loops=1)
Hash Cond: (inv_asset_full.asset_type_id = inv_asset_type.type_id)
-> Hash Left Join (cost=39.85..139.19 rows=162 width=177) (actual time=5.831..25.352 rows=2703 loops=1)
Hash Cond: (admin_building_room.room_floor_id = admin_building_floor.floor_id)
-> Hash Left Join (cost=36.35..133.46 rows=162 width=161) (actual time=5.669..22.020 rows=2703 loops=1)
Hash Cond: (inv_asset_full.asset_room_id = admin_building_room.room_id)
-> Nested Loop (cost=6.03..100.91 rows=162 width=153) (actual time=4.400..16.779 rows=2703 loops=1)
-> HashAggregate (cost=0.27..0.28 rows=1 width=4) (actual time=4.101..4.159 rows=69 loops=1)
-> Result (cost=0.00..0.26 rows=1 width=0) (actual time=1.891..4.015 rows=85 loops=1)
-> Bitmap Heap Scan on inv_asset_full (cost=5.75..98.19 rows=195 width=153) (actual time=0.020..0.150 rows=39 loops=69)
Recheck Cond: (inv_asset_full.priv = (get_user_view_privs()))
Filter: (inv_asset_full.asset_status_id = 0)
-> Bitmap Index Scan on inv_asset_full_priv_idx (cost=0.00..5.71 rows=195 width=0) (actual time=0.016..0.016 rows=49 loops=69)
Index Cond: (inv_asset_full.priv = (get_user_view_privs()))
-> Hash (cost=17.92..17.92 rows=992 width=12) (actual time=1.261..1.261 rows=992 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 34kB
-> Seq Scan on admin_building_room (cost=0.00..17.92 rows=992 width=12) (actual time=0.046..0.673 rows=992 loops=1)
-> Hash (cost=3.08..3.08 rows=34 width=24) (actual time=0.109..0.109 rows=34 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Hash Left Join (cost=1.27..3.08 rows=34 width=24) (actual time=0.056..0.093 rows=34 loops=1)
Hash Cond: (admin_building_floor.floor_building_id = admin_building.building_id)
-> Seq Scan on admin_building_floor (cost=0.00..1.34 rows=34 width=12) (actual time=0.002..0.007 rows=34 loops=1)
-> Hash (cost=1.12..1.12 rows=12 width=16) (actual time=0.011..0.011 rows=12 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on admin_building (cost=0.00..1.12 rows=12 width=16) (actual time=0.003..0.004 rows=12 loops=1)
-> Hash (cost=1.17..1.17 rows=17 width=61) (actual time=0.014..0.014 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Seq Scan on inv_asset_type (cost=0.00..1.17 rows=17 width=61) (actual time=0.002..0.005 rows=17 loops=1)
-> Materialize (cost=0.00..1.12 rows=1 width=12) (actual time=0.000..0.001 rows=1 loops=2703)
-> Seq Scan on inv_status (cost=0.00..1.11 rows=1 width=12) (actual time=0.017..0.020 rows=1 loops=1)
Filter: (status_id = 0)
-> Hash (cost=1.25..1.25 rows=25 width=60) (actual time=0.021..0.021 rows=25 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Seq Scan on admin_dept (cost=0.00..1.25 rows=25 width=60) (actual time=0.003..0.011 rows=25 loops=1)
-> Hash (cost=3.34..3.34 rows=134 width=196) (actual time=0.187..0.187 rows=134 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on inv_vendor (cost=0.00..3.34 rows=134 width=196) (actual time=0.019..0.076 rows=134 loops=1)
-> Index Scan using inv_computer_comp_asset_id_key on inv_computer (cost=0.00..3.30 rows=1 width=1502) (actual time=0.006..0.007 rows=0 loops=2703)
Index Cond: (inv_computer.comp_asset_id = inv_asset_full.asset_id)
-> Hash (cost=39.11..39.11 rows=811 width=278) (actual time=3.548..3.548 rows=811 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 235kB
-> Seq Scan on inv_monitor (cost=0.00..39.11 rows=811 width=278) (actual time=0.044..1.469 rows=811 loops=1)
-> Index Scan using inv_hwinfo_pkey on inv_hwinfo (cost=0.00..2.52 rows=1 width=144) (actual time=0.006..0.006 rows=0 loops=2703)
Index Cond: (inv_computer.comp_hwinfo_id = inv_hwinfo.hwinfo_id)
SubPlan 1
-> Unique (cost=2.44..2.45 rows=2 width=24) (actual time=0.010..0.010 rows=0 loops=2703)
-> Sort (cost=2.44..2.45 rows=2 width=24) (actual time=0.009..0.009 rows=0 loops=2703)
Sort Key: inv_attachment.att_filename
Sort Method: quicksort Memory: 17kB
-> Hash Left Join (cost=1.29..2.43 rows=2 width=24) (actual time=0.005..0.006 rows=0 loops=2703)
Hash Cond: (inv_asset2attachment.ass2att_att_id = inv_attachment.att_id)
-> Seq Scan on inv_asset2attachment (cost=0.00..1.11 rows=2 width=4) (actual time=0.004..0.004 rows=0 loops=2703)
Filter: (ass2att_asset_id = $0)
-> Hash (cost=1.13..1.13 rows=13 width=28) (actual time=0.016..0.016 rows=13 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on inv_attachment (cost=0.00..1.13 rows=13 width=28) (actual time=0.004..0.010 rows=13 loops=1)
SubPlan 2
-> Unique (cost=16.61..16.62 rows=1 width=7) (actual time=0.013..0.013 rows=0 loops=2703)
-> Sort (cost=16.61..16.62 rows=1 width=7) (actual time=0.012..0.012 rows=0 loops=2703)
Sort Key: public.admin_user.user_username
Sort Method: quicksort Memory: 17kB
-> Nested Loop Left Join (cost=0.00..16.60 rows=1 width=7) (actual time=0.007..0.009 rows=0 loops=2703)
-> Index Scan using inv_asset2user_ass2usr_wrel_idx on inv_asset2user (cost=0.00..8.32 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=2703)
Index Cond: ((ass2user_asset_id = $0) AND (ass2user_relationship < 3))
-> Index Scan using user_pkey on admin_user (cost=0.00..8.27 rows=1 width=11) (actual time=0.008..0.008 rows=1 loops=844)
Index Cond: (public.admin_user.user_id = public.inv_asset2user.ass2user_user_id)
SubPlan 3
-> Unique (cost=16.61..16.62 rows=1 width=7) (actual time=0.008..0.009 rows=0 loops=2703)
-> Sort (cost=16.61..16.62 rows=1 width=7) (actual time=0.007..0.007 rows=0 loops=2703)
Sort Key: public.admin_user.user_last_name
Sort Method: quicksort Memory: 17kB
-> Nested Loop Left Join (cost=0.00..16.60 rows=1 width=7) (actual time=0.004..0.005 rows=0 loops=2703)
-> Index Scan using inv_asset2user_ass2usr_wrel_idx on inv_asset2user (cost=0.00..8.32 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2703)
Index Cond: ((ass2user_asset_id = $0) AND (ass2user_relationship < 3))
-> Index Scan using user_pkey on admin_user (cost=0.00..8.27 rows=1 width=11) (actual time=0.002..0.003 rows=1 loops=844)
Index Cond: (public.admin_user.user_id = public.inv_asset2user.ass2user_user_id)
SubPlan 4
-> Sort (cost=65.14..65.14 rows=1 width=10) (actual time=0.397..0.397 rows=0 loops=2703)
Sort Key: public.inv_asset2nwdev.ass2dev_id
Sort Method: quicksort Memory: 17kB
-> Seq Scan on inv_asset2nwdev (cost=0.00..65.12 rows=1 width=10) (actual time=0.321..0.394 rows=0 loops=2703)
Filter: (ass2dev_asset_id = $0)
SubPlan 6
-> Index Scan using ip_dns_aliases_alias_name_key on ip_dns_alias (cost=0.00..11085.40 rows=3 width=32) (actual time=0.521..0.566 rows=0 loops=2703)
Filter: (CASE WHEN (alias_asset_id IS NULL) THEN (SubPlan 5) ELSE alias_asset_id END = $0)
SubPlan 5
-> Nested Loop (cost=0.00..16.55 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=62169)
-> Index Scan using ip_address_pkey on ip_address_full ip_address (cost=0.00..8.27 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=62169)
Index Cond: (ip_id = $1)
-> Index Scan using inv_asset2nwdev_pkey on inv_asset2nwdev (cost=0.00..8.27 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=62169)
Index Cond: (public.inv_asset2nwdev.ass2dev_id = ip_address.ip_ass2dev_id)
SubPlan 7
-> Sort (cost=16.02..16.03 rows=1 width=20) (actual time=0.109..0.109 rows=0 loops=2703)
Sort Key: public.inv_software.sw_nickname
Sort Method: quicksort Memory: 17kB
-> Hash Join (cost=11.47..16.01 rows=1 width=20) (actual time=0.101..0.105 rows=0 loops=2703)
Hash Cond: (public.inv_software.sw_id = inv_software_license.lic_sw_id)
-> Seq Scan on inv_software (cost=0.00..4.11 rows=111 width=24) (actual time=0.002..0.031 rows=111 loops=246)
-> Hash (cost=11.46..11.46 rows=1 width=4) (actual time=0.093..0.093 rows=0 loops=2703)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on inv_software_license (cost=0.00..11.46 rows=1 width=4) (actual time=0.088..0.092 rows=0 loops=2703)
Filter: (lic_asset_id = $0)
SubPlan 8
-> Sort (cost=1559.65..1560.01 rows=145 width=27) (actual time=0.253..0.255 rows=10 loops=2703)
Sort Key: public.inv_software.sw_nickname
Sort Method: quicksort Memory: 17kB
-> Nested Loop Left Join (cost=50.05..1554.45 rows=145 width=27) (actual time=0.054..0.240 rows=10 loops=2703)
-> Hash Join (cost=50.05..1130.39 rows=145 width=24) (actual time=0.051..0.182 rows=10 loops=2703)
Hash Cond: (inv_software2wmi.sw2wmi_sw_id = public.inv_software.sw_id)
-> Hash Join (cost=44.55..1122.90 rows=145 width=8) (actual time=0.050..0.172 rows=10 loops=2703)
Hash Cond: (inv_software_installed.installed_wmisw_id = inv_software2wmi.sw2wmi_wmisw_id)
-> Bitmap Heap Scan on inv_software_installed (cost=11.59..1086.36 rows=425 width=4) (actual time=0.037..0.124 rows=130 loops=2703)
Recheck Cond: (installed_asset_id = $0)
-> Bitmap Index Scan on inv_software_installed_unique (cost=0.00..11.48 rows=425 width=0) (actual time=0.034..0.034 rows=130 loops=2703)
Index Cond: (installed_asset_id = $0)
-> Hash (cost=18.54..18.54 rows=1154 width=8) (actual time=1.349..1.349 rows=1154 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on inv_software2wmi (cost=0.00..18.54 rows=1154 width=8) (actual time=0.046..0.726 rows=1154 loops=1)
-> Hash (cost=4.11..4.11 rows=111 width=24) (actual time=0.134..0.134 rows=111 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 6kB
-> Seq Scan on inv_software (cost=0.00..4.11 rows=111 width=24) (actual time=0.003..0.050 rows=111 loops=1)
-> Index Scan using inv_software_wmi_pkey on inv_software_wmi (cost=0.00..2.91 rows=1 width=11) (actual time=0.004..0.004 rows=1 loops=27409)
Index Cond: (inv_software2wmi.sw2wmi_wmisw_id = inv_software_wmi.wmisw_id)
SubPlan 9
-> Sort (cost=376.87..376.97 rows=42 width=178) (actual time=0.116..0.119 rows=13 loops=2703)
Sort Key: inv_computer_process.process_path
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=4.58..375.73 rows=42 width=178) (actual time=0.019..0.098 rows=13 loops=2703)
-> Bitmap Heap Scan on inv_computer2process (cost=4.58..115.62 rows=42 width=8) (actual time=0.015..0.030 rows=13 loops=2703)
Recheck Cond: (comp2process_comp_id = $2)
-> Bitmap Index Scan on inv_computer2process_pkey (cost=0.00..4.57 rows=42 width=0) (actual time=0.006..0.006 rows=13 loops=2703)
Index Cond: (comp2process_comp_id = $2)
-> Index Scan using inv_computer_process_process_id_key on inv_computer_process (cost=0.00..6.17 rows=1 width=186) (actual time=0.003..0.003 rows=1 loops=35262)
Index Cond: (inv_computer_process.process_id = inv_computer2process.comp2process_process_id)
SubPlan 10
-> Sort (cost=65.14..65.14 rows=1 width=11) (actual time=0.376..0.376 rows=0 loops=2703)
Sort Key: public.inv_asset2nwdev.ass2dev_id
Sort Method: quicksort Memory: 17kB
-> Seq Scan on inv_asset2nwdev (cost=0.00..65.12 rows=1 width=11) (actual time=0.311..0.373 rows=0 loops=2703)
Filter: (ass2dev_asset_id = $0)
SubPlan 11
-> Sort (cost=92.54..92.55 rows=1 width=36) (actual time=1.089..1.089 rows=0 loops=2703)
Sort Key: public.inv_asset2nwdev.ass2dev_id
Sort Method: quicksort Memory: 17kB
-> Nested Loop Left Join (cost=10.31..92.53 rows=1 width=36) (actual time=0.671..1.086 rows=0 loops=2703)
Join Filter: (ip_address_full.ip_ass2dev_id = public.inv_asset2nwdev.ass2dev_id)
-> Seq Scan on inv_asset2nwdev (cost=0.00..65.12 rows=1 width=4) (actual time=0.312..0.371 rows=0 loops=2703)
Filter: (ass2dev_asset_id = $0)
-> Nested Loop (cost=10.31..24.52 rows=231 width=36) (actual time=0.059..1.293 rows=1154 loops=1224)
-> HashAggregate (cost=0.27..0.28 rows=1 width=4) (actual time=0.002..0.026 rows=69 loops=1224)
-> Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.674..1.481 rows=85 loops=1)
-> Bitmap Heap Scan on ip_address_full (cost=10.04..21.35 rows=231 width=40) (actual time=0.004..0.009 rows=17 loops=84456)
Recheck Cond: (ip_address_full.priv = (get_user_view_privs()))
-> Bitmap Index Scan on ip_address_full_priv_idx (cost=0.00..9.98 rows=231 width=0) (actual time=0.003..0.003 rows=17 loops=84456)
Index Cond: (ip_address_full.priv = (get_user_view_privs()))
Total runtime: 8303.793 ms
(171 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment