Looks like there are two queries that are extremely slow for some reason,
could you please run the following commands in the psql console:
EXPLAIN ANALYZE SELECT DISTINCT `hosts`.`id` FROM `hosts` LEFT OUTER JOIN
`host_status` ON `host_status`.`host_id` = `hosts`.`id` LEFT OUTER JOIN
`compute_resources` ON `compute_resources`.`id` =
`hosts`.`compute_resource_id` LEFT OUTER JOIN `hostgroups` ON
`hostgroups`.`id` = `hosts`.`hostgroup_id` LEFT OUTER JOIN
`operatingsystems` ON `operatingsystems`.`id` =
`hosts`.`operatingsystem_id` LEFT OUTER JOIN `nics` ON `nics`.`host_id` =
`hosts`.`id` LEFT OUTER JOIN `tokens` ON `tokens`.`host_id` = `hosts`.`id`
LEFT OUTER JOIN `models` ON `models`.`id` = `hosts`.`model_id` LEFT OUTER
JOIN `nics` `primary_interfaces_hosts_join` ON
`primary_interfaces_hosts_join`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join`.`primary` = 1 LEFT OUTER JOIN `domains` ON
`domains`.`id` = `primary_interfaces_hosts_join`.`domain_id` LEFT OUTER
JOIN `realms` ON `realms`.`id` = `hosts`.`realm_id` LEFT OUTER JOIN
`environments` ON `environments`.`id` = `hosts`.`environment_id` LEFT OUTER
JOIN `architectures` ON `architectures`.`id` = `hosts`.`architecture_id`
LEFT OUTER JOIN `images` ON `images`.`id` = `hosts`.`image_id` LEFT OUTER
JOIN `nics` `primary_interfaces_hosts` ON
`primary_interfaces_hosts`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts`.`primary` = 1 LEFT OUTER JOIN `nics`
`primary_interfaces_hosts_join_2` ON
`primary_interfaces_hosts_join_2`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join_2`.`primary` = 1 LEFT OUTER JOIN `subnets`
ON `subnets`.`id` = `primary_interfaces_hosts_join_2`.`subnet_id` AND
`subnets`.`type` = 'Subnet::Ipv4' LEFT OUTER JOIN `nics`
`primary_interfaces_hosts_join_3` ON
`primary_interfaces_hosts_join_3`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join_3`.`primary` = 1 LEFT OUTER JOIN `subnets`
`subnet6s_hosts` ON `subnet6s_hosts`.`id` =
`primary_interfaces_hosts_join_3`.`subnet6_id` AND `subnet6s_hosts`.`type`
= 'Subnet::Ipv6' LEFT OUTER JOIN `nics` `provision_interfaces_hosts` ON
`provision_interfaces_hosts`.`host_id` = `hosts`.`id` AND
`provision_interfaces_hosts`.`provision` = 1 LEFT OUTER JOIN
`discovery_rules` ON `discovery_rules`.`id` = `hosts`.`discovery_rule_id`
LEFT OUTER JOIN `host_salt_modules` ON `host_salt_modules`.`host_id` =
`hosts`.`id` LEFT OUTER JOIN `salt_modules` ON `salt_modules`.`id` =
`host_salt_modules`.`salt_module_id` LEFT OUTER JOIN `salt_environments` ON
`salt_environments`.`id` = `hosts`.`salt_environment_id` LEFT OUTER JOIN
`smart_proxies` ON `smart_proxies`.`id` = `hosts`.`salt_proxy_id` WHERE
`hosts`.`type` IN ('Host::Managed') AND ((`hosts`.`name` LIKE '%test%' OR
`hosts`.`comment` LIKE '%test%' OR `models`.`name` LIKE '%test%' OR
`hostgroups`.`name` LIKE '%test%' OR `hostgroups`.`title` LIKE '%test%' OR
`hostgroups`.`title` LIKE '%test%' OR `domains`.`name` LIKE '%test%' OR
`realms`.`name` LIKE '%test%' OR `environments`.`name` LIKE '%test%' OR
`architectures`.`name` LIKE '%test%' OR `compute_resources`.`name` LIKE
'%test%' OR `images`.`name` LIKE '%test%' OR `operatingsystems`.`name` LIKE
'%test%' OR `operatingsystems`.`description` LIKE '%test%' OR
`operatingsystems`.`title` LIKE '%test%' OR `operatingsystems`.`major` LIKE
'%test%' OR `operatingsystems`.`minor` LIKE '%test%' OR `nics`.`ip` LIKE
'%test%' OR `nics`.`ip` LIKE '%test%' OR `nics`.`mac` LIKE '%test%' OR
`subnets`.`network` LIKE '%test%' OR `subnets`.`name` LIKE '%test%' OR
`subnets`.`network` LIKE '%test%' OR `subnets`.`name` LIKE '%test%' OR
`hosts`.`uuid` LIKE '%test%' OR `nics`.`mac` LIKE '%test%' OR
`operatingsystems`.`name` LIKE '%test%' OR `operatingsystems`.`description`
LIKE '%test%' OR `operatingsystems`.`title` LIKE '%test%' OR
`operatingsystems`.`major` LIKE '%test%' OR `operatingsystems`.`minor` LIKE
'%test%' OR `discovery_rules`.`name` LIKE '%test%' OR `salt_modules`.`name`
LIKE '%test%' OR `salt_environments`.`name` LIKE '%test%' OR
`smart_proxies`.`name` LIKE '%test%')) ORDER BY `hosts`.`name` ASC LIMIT 20
OFFSET 0;
EXPLAIN ANALYZE SELECT COUNT(DISTINCT `hosts`.`id`) FROM `hosts` LEFT OUTER
JOIN `host_status` ON `host_status`.`host_id` = `hosts`.`id` LEFT OUTER
JOIN `compute_resources` ON `compute_resources`.`id` =
`hosts`.`compute_resource_id` LEFT OUTER JOIN `hostgroups` ON
`hostgroups`.`id` = `hosts`.`hostgroup_id` LEFT OUTER JOIN
`operatingsystems` ON `operatingsystems`.`id` =
`hosts`.`operatingsystem_id` LEFT OUTER JOIN `nics` ON `nics`.`host_id` =
`hosts`.`id` LEFT OUTER JOIN `tokens` ON `tokens`.`host_id` = `hosts`.`id`
LEFT OUTER JOIN `models` ON `models`.`id` = `hosts`.`model_id` LEFT OUTER
JOIN `nics` `primary_interfaces_hosts_join` ON
`primary_interfaces_hosts_join`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join`.`primary` = 1 LEFT OUTER JOIN `domains` ON
`domains`.`id` = `primary_interfaces_hosts_join`.`domain_id` LEFT OUTER
JOIN `realms` ON `realms`.`id` = `hosts`.`realm_id` LEFT OUTER JOIN
`environments` ON `environments`.`id` = `hosts`.`environment_id` LEFT OUTER
JOIN `architectures` ON `architectures`.`id` = `hosts`.`architecture_id`
LEFT OUTER JOIN `images` ON `images`.`id` = `hosts`.`image_id` LEFT OUTER
JOIN `nics` `primary_interfaces_hosts` ON
`primary_interfaces_hosts`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts`.`primary` = 1 LEFT OUTER JOIN `nics`
`primary_interfaces_hosts_join_2` ON
`primary_interfaces_hosts_join_2`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join_2`.`primary` = 1 LEFT OUTER JOIN `subnets`
ON `subnets`.`id` = `primary_interfaces_hosts_join_2`.`subnet_id` AND
`subnets`.`type` = 'Subnet::Ipv4' LEFT OUTER JOIN `nics`
`primary_interfaces_hosts_join_3` ON
`primary_interfaces_hosts_join_3`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join_3`.`primary` = 1 LEFT OUTER JOIN `subnets`
`subnet6s_hosts` ON `subnet6s_hosts`.`id` =
`primary_interfaces_hosts_join_3`.`subnet6_id` AND `subnet6s_hosts`.`type`
= 'Subnet::Ipv6' LEFT OUTER JOIN `nics` `provision_interfaces_hosts` ON
`provision_interfaces_hosts`.`host_id` = `hosts`.`id` AND
`provision_interfaces_hosts`.`provision` = 1 LEFT OUTER JOIN
`discovery_rules` ON `discovery_rules`.`id` = `hosts`.`discovery_rule_id`
LEFT OUTER JOIN `host_salt_modules` ON `host_salt_modules`.`host_id` =
`hosts`.`id` LEFT OUTER JOIN `salt_modules` ON `salt_modules`.`id` =
`host_salt_modules`.`salt_module_id` LEFT OUTER JOIN `salt_environments` ON
`salt_environments`.`id` = `hosts`.`salt_environment_id` LEFT OUTER JOIN
`smart_proxies` ON `smart_proxies`.`id` = `hosts`.`salt_proxy_id` WHERE
`hosts`.`type` IN ('Host::Managed') AND ((`hosts`.`name` LIKE '%test%' OR
`hosts`.`comment` LIKE '%test%' OR `models`.`name` LIKE '%test%' OR
`hostgroups`.`name` LIKE '%test%' OR `hostgroups`.`title` LIKE '%test%' OR
`hostgroups`.`title` LIKE '%test%' OR `domains`.`name` LIKE '%test%' OR
`realms`.`name` LIKE '%test%' OR `environments`.`name` LIKE '%test%' OR
`architectures`.`name` LIKE '%test%' OR `compute_resources`.`name` LIKE
'%test%' OR `images`.`name` LIKE '%test%' OR `operatingsystems`.`name` LIKE
'%test%' OR `operatingsystems`.`description` LIKE '%test%' OR
`operatingsystems`.`title` LIKE '%test%' OR `operatingsystems`.`major` LIKE
'%test%' OR `operatingsystems`.`minor` LIKE '%test%' OR `nics`.`ip` LIKE
'%test%' OR `nics`.`ip` LIKE '%test%' OR `nics`.`mac` LIKE '%test%' OR
`subnets`.`network` LIKE '%test%' OR `subnets`.`name` LIKE '%test%' OR
`subnets`.`network` LIKE '%test%' OR `subnets`.`name` LIKE '%test%' OR
`hosts`.`uuid` LIKE '%test%' OR `nics`.`mac` LIKE '%test%' OR
`operatingsystems`.`name` LIKE '%test%' OR `operatingsystems`.`description`
LIKE '%test%' OR `operatingsystems`.`title` LIKE '%test%' OR
`operatingsystems`.`major` LIKE '%test%' OR `operatingsystems`.`minor` LIKE
'%test%' OR `discovery_rules`.`name` LIKE '%test%' OR `salt_modules`.`name`
LIKE '%test%' OR `salt_environments`.`name` LIKE '%test%' OR
`smart_proxies`.`name` LIKE '%test%'));
It may take a few minutes to run but it will show us what is taking the
time- there may be one table that is extremely painful to join on, or some
missing indexes.
On Tue, Nov 1, 2016 at 10:18 PM, 'Konstantin Orekhov' via Foreman users <
foreman-users@googlegroups.com> wrote:
> Here you go - https://gist.github.com/korekhov/
> 7ad0fddae6e330f1655305c626bb4808
>
> Please let me know if that's not enough and you need something else.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Foreman users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to foreman-users+unsubscr...@googlegroups.com.
> To post to this group, send email to foreman-users@googlegroups.com.
> Visit this group at https://groups.google.com/group/foreman-users.
> For more options, visit https://groups.google.com/d/optout.
>
--
Have a nice day,
Tomer Brisker
Red Hat Engineering
--
You received this message because you are subscribed to the Google Groups
"Foreman users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to foreman-users+unsubscr...@googlegroups.com.
To post to this group, send email to foreman-users@googlegroups.com.
Visit this group at https://groups.google.com/group/foreman-users.
For more options, visit https://groups.google.com/d/optout.
