Project

General

Profile

Bug #25154

search hosts performance is painfully slow

Added by Timo Goebel about 1 month ago. Updated about 1 month ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Search
Target version:
Team Backlog:
Fixed in Releases:
Found in Releases:

Description

When just searching for "infra-gitlab" the search is painfully slow (execution time 37.63s).

The generated SQL looks like this:

SELECT DISTINCT "hosts"."id", 
                "hosts"."name" AS alias_0 
FROM            "hosts" 
LEFT OUTER JOIN "reports" 
ON              "reports"."host_id" = "hosts"."id" 
AND             "reports"."type" IN ('ConfigReport') 
LEFT OUTER JOIN "models" 
ON              "models"."id" = "hosts"."model_id" 
LEFT OUTER JOIN "hostgroups" 
ON              "hostgroups"."id" = "hosts"."hostgroup_id" 
LEFT OUTER JOIN "nics" 
ON              "nics"."host_id" = "hosts"."id" 
AND             "nics"."primary" = 't' 
LEFT OUTER JOIN "domains" 
ON              "domains"."id" = "nics"."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 "compute_resources" 
ON              "compute_resources"."id" = "hosts"."compute_resource_id" 
LEFT OUTER JOIN "images" 
ON              "images"."id" = "hosts"."image_id" 
LEFT OUTER JOIN "operatingsystems" 
ON              "operatingsystems"."id" = "hosts"."operatingsystem_id" 
LEFT OUTER JOIN "nics" "primary_interfaces_hosts" 
ON              "primary_interfaces_hosts"."host_id" = "hosts"."id" 
AND             "primary_interfaces_hosts"."primary" = 't' 
LEFT OUTER JOIN "nics" "primary_interfaces_hosts_join" 
ON              "primary_interfaces_hosts_join"."host_id" = "hosts"."id" 
AND             "primary_interfaces_hosts_join"."primary" = 't' 
LEFT OUTER JOIN "subnets" 
ON              "subnets"."id" = "primary_interfaces_hosts_join"."subnet_id" 
AND             "subnets"."type" = 'Subnet::Ipv4' 
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" = 't' 
LEFT OUTER JOIN "subnets" "subnet6s_hosts" 
ON              "subnet6s_hosts"."id" = "primary_interfaces_hosts_join_2"."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" = 't' 
LEFT OUTER JOIN "discovery_rules" 
ON              "discovery_rules"."id" = "hosts"."discovery_rule_id" 
LEFT OUTER JOIN "katello_host_collection_hosts" 
ON              "katello_host_collection_hosts"."host_id" = "hosts"."id" 
LEFT OUTER JOIN "katello_host_collections" 
ON              "katello_host_collections"."id" = "katello_host_collection_hosts"."host_collection_id" 
LEFT OUTER JOIN "katello_content_facets" 
ON              "katello_content_facets"."host_id" = "hosts"."id" 
LEFT OUTER JOIN "katello_content_views" 
ON              "katello_content_views"."id" = "katello_content_facets"."content_view_id" 
LEFT OUTER JOIN "katello_content_facets" "content_facets_hosts_join" 
ON              "content_facets_hosts_join"."host_id" = "hosts"."id" 
LEFT OUTER JOIN "katello_environments" 
ON              "katello_environments"."id" = "content_facets_hosts_join"."lifecycle_environment_id" 
LEFT OUTER JOIN "katello_content_facets" "content_facets_hosts_join_2" 
ON              "content_facets_hosts_join_2"."host_id" = "hosts"."id" 
LEFT OUTER JOIN "smart_proxies" 
ON              "smart_proxies"."id" = "content_facets_hosts_join_2"."content_source_id" 
LEFT OUTER JOIN "monitoring_results" 
ON              "monitoring_results"."host_id" = "hosts"."id" 
LEFT OUTER JOIN "smart_proxies" "monitoring_proxies_hosts" 
ON              "monitoring_proxies_hosts"."id" = "hosts"."monitoring_proxy_id" 
LEFT OUTER JOIN "omaha_facets" 
ON              "omaha_facets"."host_id" = "hosts"."id" 
LEFT OUTER JOIN "vmware_facets" 
ON              "vmware_facets"."host_id" = "hosts"."id" 
LEFT OUTER JOIN "host_status" 
ON              "host_status"."host_id" = "hosts"."id" 
LEFT OUTER JOIN "tokens" 
ON              "tokens"."host_id" = "hosts"."id" 
WHERE           "hosts"."type" IN ('Host::Managed') 
AND             (( 
                                                "hosts"."name" ilike '%infra-gitlab%' 
                                OR              "hosts"."comment" ilike '%infra-gitlab%' 
                                OR              "reports"."origin" ilike '%infra-gitlab%' 
                                OR              "models"."name" ilike '%infra-gitlab%' 
                                OR              "hostgroups"."name" ilike '%infra-gitlab%' 
                                OR              "hostgroups"."title" ilike '%infra-gitlab%' 
                                OR              "domains"."name" ilike '%infra-gitlab%' 
                                OR              "realms"."name" ilike '%infra-gitlab%' 
                                OR              "environments"."name" ilike '%infra-gitlab%' 
                                OR              "architectures"."name" ilike '%infra-gitlab%' 
                                OR              "compute_resources"."name" ilike '%infra-gitlab%' 
                                OR              "images"."name" ilike '%infra-gitlab%' 
                                OR              "operatingsystems"."name" ilike '%infra-gitlab%' 
                                OR              "operatingsystems"."description" ilike '%infra-gitlab%' 
                                OR              "operatingsystems"."title" ilike '%infra-gitlab%' 
                                OR              "nics"."ip" ilike '%infra-gitlab%' 
                                OR              "subnets"."network" ilike '%infra-gitlab%' 
                                OR              "subnets"."name" ilike '%infra-gitlab%' 
                                OR              "subnets"."network" ilike '%infra-gitlab%' 
                                OR              "subnets"."name" ilike '%infra-gitlab%' 
                                OR              "hosts"."uuid" ilike '%infra-gitlab%' 
                                OR              "nics"."mac" ilike '%infra-gitlab%' 
                                OR              "discovery_rules"."name" ilike '%infra-gitlab%' 
                                OR              "katello_host_collections"."name" ilike '%infra-gitlab%' 
                                OR              "katello_content_views"."name" ilike '%infra-gitlab%' 
                                OR              "katello_environments"."name" ilike '%infra-gitlab%' 
                                OR              "smart_proxies"."name" ilike '%infra-gitlab%' 
                                OR              1=0 
                                OR              1=0 
                                OR              1=0)) 
ORDER BY        "hosts"."name" ASC limit '42' offset '0'

The joining on reports is the reason why this is slow.

scoped_search :relation => :last_report_object, :on => :origin

should be explicit only.


Related issues

Related to Foreman - Feature #22779: Calculate out of sync host via origin specific interval settingClosed2018-03-05

Associated revisions

Revision 02c7aa77 (diff)
Added by Timo Goebel about 1 month ago

fixes #25154 - do not join reports when searching

History

#1 Updated by Timo Goebel about 1 month ago

  • Related to Feature #22779: Calculate out of sync host via origin specific interval setting added

#2 Updated by The Foreman Bot about 1 month ago

  • Status changed from New to Ready For Testing
  • Pull request https://github.com/theforeman/foreman/pull/6126 added

#3 Updated by Tomer Brisker about 1 month ago

  • Fixed in Releases 1.20.0 added

#4 Updated by Timo Goebel about 1 month ago

  • Status changed from Ready For Testing to Closed

#5 Updated by The Foreman Bot about 1 month ago

  • Pull request https://github.com/theforeman/foreman/pull/6128 added

#6 Updated by Tomer Brisker about 1 month ago

  • Fixed in Releases 1.19.1 added

Also available in: Atom PDF