Skip to content

Instantly share code, notes, and snippets.

@claar

claar/gist:1001327

Forked from anonymous/gist:1001317
Created May 31, 2011 21:32
Show Gist options
  • Save claar/1001327 to your computer and use it in GitHub Desktop.
Save claar/1001327 to your computer and use it in GitHub Desktop.
Download ZIP
claar-postgres-plan
Raw
gistfile1.txt
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2155935.40..2155935.52 rows=50 width=4) (actual time=8060.510..8060.547 rows=50 loops=1)
-> Sort (cost=2155935.40..2155935.80 rows=162 width=4) (actual time=8060.504..8060.523 rows=50 loops=1)
Sort Key: inv_asset_view.asset_id
Sort Method: top-N heapsort Memory: 18kB
-> Subquery Scan on inv_asset_view (cost=97.06..2155930.01 rows=162 width=4) (actual time=28.885..8057.971 rows=2703 loops=1)
-> Hash Left Join (cost=97.06..2155928.39 rows=162 width=2430) (actual time=28.884..8056.156 rows=2703 loops=1)
Hash Cond: (inv_asset_full.asset_id = inv_monitor.mon_asset_id)
-> Nested Loop Left Join (cost=47.81..1064.89 rows=162 width=2152) (actual time=7.229..92.130 rows=2703 loops=1)
-> Nested Loop Left Join (cost=47.81..741.24 rows=162 width=2008) (actual time=7.053..68.687 rows=2703 loops=1)
-> Hash Left Join (cost=47.81..155.84 rows=162 width=506) (actual time=6.894..42.527 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=6.574..39.534 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=6.482..36.129 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=6.427..30.171 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=6.340..26.731 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=6.133..23.236 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.750..17.872 rows=2703 loops=1)
-> HashAggregate (cost=0.27..0.28 rows=1 width=4) (actual time=4.336..4.410 rows=69 loops=1)
-> Result (cost=0.00..0.26 rows=1 width=0) (actual time=2.050..4.237 rows=85 loops=1)
-> Bitmap Heap Scan on inv_asset_full (cost=5.75..98.19 rows=195 width=153) (actual time=0.022..0.161 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.017..0.017 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.375..1.375 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.653 rows=992 loops=1)
-> Hash (cost=3.08..3.08 rows=34 width=24) (actual time=0.147..0.147 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.093..0.128 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.003..0.011 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.002..0.006 rows=12 loops=1)
-> Hash (cost=1.17..1.17 rows=17 width=61) (actual time=0.015..0.015 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.003..0.008 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.019..0.021 rows=1 loops=1)
Filter: (status_id = 0)
-> Hash (cost=1.25..1.25 rows=25 width=60) (actual time=0.020..0.020 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.009 rows=25 loops=1)
-> Hash (cost=3.34..3.34 rows=134 width=196) (actual time=0.243..0.243 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.020..0.073 rows=134 loops=1)
-> Index Scan using inv_computer_comp_asset_id_key on inv_computer (cost=0.00..3.60 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)
-> Index Scan using inv_hwinfo_pkey on inv_hwinfo (cost=0.00..1.99 rows=1 width=144) (actual time=0.006..0.007 rows=0 loops=2703)
Index Cond: (inv_computer.comp_hwinfo_id = inv_hwinfo.hwinfo_id)
-> Hash (cost=39.11..39.11 rows=811 width=278) (actual time=3.438..3.438 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.043..1.369 rows=811 loops=1)
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.006..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.008 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.008..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.009 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.008..0.008 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.370..0.370 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.306..0.367 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.45 rows=3 width=32) (actual time=0.491..0.533 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.007 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.002..0.003 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.107..0.107 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.099..0.103 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.032 rows=111 loops=246)
-> Hash (cost=11.46..11.46 rows=1 width=4) (actual time=0.094..0.094 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.093 rows=0 loops=2703)
Filter: (lic_asset_id = $0)
SubPlan 8
-> Sort (cost=1559.65..1560.01 rows=145 width=27) (actual time=0.251..0.254 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.055..0.238 rows=10 loops=2703)
-> Hash Join (cost=50.05..1130.39 rows=145 width=24) (actual time=0.052..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.051..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.038..0.123 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.499..1.499 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.692 rows=1154 loops=1)
-> Hash (cost=4.11..4.11 rows=111 width=24) (actual time=0.161..0.161 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.004..0.046 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.003..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.110..0.113 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.012..0.092 rows=13 loops=2703)
-> Bitmap Heap Scan on inv_computer2process (cost=4.58..115.62 rows=42 width=8) (actual time=0.008..0.024 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=35263)
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.371..0.371 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.308..0.368 rows=0 loops=2703)
Filter: (ass2dev_asset_id = $0)
SubPlan 11
-> Sort (cost=96.54..96.55 rows=1 width=36) (actual time=1.072..1.072 rows=0 loops=2703)
Sort Key: public.inv_asset2nwdev.ass2dev_id
Sort Method: quicksort Memory: 17kB
-> Nested Loop Left Join (cost=10.31..96.53 rows=1 width=36) (actual time=0.658..1.069 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.305..0.359 rows=0 loops=2703)
Filter: (ass2dev_asset_id = $0)
-> Nested Loop (cost=10.31..28.52 rows=231 width=36) (actual time=0.064..1.283 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.903..1.565 rows=85 loops=1)
-> Bitmap Heap Scan on ip_address_full (cost=10.04..25.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.004..0.004 rows=17 loops=84456)
Index Cond: (ip_address_full.priv = (get_user_view_privs()))
Total runtime: 8066.796 ms
(171 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment