Attachment 1448664 Details for Bug 1581677 – explain analyze output

explain analyze output

explain_analyze_output.log (text/plain), 45.07 KB, created by mlehrer on 2018-06-07 09:51:47 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: mlehrer

Created: 2018-06-07 09:51:47 UTC

Size: 45.07 KB

patch

obsolete

>psql -t -l -U engine -c "EXPLAIN ANALYZE SELECT * FROM(SELECT * FROM vms WHERE( vm_guid IN (SELECT distinct vms_with_tags.vm_guid FROM  vms_with_tags LEFT OUTER JOIN vds_with_tags ON vms_with_tags.run_on_vds=vds_with_tags.vds_id WHERE vms.vm_name LIKE '%L1%' AND ((vds_with_tags.cluster_name LIKE '%b01-h13%%' OR vds_with_tags.cpu_model LIKE '%b01-h13%%' OR vds_with_tags.free_text_comment LIKE '%b01-h13%%' OR vds_with_tags.host_name LIKE '%b01-h13%%' OR vds_with_tags.software_version LIKE '%b01-h13%%' OR vds_with_tags.storage_pool_name LIKE '%b01-h13%%' OR vds_with_tags.tag_name LIKE '%b01-h13%%' OR vds_with_tags.vds_name LIKE '%b01-h13%%') AND vms.vm_name LIKE '%L1%'  ))) ORDER BY vm_name ASC) as T1 OFFSET (1 -1) LIMIT 100;"
>
>
> Limit  (cost=548.76..47104541.99 rows=100 width=8093) (actual time=24919.404..1416567.810 rows=100 loop
>s=1)
>   ->  Nested Loop Left Join  (cost=548.76..953856391.35 rows=2025 width=11378) (actual time=24919.402..
>1416567.721 rows=100 loops=1)
>         Join Filter: (image_details.vm_id = vm_static.vm_guid)
>         Rows Removed by Join Filter: 407766
>         ->  Nested Loop Left Join  (cost=169.83..953730218.47 rows=2025 width=11377) (actual time=24912
>.155..1416479.279 rows=100 loops=1)
>               Join Filter: (vm_static.vm_guid = vm_snapshots.vm_id)
>               Rows Removed by Join Filter: 406368
>               ->  Nested Loop Left Join  (cost=0.67..953606189.53 rows=2025 width=11375) (actual time=2
>4908.152..1416407.153 rows=100 loops=1)
>                     ->  Nested Loop Left Join  (cost=0.28..953604369.63 rows=2025 width=10327) (actual 
>time=24908.114..1416405.401 rows=100 loops=1)
>                           Join Filter: (vm_dynamic.run_on_vds = vds_static.vds_id)
>                           Rows Removed by Join Filter: 40300
>                           ->  Nested Loop Left Join  (cost=0.28..953592102.47 rows=2025 width=10288) (a
>ctual time=24907.820..1416397.215 rows=100 loops=1)
>                                 Join Filter: (vm_static.quota_id = quota.id)
>                                 Rows Removed by Join Filter: 398
>                                 ->  Nested Loop Left Join  (cost=0.28..953591949.53 rows=2025 width=101
>40) (actual time=24907.791..1416396.640 rows=100 loops=1)
>                                       Join Filter: ((vm_static.cluster_id = cluster.cluster_id) AND (cl
>uster.storage_pool_id = storage_pool.id))
>                                       Rows Removed by Join Filter: 400
>                                       ->  Nested Loop  (cost=0.28..953591771.28 rows=2025 width=10143) 
>(actual time=24907.759..1416396.019 rows=100 loops=1)
>                                             Join Filter: (vm_static.vm_guid = vm_statistics.vm_guid)
>                                             Rows Removed by Join Filter: 407446
>                                             ->  Nested Loop  (cost=0.28..953304118.03 rows=2025 width=9
>990) (actual time=24902.208..1416141.296 rows=100 loops=1)
>                                                   Join Filter: (vm_static.vmt_guid = vm_templates.vm_gu
>id)
>                                                   Rows Removed by Join Filter: 405899
>                                                   ->  Nested Loop  (cost=0.28..953179243.28 rows=2025 w
>idth=9973) (actual time=24902.122..1416071.859 rows=100 loops=1)
>                                                         Join Filter: (vm_static.vm_guid = vm_dynamic.vm
>_guid)
>                                                         Rows Removed by Join Filter: 404197
>                                                         ->  Nested Loop  (cost=0.28..951897107.91 rows=
>2036 width=7201) (actual time=24900.625..1415906.060 rows=100 loops=1)
>                                                               Join Filter: (vm_static.cluster_id = clus
>ter.cluster_id)
>                                                               Rows Removed by Join Filter: 398
>                                                               ->  Index Scan using idx_vm_static_vm_nam
>e on vm_static  (cost=0.28..951896953.92 rows=2039 width=6609) (actual time=24900.585..1415905.130 rows=
>100 loops=1)
>                                                                     Filter: (((entity_type)::text = 'VM
>'::text) AND (SubPlan 2))
>                                                                     Rows Removed by Filter: 2492
>                                                                     SubPlan 2
>                                                                       ->  HashAggregate  (cost=232169.2
>4..232169.89 rows=65 width=16) (actual time=550.673..550.689 rows=140 loops=2571)
>                                                                             Group Key: vm_static_1.vm_g
>uid
>                                                                             ->  Result  (cost=219873.82
>..232169.07 rows=65 width=16) (actual time=519.224..549.402 rows=573 loops=2571)
>                                                                                   One-Time Filter: (((v
>m_static.vm_name)::text ~~ '%L1%'::text) AND ((vm_static.vm_name)::text ~~ '%L1%'::text))
>                                                                                   ->  Hash Left Join  (
>cost=219873.82..232169.07 rows=65 width=16) (actual time=522.268..552.555 rows=576 loops=2556)
>                                                                                         Hash Cond: (vm_
>dynamic_1.run_on_vds = vds_static_1.vds_id)
>                                                                                         Filter: (((clus
>ter_1.name)::text ~~ '%b01-h13%%'::text) OR ((vds_dynamic.cpu_model)::text ~~ '%b01-h13%%'::text) OR (vd
>s_static_1.free_text_comment ~~ '%b01-h13%%'::text) OR ((vds_static_1.host_name)::text ~~ '%b01-h13%%'::
>text) OR ((vds_dynamic.software_version)::text ~~ '%b01-h13%%'::text) OR ((storage_pool_1.name)::text ~~
> '%b01-h13%%'::text) OR ((tags.tag_name)::text ~~ '%b01-h13%%'::text) OR ((vds_static_1.vds_name)::text 
>~~ '%b01-h13%%'::text))
>                                                                                         Rows Removed by
> Filter: 14613
>                                                                                         ->  Unique  (co
>st=218167.68..228355.36 rows=28497 width=11417) (actual time=522.092..534.655 rows=4078 loops=2556)
>                                                                                               ->  Sort 
> (cost=218167.68..218238.92 rows=28497 width=11417) (actual time=522.090..524.183 rows=4109 loops=2556)
>                                                                                                     Sor
>t Key: vm_static_1.vm_name, vm_static_1.lease_sd_id, vm_dynamic_1.lease_info, vm_static_1.mem_size_mb, v
>m_static_1.max_memory_size_mb, vm_static_1.num_of_io_threads, vm_static_1.nice_level, vm_static_1.cpu_sh
>ares, vm_static_1.vmt_guid, vm_static_1.os, vm_static_1.description, vm_static_1.free_text_comment, vm_s
>tatic_1.cluster_id, vm_static_1.creation_date, vm_static_1.auto_startup, vm_static_1.is_stateless, vm_st
>atic_1.is_smartcard_enabled, vm_static_1.is_delete_protected, vm_static_1.sso_method, (fn_get_dedicated_
>hosts_ids_by_vm_id(vm_static_1.vm_guid)), vm_static_1.fail_back, vm_static_1.default_boot_sequence, vm_s
>tatic_1.vm_type, cluster_2.name, storage_pool_2.id, storage_pool_2.name, vm_templates_1.vm_name, vm_dyna
>mic_1.status, vm_dynamic_1.vm_ip, (fn_get_comparable_ip_list(vm_dynamic_1.vm_ip)), vm_dynamic_1.vm_host,
> vm_dynamic_1.last_start_time, vm_dynamic_1.boot_time, vm_dynamic_1.downtime, vm_dynamic_1.last_stop_tim
>e, vm_dynamic_1.guest_cur_user_name, vm_dynamic_1.console_cur_user_name, vm_dynamic_1.console_user_id, v
>m_dynamic_1.guest_os, vm_dynamic_1.run_on_vds, vm_dynamic_1.migrating_to_vds, vm_dynamic_1.app_list, vm_
>pools_1.vm_pool_name, vm_pool_map_1.vm_pool_id, vm_static_1.vm_guid, vm_static_1.num_of_monitors, vm_sta
>tic_1.single_qxl_pci, vm_static_1.allow_console_reconnect, vm_static_1.is_initialized, vm_static_1.num_o
>f_sockets, vm_static_1.cpu_per_socket, vm_static_1.threads_per_cpu, vm_static_1.usb_policy, vm_dynamic_1
>.acpi_enable, vm_dynamic_1.session, (fn_get_num_of_vcpus(vm_static_1.*)), vm_dynamic_1.boot_sequence, vm
>_dynamic_1.utc_diff, vm_dynamic_1.client_ip, vm_dynamic_1.guest_requested_memory, vm_static_1.time_zone,
> vm_statistics_1.cpu_user, vm_statistics_1.cpu_sys, vm_statistics_1.elapsed_time, vm_statistics_1.usage_
>network_percent, vm_statistics_1.disks_usage, vm_statistics_1.usage_mem_percent, vm_statistics_1.usage_c
>pu_percent, vds_static_2.vds_name, cluster_2.cpu_name, tags_1.tag_name, tags_1.tag_id, vm_static_1.defau
>lt_display_type, vm_static_1.priority, cluster_2.compatibility_version, vm_static_1.initrd_url, vm_stati
>c_1.kernel_url, vm_static_1.kernel_params, vm_dynamic_1.pause_status, vm_dynamic_1.exit_status, vm_dynam
>ic_1.exit_message, vm_static_1.min_allocated_mem, image_storage_domain_map.storage_domain_id, vm_static_
>1.quota_id, quota_1.quota_name, vm_static_1.tunnel_migration, vm_static_1.vnc_keyboard_layout, vm_static
>_1.is_run_and_pause, vm_static_1.created_by_user_id, vm_dynamic_1.vm_fqdn, vm_dynamic_1.cpu_name, vm_dyn
>amic_1.emulated_machine, vm_static_1.custom_emulated_machine, vm_static_1.custom_cpu_name, vm_pools_1.sp
>ice_proxy, cluster_2.spice_proxy, vm_static_1.instance_type_id, vm_static_1.image_type_id, cluster_2.arc
>hitecture, vm_static_1.original_template_id, vm_static_1.original_template_name, vm_static_1.migration_d
>owntime, vm_static_1.template_version_number, vm_dynamic_1.current_cd, vm_dynamic_1.reason, vm_static_1.
>serial_number_policy, vm_static_1.custom_serial_number, vm_dynamic_1.exit_reason, vm_static_1.is_boot_me
>nu_enabled, vm_dynamic_1.guest_cpu_count, vm_snapshots_1.next_run_config_exists, vm_snapshots_1.is_previ
>ewing_snapshot, vm_static_1.numatune_mode, vm_static_1.is_spice_file_transfer_enabled, vm_static_1.is_sp
>ice_copy_paste_enabled, vm_static_1.cpu_profile_id, vm_static_1.is_auto_converge, vm_static_1.is_migrate
>_compressed, vm_dynamic_1.spice_port, vm_dynamic_1.spice_tls_port, vm_dynamic_1.spice_ip, vm_dynamic_1.v
>nc_port, vm_dynamic_1.vnc_ip, vm_dynamic_1.guest_agent_status, vm_statistics_1.guest_mem_buffered, vm_st
>atistics_1.guest_mem_cached, vm_static_1.small_icon_id, vm_static_1.large_icon_id, vm_static_1.migration
>_policy_id, vm_static_1.console_disconnect_action, vm_static_1.resume_behavior, vm_dynamic_1.guest_timez
>one_offset, vm_dynamic_1.guest_timezone_name, vm_dynamic_1.guestos_arch, vm_dynamic_1.guestos_codename, 
>vm_dynamic_1.guestos_distribution, vm_dynamic_1.guestos_kernel_version, vm_dynamic_1.guestos_type, vm_dy
>namic_1.guestos_version, vm_static_1.custom_compatibility_version, vm_dynamic_1.guest_containers, image_
>details_1.has_illegal_images
>                                                                                                     Sor
>t Method: external merge  Disk: 4872kB
>                                                                                                     -> 
> Hash Left Join  (cost=6673.80..76675.11 rows=28497 width=11417) (actual time=0.240..454.136 rows=4109 l
>oops=2556)
>                                                                                                        
>   Hash Cond: (images_1.image_guid = image_storage_domain_map.image_id)
>                                                                                                        
>   ->  Hash Left Join  (cost=6513.87..54715.01 rows=28490 width=11417) (actual time=0.108..160.380 rows=
>4109 loops=2556)
>                                                                                                        
>         Hash Cond: (vm_device_1.device_id = images_1.image_group_id)
>                                                                                                        
>         Filter: ((((vm_device_1.device)::text = 'disk'::text) AND ((images_1.active IS NULL) OR images_
>1.active)) OR (NOT (hashed SubPlan 1)))
>                                                                                                        
>         Rows Removed by Filter: 49321
>                                                                                                        
>         ->  Hash Left Join  (cost=4075.57..51619.35 rows=52945 width=11424) (actual time=0.087..131.587
> rows=53408 loops=2556)
>                                                                                                        
>               Hash Cond: (vm_static_1.vm_guid = vm_device_1.vm_id)
>                                                                                                        
>               ->  Hash Left Join  (cost=1311.01..48198.84 rows=4048 width=11401) (actual time=0.060..95
>.757 rows=4077 loops=2556)
>                                                                                                        
>                     Hash Cond: (vm_static_1.vm_guid = image_details_1.vm_id)
>                                                                                                        
>                     ->  Hash Left Join  (cost=789.00..45556.81 rows=4048 width=11400) (actual time=0.05
>2..91.759 rows=4077 loops=2556)
>                                                                                                        
>                           Hash Cond: (vm_static_1.vm_guid = vm_snapshots_1.vm_id)
>                                                                                                        
>                           ->  Hash Left Join  (cost=467.06..43125.11 rows=4048 width=11398) (actual tim
>e=0.046..87.707 rows=4077 loops=2556)
>                                                                                                        
>                                 Hash Cond: (vm_static_1.vm_guid = vm_pool_map_1.vm_guid)
>                                                                                                        
>                                 ->  Hash Left Join  (cost=464.35..43107.09 rows=4048 width=10350) (actu
>al time=0.044..84.576 rows=4077 loops=2556)
>                                                                                                        
>                                       Hash Cond: (vm_static_1.vm_guid = tags_vm_map.vm_id)
>                                                                                                        
>                                       ->  Hash Left Join  (cost=412.35..42725.10 rows=4048 width=10216)
> (actual time=0.043..81.530 rows=4077 loops=2556)
>                                                                                                        
>                                             Hash Cond: (vm_dynamic_1.run_on_vds = vds_static_2.vds_id)
>                                                                                                        
>                                             ->  Hash Left Join  (cost=382.28..42639.45 rows=4048 width=
>10177) (actual time=0.041..77.981 rows=4077 loops=2556)
>                                                                                                        
>                                                   Hash Cond: (vm_static_1.quota_id = quota_1.id)
>                                                                                                        
>                                                   ->  Hash Left Join  (cost=381.17..42582.96 rows=4048 
>width=10029) (actual time=0.039..74.677 rows=4077 loops=2556)
>                                                                                                        
>                                                         Hash Cond: (cluster_2.storage_pool_id = storage
>_pool_2.id)
>                                                                                                        
>                                                         Join Filter: (vm_static_1.cluster_id = cluster_
>2.cluster_id)
>                                                                                                        
>                                                         ->  Hash Join  (cost=380.05..42516.07 rows=4048
> width=10036) (actual time=0.037..70.731 rows=4077 loops=2556)
>                                                                                                        
>                                                               Hash Cond: (vm_static_1.cluster_id = clus
>ter_2.cluster_id)
>                                                                                                        
>                                                               ->  Hash Join  (cost=378.94..42459.27 row
>s=4054 width=9446) (actual time=0.036..67.100 rows=4077 loops=2556)
>                                                                                                        
>                                                                     Hash Cond: (vm_static_1.vmt_guid = 
>vm_templates_1.vm_guid)
>                                                                                                        
>                                                                     ->  Nested Loop  (cost=0.69..42025.
>28 rows=4054 width=9429) (actual time=0.031..63.531 rows=4077 loops=2556)
>                                                                                                        
>                                                                           Join Filter: (vm_dynamic_1.vm
>_guid = vm_static_1.vm_guid)
>                                                                                                        
>                                                                           ->  Nested Loop  (cost=0.41..
>40075.77 rows=4077 width=2889) (actual time=0.019..37.867 rows=4077 loops=2556)
>                                                                                                        
>                                                                                 ->  Seq Scan on vm_dyna
>mic vm_dynamic_1  (cost=0.00..578.77 rows=4077 width=2704) (actual time=0.005..2.467 rows=4077 loops=255
>6)
>                                                                                                        
>                                                                                 ->  Index Scan using pk
>_vm_statistics on vm_statistics vm_statistics_1  (cost=0.41..9.68 rows=1 width=185) (actual time=0.005..
>0.006 rows=1 loops=10420812)
>                                                                                                        
>                                                                                       Index Cond: (vm_g
>uid = vm_dynamic_1.vm_guid)
>                                                                                                        
>                                                                           ->  Index Scan using pk_vm_st
>atic on vm_static vm_static_1  (cost=0.28..0.47 rows=1 width=6572) (actual time=0.004..0.005 rows=1 loop
>s=10420812)
>                                                                                                        
>                                                                                 Index Cond: (vm_guid = 
>vm_statistics_1.vm_guid)
>                                                                                                        
>                                                                                 Filter: ((entity_type):
>:text = 'VM'::text)
>                                                                                                        
>                                                                     ->  Hash  (cost=327.00..327.00 rows
>=4100 width=33) (actual time=6.142..6.142 rows=4100 loops=1)
>                                                                                                        
>                                                                           Buckets: 8192  Batches: 1  Me
>mory Usage: 327kB
>                                                                                                        
>                                                                           ->  Seq Scan on vm_static vm_
>templates_1  (cost=0.00..327.00 rows=4100 width=33) (actual time=0.016..4.020 rows=4100 loops=1)
>                                                                                                        
>                                                               ->  Hash  (cost=1.05..1.05 rows=5 width=5
>90) (actual time=0.031..0.031 rows=5 loops=1)
>                                                                                                        
>                                                                     Buckets: 1024  Batches: 1  Memory U
>sage: 9kB
>                                                                                                        
>                                                                     ->  Seq Scan on cluster cluster_2  
>(cost=0.00..1.05 rows=5 width=590) (actual time=0.009..0.013 rows=5 loops=1)
>                                                                                                        
>                                                         ->  Hash  (cost=1.05..1.05 rows=5 width=25) (ac
>tual time=0.027..0.027 rows=5 loops=1)
>                                                                                                        
>                                                               Buckets: 1024  Batches: 1  Memory Usage: 
>9kB
>                                                                                                        
>                                                               ->  Seq Scan on storage_pool storage_pool
>_2  (cost=0.00..1.05 rows=5 width=25) (actual time=0.005..0.008 rows=5 loops=1)
>                                                                                                        
>                                                   ->  Hash  (cost=1.05..1.05 rows=5 width=164) (actual 
>time=0.020..0.020 rows=5 loops=1)
>                                                                                                        
>                                                         Buckets: 1024  Batches: 1  Memory Usage: 9kB
>                                                                                                        
>                                                         ->  Seq Scan on quota quota_1  (cost=0.00..1.05
> rows=5 width=164) (actual time=0.006..0.008 rows=5 loops=1)
>                                                                                                        
>                                             ->  Hash  (cost=25.03..25.03 rows=403 width=55) (actual tim
>e=0.570..0.570 rows=404 loops=1)
>                                                                                                        
>                                                   Buckets: 1024  Batches: 1  Memory Usage: 43kB
>                                                                                                        
>                                                   ->  Seq Scan on vds_static vds_static_2  (cost=0.00..
>25.03 rows=403 width=55) (actual time=0.012..0.354 rows=404 loops=1)
>                                                                                                        
>                                       ->  Hash  (cost=44.23..44.23 rows=622 width=150) (actual time=0.0
>02..0.002 rows=0 loops=1)
>                                                                                                        
>                                             Buckets: 1024  Batches: 1  Memory Usage: 8kB
>                                                                                                        
>                                             ->  Hash Join  (cost=12.47..44.23 rows=622 width=150) (actu
>al time=0.001..0.001 rows=0 loops=1)
>                                                                                                        
>                                                   Hash Cond: (tags_vm_map.tag_id = tags_1.tag_id)
>                                                                                                        
>                                                   ->  Seq Scan on tags_vm_map  (cost=0.00..21.30 rows=1
>130 width=32) (actual time=0.000..0.000 rows=0 loops=1)
>                                                                                                        
>                                                   ->  Hash  (cost=11.10..11.10 rows=110 width=134) (nev
>er executed)
>                                                                                                        
>                                                         ->  Seq Scan on tags tags_1  (cost=0.00..11.10 
>rows=110 width=134) (never executed)
>                                                                                                        
>                                 ->  Hash  (cost=2.53..2.53 rows=14 width=1064) (actual time=0.015..0.01
>5 rows=0 loops=1)
>                                                                                                        
>                                       Buckets: 1024  Batches: 1  Memory Usage: 8kB
>                                                                                                        
>                                       ->  Hash Join  (cost=1.02..2.53 rows=14 width=1064) (actual time=
>0.014..0.014 rows=0 loops=1)
>                                                                                                        
>                                             Hash Cond: (vm_pool_map_1.vm_pool_id = vm_pools_1.vm_pool_i
>d)
>                                                                                                        
>                                             ->  Seq Scan on vm_pool_map vm_pool_map_1  (cost=0.00..1.27
> rows=27 width=32) (actual time=0.014..0.014 rows=0 loops=1)
>                                                                                                        
>                                             ->  Hash  (cost=1.01..1.01 rows=1 width=1048) (never execut
>ed)
>                                                                                                        
>                                                   ->  Seq Scan on vm_pools vm_pools_1  (cost=0.00..1.01
> rows=1 width=1048) (never executed)
>                                                                                                        
>                           ->  Hash  (cost=271.01..271.01 rows=4074 width=18) (actual time=11.227..11.22
>7 rows=4077 loops=1)
>                                                                                                        
>                                 Buckets: 4096  Batches: 1  Memory Usage: 232kB
>                                                                                                        
>                                 ->  Subquery Scan on vm_snapshots_1  (cost=169.16..271.01 rows=4074 wid
>th=18) (actual time=6.276..9.447 rows=4077 loops=1)
>                                                                                                        
>                                       ->  HashAggregate  (cost=169.16..230.27 rows=4074 width=23) (actu
>al time=6.274..8.407 rows=4077 loops=1)
>                                                                                                        
>                                             Group Key: snapshots_1.vm_id
>                                                                                                        
>                                             ->  Seq Scan on snapshots snapshots_1  (cost=0.00..117.96 r
>ows=4096 width=23) (actual time=0.011..1.924 rows=4099 loops=1)
>                                                                                                        
>                     ->  Hash  (cost=470.91..470.91 rows=4088 width=17) (actual time=17.348..17.348 rows
>=4091 loops=1)
>                                                                                                        
>                           Buckets: 4096  Batches: 1  Memory Usage: 228kB
>                                                                                                                                   ->  Subquery Scan on image_details_1  (cost=378.93..470.91 rows=4088 width=17) (
>actual time=12.802..15.694 rows=4091 loops=1)
>                                                                                                                                         ->  HashAggregate  (cost=378.93..430.03 rows=4088 width=21) (actual time=1
>2.800..14.680 rows=4091 loops=1)
>                                                                                                                                               Group Key: disk_vm_element_1.vm_id
>                                                                                                                                               Filter: bool_or(images_2.active)
>                                                                                                                                               ->  Hash Join  (cost=137.23..337.74 rows=4119 width=21) (actual time
>=3.422..8.845 rows=4126 loops=1)
>                                                                                                                                                     Hash Cond: (images_2.image_group_id = disk_vm_element_1.disk_i
>d)
>                                                                                                                                                     ->  Seq Scan on images images_2  (cost=0.00..143.69 rows=4169 
>width=21) (actual time=0.005..1.555 rows=4178 loops=1)
>                                                                                                                                                     ->  Hash  (cost=85.99..85.99 rows=4099 width=32) (actual time=
>3.320..3.320 rows=4104 loops=1)
>                                                                                                                                                           Buckets: 8192  Batches: 1  Memory Usage: 321kB
>                                                                                                                                                           ->  Seq Scan on disk_vm_element disk_vm_element_1  (cost
>=0.00..85.99 rows=4099 width=32) (actual time=0.017..1.440 rows=4104 loops=1)
>                                                                                                                       ->  Hash  (cost=2094.25..2094.25 rows=53625 width=39) (actual time=63.616..63.616 rows=53625
> loops=1)
>                                                                                                                             Buckets: 65536  Batches: 1  Memory Usage: 4234kB
>                                                                                                                             ->  Seq Scan on vm_device vm_device_1  (cost=0.00..2094.25 rows=53625 width=39) (actua
>l time=0.006..34.196 rows=53625 loops=1)
>                                                                                                                 ->  Hash  (cost=143.69..143.69 rows=4169 width=33) (actual time=3.838..3.838 rows=4177 loops=1)
>                                                                                                                       Buckets: 8192  Batches: 1  Memory Usage: 330kB
>                                                                                                                       ->  Seq Scan on images images_1  (cost=0.00..143.69 rows=4169 width=33) (actual time=0.006..
>1.864 rows=4178 loops=1)
>                                                                                                                 SubPlan 1
>                                                                                                                   ->  HashAggregate  (cost=2238.58..2241.72 rows=314 width=16) (actual time=23.381..24.467 rows=40
>91 loops=1)
>                                                                                                                         Group Key: vm_device.vm_id
>                                                                                                                         ->  Seq Scan on vm_device  (cost=0.00..2228.31 rows=4106 width=16) (actual time=0.015..20.
>538 rows=4104 loops=1)
>                                                                                                                               Filter: ((device)::text = 'disk'::text)
>                                                                                                                               Rows Removed by Filter: 49521
>                                                                                                           ->  Hash  (cost=107.19..107.19 rows=4219 width=32) (actual time=3.571..3.571 rows=4178 loops=1)
>                                                                                                                 Buckets: 8192  Batches: 1  Memory Usage: 326kB
>                                                                                                                 ->  Seq Scan on image_storage_domain_map  (cost=0.00..107.19 rows=4219 width=32) (actual time=0.01
>6..1.595 rows=4178 loops=1)
>                                                                                         ->  Hash  (cost=1696.24..1696.24 rows=792 width=246) (actual time=4.844..4.844 rows=486 loops=1)
>                                                                                               Buckets: 1024  Batches: 1  Memory Usage: 94kB
>                                                                                               ->  Hash Left Join  (cost=1632.94..1696.24 rows=792 width=246) (actual time=4.289..4.570 rows=486 loops=1)
>                                                                                                     Hash Cond: (storage_pool_1.id = storage_pool_iso_map.storage_pool_id)
>                                                                                                     ->  Hash Join  (cost=1631.81..1683.06 rows=660 width=262) (actual time=4.249..4.412 rows=404 loops=1)
>                                                                                                           Hash Cond: (vds_static_1.vds_id = vds_dynamic.vds_id)
>                                                                                                           ->  Hash Right Join  (cost=68.99..111.16 rows=660 width=224) (actual time=0.897..0.964 rows=404 loops=1)
>                                                                                                                 Hash Cond: (tags_vds_map.vds_id = vds_static_1.vds_id)
>                                                                                                                 ->  Hash Join  (cost=12.47..45.58 rows=660 width=134) (actual time=0.006..0.006 rows=0 loops=1)
>                                                                                                                       Hash Cond: (tags_vds_map.tag_id = tags.tag_id)
>                                                                                                                       ->  Seq Scan on tags_vds_map  (cost=0.00..22.00 rows=1200 width=32) (actual time=0.000..0.00
>0 rows=0 loops=1)
>                                                                                                                       ->  Hash  (cost=11.10..11.10 rows=110 width=134) (never executed)
>                                                                                                                             ->  Seq Scan on tags  (cost=0.00..11.10 rows=110 width=134) (never executed)
>                                                                                                                 ->  Hash  (cost=51.47..51.47 rows=403 width=106) (actual time=0.879..0.879 rows=404 loops=1)
>                                                                                                                       Buckets: 1024  Batches: 1  Memory Usage: 65kB
>                                                                                                                       ->  Hash Left Join  (cost=15.36..51.47 rows=403 width=106) (actual time=0.297..0.690 rows=40
>4 loops=1)
>                                                                                                                             Hash Cond: (vds_static_1.vds_id = vds_spm_id_map.vds_id)
>                                                                                                                             ->  Hash Join  (cost=2.29..32.87 rows=403 width=106) (actual time=0.104..0.390 rows=40
>4 loops=1)
>                                                                                                                                   Hash Cond: (vds_static_1.cluster_id = cluster_1.cluster_id)
>                                                                                                                                   ->  Seq Scan on vds_static vds_static_1  (cost=0.00..25.03 rows=403 width=88) (a
>ctual time=0.026..0.092 rows=404 loops=1)
>                                                                                                                                   ->  Hash  (cost=2.23..2.23 rows=5 width=50) (actual time=0.058..0.058 rows=5 loo
>ps=1)
>                                                                                                                                         Buckets: 1024  Batches: 1  Memory Usage: 9kB
>                                                                                                                                         ->  Hash Left Join  (cost=1.11..2.23 rows=5 width=50) (actual time=0.046..
>0.048 rows=5 loops=1)
>                                                                                                                                               Hash Cond: (cluster_1.storage_pool_id = storage_pool_1.id)
>                                                                                                                                               ->  Seq Scan on cluster cluster_1  (cost=0.00..1.05 rows=5 width=41)
> (actual time=0.007..0.009 rows=5 loops=1)
>                                                                                                                                               ->  Hash  (cost=1.05..1.05 rows=5 width=25) (actual time=0.016..0.01
>6 rows=5 loops=1)
>                                                                                                                                                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
>                                                                                                                                                     ->  Seq Scan on storage_pool storage_pool_1  (cost=0.00..1.05 
>rows=5 width=25) (actual time=0.005..0.006 rows=5 loops=1)
>                                                                                                                             ->  Hash  (cost=8.03..8.03 rows=403 width=16) (actual time=0.174..0.174 rows=404 loops
>=1)
>                                                                                                                                   Buckets: 1024  Batches: 1  Memory Usage: 27kB
>                                                                                                                                   ->  Seq Scan on vds_spm_id_map  (cost=0.00..8.03 rows=403 width=16) (actual time
>=0.014..0.078 rows=404 loops=1)
>                                                                                                           ->  Hash  (cost=1557.77..1557.77 rows=404 width=70) (actual time=3.314..3.314 rows=404 loops=1)
>                                                                                                                 Buckets: 1024  Batches: 1  Memory Usage: 49kB
>                                                                                                                 ->  Nested Loop  (cost=0.41..1557.77 rows=404 width=70) (actual time=0.083..3.096 rows=404 loops=1
>)
>                                                                                                                       ->  Seq Scan on vds_dynamic  (cost=0.00..303.04 rows=404 width=54) (actual time=0.010..1.036
> rows=404 loops=1)
>                                                                                                                       ->  Index Only Scan using pk_vds_statistics on vds_statistics  (cost=0.41..3.10 rows=1 width
>=16) (actual time=0.003..0.004 rows=1 loops=404)
>                                                                                                                             Index Cond: (vds_id = vds_dynamic.vds_id)
>                                                                                                                             Heap Fetches: 0
>                                                                                                     ->  Hash  (cost=1.06..1.06 rows=6 width=16) (actual time=0.019..0.019 rows=7 loops=1)
>                                                                                                           Buckets: 1024  Batches: 1  Memory Usage: 9kB
>                                                                                                           ->  Seq Scan on storage_pool_iso_map  (cost=0.00..1.06 rows=6 width=16) (actual time=0.009..0.010 rows=7
> loops=1)
>                                                               ->  Materialize  (cost=0.00..1.07 rows=5 width=592) (actual time=0.001..0.002 rows=5 loops=100)
>                                                                     ->  Seq Scan on cluster  (cost=0.00..1.05 rows=5 width=592) (actual time=0.015..0.020 rows=5 loops=1)
>                                                         ->  Seq Scan on vm_dynamic  (cost=0.00..578.77 rows=4077 width=2772) (actual time=0.006..0.938 rows=4043 loops=100)
>                                                   ->  Materialize  (cost=0.00..347.50 rows=4100 width=33) (actual time=0.001..0.242 rows=4060 loops=100)
>                                                         ->  Seq Scan on vm_static vm_templates  (cost=0.00..327.00 rows=4100 width=33) (actual time=0.004..1.327 rows=4100 loops=1)
>                                             ->  Materialize  (cost=0.00..50140.30 rows=7820 width=185) (actual time=0.002..1.767 rows=4075 loops=100)
>                                                   ->  Seq Scan on vm_statistics  (cost=0.00..50101.20 rows=7820 width=185) (actual time=0.130..151.792 rows=4077 loops=1)
>                                       ->  Materialize  (cost=0.00..1.07 rows=5 width=29) (actual time=0.000..0.001 rows=5 loops=100)
>                                             ->  Seq Scan on storage_pool  (cost=0.00..1.05 rows=5 width=29) (actual time=0.007..0.007 rows=5 loops=1)
>                                 ->  Materialize  (cost=0.00..1.07 rows=5 width=164) (actual time=0.000..0.001 rows=5 loops=100)
>                                       ->  Seq Scan on quota  (cost=0.00..1.05 rows=5 width=164) (actual time=0.006..0.007 rows=5 loops=1)
>                           ->  Materialize  (cost=0.00..27.05 rows=403 width=55) (actual time=0.001..0.028 rows=404 loops=100)
>                                 ->  Seq Scan on vds_static  (cost=0.00..25.03 rows=403 width=55) (actual time=0.011..0.112 rows=404 loops=1)
>                     ->  Nested Loop  (cost=0.39..0.89 rows=1 width=1064) (actual time=0.011..0.011 rows=0 loops=100)
>                           ->  Index Scan using pk_vm_pool_map on vm_pool_map  (cost=0.26..0.29 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=100)
>                                 Index Cond: (vm_static.vm_guid = vm_guid)
>                           ->  Index Scan using pk_vm_pools on vm_pools  (cost=0.12..0.59 rows=1 width=1048) (never executed)
>                                 Index Cond: (vm_pool_id = vm_pool_map.vm_pool_id)
>               ->  Materialize  (cost=169.16..291.38 rows=4074 width=18) (actual time=0.030..0.271 rows=4065 loops=100)
>                     ->  Subquery Scan on vm_snapshots  (cost=169.16..271.01 rows=4074 width=18) (actual time=2.880..4.404 rows=4077 loops=1)
>                           ->  HashAggregate  (cost=169.16..230.27 rows=4074 width=23) (actual time=2.878..3.941 rows=4077 loops=1)
>                                 Group Key: snapshots.vm_id
>                                 ->  Seq Scan on snapshots  (cost=0.00..117.96 rows=4096 width=23) (actual time=0.006..0.711 rows=4099 loops=1)
>         ->  Materialize  (cost=378.93..491.35 rows=4088 width=17) (actual time=0.059..0.307 rows=4079 loops=100)
>               ->  Subquery Scan on image_details  (cost=378.93..470.91 rows=4088 width=17) (actual time=5.765..7.181 rows=4091 loops=1)
>                     ->  HashAggregate  (cost=378.93..430.03 rows=4088 width=21) (actual time=5.764..6.726 rows=4091 loops=1)
>                           Group Key: disk_vm_element.vm_id
>                           Filter: bool_or(images.active)
>                           ->  Hash Join  (cost=137.23..337.74 rows=4119 width=21) (actual time=1.548..3.810 rows=4126 loops=1)
>                                 Hash Cond: (images.image_group_id = disk_vm_element.disk_id)
>                                 ->  Seq Scan on images  (cost=0.00..143.69 rows=4169 width=21) (actual time=0.006..0.509 rows=4178 loops=1)
>                                 ->  Hash  (cost=85.99..85.99 rows=4099 width=32) (actual time=1.465..1.465 rows=4104 loops=1)
>                                       Buckets: 8192  Batches: 1  Memory Usage: 321kB
>                                       ->  Seq Scan on disk_vm_element  (cost=0.00..85.99 rows=4099 width=32) (actual time=0.005..0.562 rows=4104 loops=1)
> Planning time: 131.204 ms
> Execution time: 1416574.790 ms
>

Actions: View

Attachments on bug 1581677: 1448664