Hi there,

following is the explain plan for the sql:

Nested Loop Left Join  (cost=16668.75..45631.15 rows=4095 width=32186) 
(actual time=0.444..241.535 rows=55104 loops=1)
->  Nested Loop Left Join  (cost=16668.75..45229.33 rows=1024 width=32136) 
(actual time=0.438..149.625 rows=13776 loops=1)
Join Filter: false
Filter: (((hosts.name)::text ~~* '%goldstan%'::text) OR (hosts.comment ~~* 
'%goldstan%'::text) OR ((models.name)::text ~~* '%goldstan%'::text) OR 
((hostgroups.name)::text ~~* '%gold
stan%'::text) OR ((hostgroups.title)::text ~~* '%goldstan%'::text) OR 
((hostgroups.title)::text ~~* '%goldstan%'::text) OR ((domains.name)::text 
~~* '%goldstan%'::text) OR ((realms.name)::te
xt ~~* '%goldstan%'::text) OR ((environments.name)::text ~~* 
'%goldstan%'::text) OR ((architectures.name)::text ~~* '%goldstan%'::text) 
OR ((compute_resources.name)::text ~~* '%goldstan%'::t
ext) OR ((images.name)::text ~~* '%goldstan%'::text) OR 
((operatingsystems.name)::text ~~* '%goldstan%'::text) OR 
(operatingsystems.description ~~* '%goldstan%'::text) OR ((operatingsystems.
title)::text ~~* '%goldstan%'::text) OR ((operatingsystems.major)::text ~~* 
'%goldstan%'::text) OR ((operatingsystems.minor)::text ~~* 
'%goldstan%'::text) OR ((nics.ip)::text ~~* '%goldstan%
'::text) OR ((nics.ip)::text ~~* '%goldstan%'::text) OR ((nics.mac)::text 
~~* '%goldstan%'::text) OR ((taxonomies.title)::text ~~* 
'%goldstan%'::text) OR ((subnets.network)::text ~~* '%golds
tan%'::text) OR (subnets.name ~~* '%goldstan%'::text) OR 
((hosts.uuid)::text ~~* '%goldstan%'::text) OR ((nics.mac)::text ~~* 
'%goldstan%'::text) OR ((operatingsystems.name)::text ~~* '%gold
stan%'::text) OR (operatingsystems.description ~~* '%goldstan%'::text) OR 
((operatingsystems.title)::text ~~* '%goldstan%'::text) OR 
((operatingsystems.major)::text ~~* '%goldstan%'::text) O
R ((operatingsystems.minor)::text ~~* '%goldstan%'::text) OR 
((smart_proxies.name)::text ~~* '%goldstan%'::text) OR 
((katello_host_collections.name)::text ~~* '%goldstan%'::text) OR ((katell
o_installed_packages.nvra)::text ~~* '%goldstan%'::text) OR 
((katello_installed_packages.name)::text ~~* '%goldstan%'::text) OR 
((katello_content_views.name)::text ~~* '%goldstan%'::text) OR
((katello_environments.name)::text ~~* '%goldstan%'::text) OR 
((katello_subscription_facets.release_version)::text ~~* 
'%goldstan%'::text) OR ((katello_subscription_facets.service_level)::t
ext ~~* '%goldstan%'::text) OR ((katello_subscription_facets.uuid)::text 
~~* '%goldstan%'::text) OR ((discovery_rules.name)::text ~~* 
'%goldstan%'::text))
->  Nested Loop Left Join  (cost=16668.75..45229.33 rows=79625 width=30586) 
(actual time=0.429..113.589 rows=13776 loops=1)
->  Nested Loop Left Join  (cost=16668.75..22460.28 rows=79625 width=29492) 
(actual time=0.423..74.037 rows=13776 loops=1)
Join Filter: (katello_content_facets.host_id = hosts.id)
->  Nested Loop Left Join  (cost=16652.20..20652.17 rows=79625 width=28398) 
(actual time=0.410..50.180 rows=13776 loops=1)
Join Filter: (content_facets_hosts_join.host_id = hosts.id)
->  Nested Loop Left Join  (cost=16635.66..18844.05 rows=79625 width=27309) 
(actual time=0.397..26.377 rows=13776 loops=1)
Join Filter: (katello_host_installed_packages.host_id = hosts.id)
->  Nested Loop Left Join  (cost=41.24..458.07 rows=35 width=27260) (actual 
time=0.211..0.837 rows=6 loops=1)
Join Filter: (katello_host_collection_hosts.host_id = hosts.id)
->  Nested Loop Left Join  (cost=13.78..429.83 rows=5 width=26683) (actual 
time=0.195..0.807 rows=6 loops=1)
Join Filter: (smart_proxies.id = hosts.content_source_id)
->  Nested Loop Left Join  (cost=13.78..424.43 rows=5 width=25111) (actual 
time=0.190..0.757 rows=6 loops=1)
Join Filter: (subnets.id = primary_interfaces_hosts_join.subnet_id)
->  Nested Loop Left Join  (cost=3.50..413.81 rows=5 width=21297) (actual 
time=0.179..0.710 rows=6 loops=1)
->  Nested Loop Left Join  (cost=0.00..316.12 rows=5 width=19546) (actual 
time=0.163..0.599 rows=6 loops=1)
Join Filter: false
->  Nested Loop Left Join  (cost=0.00..316.12 rows=5 width=16334) (actual 
time=0.160..0.584 rows=6 loops=1)
Join Filter: (interfaces_hosts.host_id = hosts.id)
->  Nested Loop Left Join  (cost=0.00..292.04 rows=1 width=14583) (actual 
time=0.157..0.566 rows=1 loops=1)
->  Nested Loop Left Join  (cost=0.00..283.76 rows=1 width=11970) (actual 
time=0.154..0.561 rows=1 loops=1)
Join Filter: (operatingsystems.id = hosts.operatingsystem_id)
->  Nested Loop Left Join  (cost=0.00..282.51 rows=1 width=11874) (actual 
time=0.145..0.546 rows=1 loops=1)
Join Filter: (primary_interfaces_hosts.host_id = hosts.id)
->  Nested Loop Left Join  (cost=0.00..258.48 rows=1 width=10123) (actual 
time=0.138..0.537 rows=1 loops=1)
Join Filter: (taxonomies.id = hosts.location_id)
->  Nested Loop Left Join  (cost=0.00..250.20 rows=1 width=7458) (actual 
time=0.132..0.529 rows=1 loops=1)
Join Filter: (primary_interfaces_hosts_join.host_id = hosts.id)
->  Nested Loop Left Join  (cost=0.00..226.16 rows=1 width=7454) (actual 
time=0.126..0.521 rows=1 loops=1)
Join Filter: false
->  Nested Loop Left Join  (cost=0.00..226.16 rows=1 width=6390) (actual 
time=0.125..0.519 rows=1 loops=1)
Join Filter: (models.id = hosts.model_id)
->  Nested Loop Left Join  (cost=0.00..224.87 rows=1 width=5283) (actual 
time=0.118..0.509 rows=1 loops=1)
Join Filter: (hostgroups.id = hosts.hostgroup_id)
->  Nested Loop Left Join  (cost=0.00..223.35 rows=1 width=1796) (actual 
time=0.103..0.488 rows=1 loops=1)
Join Filter: (domains.id = nics.domain_id)
->  Nested Loop Left Join  (cost=0.00..222.31 rows=1 width=1749) (actual 
time=0.099..0.481 rows=1 loops=1)
Join Filter: (nics.host_id = hosts.id)
->  Nested Loop Left Join  (cost=0.00..198.27 rows=1 width=1714) (actual 
time=0.086..0.467 rows=1 loops=1)
Join Filter: (environments.id = hosts.environment_id)
->  Nested Loop Left Join  (cost=0.00..197.16 rows=1 width=1646) (actual 
time=0.079..0.456 rows=1 loops=1)
Join Filter: (architectures.id = hosts.architecture_id)
->  Index Scan using index_hosts_on_name on hosts (cost=0.00..196.11 rows=1 
width=1612) (actual time=0.072..0.446 rows=1 loops=1)
Filter: (((type)::text = 'Host::Managed'::text) AND (organization_id = 3) 
AND (location_id = 4) AND (id = 934))
->  Seq Scan on architectures  (cost=0.00..1.02 rows=2 width=34) (actual 
time=0.003..0.003 rows=2 loops=1)
->  Seq Scan on environments  (cost=0.00..1.08 rows=3 width=68) (actual 
time=0.005..0.006 rows=5 loops=1)
Filter: (environments.id = ANY ('{2,3,4,8,5}'::integer[]))
->  Index Scan using index_by_host on nics  (cost=0.00..24.02 rows=1 
width=39) (actual time=0.011..0.011 rows=1 loops=1)
Index Cond: (nics.host_id = 934)
Filter: nics."primary"
->  Seq Scan on domains  (cost=0.00..1.02 rows=1 width=51) (actual 
time=0.003..0.003 rows=1 loops=1)
Filter: (domains.id = 1)
->  Seq Scan on hostgroups  (cost=0.00..1.41 rows=9 width=3487) (actual 
time=0.003..0.012 rows=14 loops=1)
Filter: (hostgroups.id = ANY 
('{14,27,19,1,2,21,9,3,10,23,11,4,18,22}'::integer[]))
->  Seq Scan on models  (cost=0.00..1.13 rows=13 width=1107) (actual 
time=0.002..0.005 rows=13 loops=1)
->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 
rows=0 loops=1)
One-Time Filter: false
->  Index Scan using index_by_host on nics primary_interfaces_hosts_join  
(cost=0.00..24.02 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1) 
Index Cond: (primary_interfaces_hosts_join.host_id = 934)
Filter: primary_interfaces_hosts_join."primary"
->  Index Scan using taxonomies_pkey on taxonomies  (cost=0.00..8.27 rows=1 
width=2665) (actual time=0.004..0.006 rows=1 loops=1) Index Cond: 
(taxonomies.id = 4)
Filter: ((taxonomies.type)::text = 'Location'::text)
->  Index Scan using index_by_host on nics primary_interfaces_hosts  
(cost=0.00..24.02 rows=1 width=1751) (actual time=0.004..0.005 rows=1 
loops=1)
Index Cond: (primary_interfaces_hosts.host_id = 934)
Filter: primary_interfaces_hosts."primary"
->  Seq Scan on operatingsystems  (cost=0.00..1.11 rows=11 width=96) 
(actual time=0.003..0.011 rows=13 loops=1)
->  Index Scan using images_pkey on images  (cost=0.00..8.27 rows=1 
width=2613) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (images.id = hosts.image_id)
->  Index Scan using index_by_host on nics interfaces_hosts  
(cost=0.00..24.02 rows=5 width=1751) (actual time=0.001..0.006 rows=6 
loops=1)
Index Cond: (interfaces_hosts.host_id = 934)
->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 
rows=0 loops=6) One-Time Filter: false
->  Bitmap Heap Scan on nics provision_interfaces_hosts  (cost=3.50..19.53 
rows=1 width=1751) (actual time=0.009..0.014 rows=1 loops=6)
Recheck Cond: ((provision_interfaces_hosts.host_id = 934) AND 
(provision_interfaces_hosts.host_id = hosts.id))
Filter: provision_interfaces_hosts.provision
->  Bitmap Index Scan on index_by_host  (cost=0.00..3.50 rows=5 width=0) 
(actual time=0.006..0.006 rows=6 loops=6)
Index Cond: ((provision_interfaces_hosts.host_id = 934) AND 
(provision_interfaces_hosts.host_id = hosts.id))
->  Materialize  (cost=10.28..10.31 rows=3 width=3818) (actual 
time=0.002..0.004 rows=3 loops=6)
->  Seq Scan on subnets  (cost=0.00..10.28 rows=3 width=3818) (actual 
time=0.004..0.014 rows=3 loops=1)
Filter: (id = ANY ('{5,2,1}'::integer[]))
->  Seq Scan on smart_proxies  (cost=0.00..1.05 rows=2 width=1572) (actual 
time=0.001..0.004 rows=3 loops=6)
Filter: (smart_proxies.id = ANY ('{7,11,1}'::integer[]))
->  Materialize  (cost=27.46..27.53 rows=7 width=581) (actual 
time=0.003..0.003 rows=0 loops=6)
->  Hash Left Join  (cost=17.23..27.45 rows=7 width=581) (actual 
time=0.013..0.013 rows=0 loops=1)
Hash Cond: (katello_host_collection_hosts.host_collection_id = 
katello_host_collections.id)
->  Bitmap Heap Scan on katello_host_collection_hosts  (cost=4.30..14.45 
rows=7 width=8) (actual time=0.011..0.011 rows=0 loops=1)
Recheck Cond: (host_id = 934)
->  Bitmap Index Scan on index_katello_host_collection_hosts_on_host_id  
(cost=0.00..4.30 rows=7 width=0) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: (host_id = 934)
->  Hash  (cost=11.30..11.30 rows=130 width=577) (never executed)
->  Seq Scan on katello_host_collections  (cost=0.00..11.30 rows=130 
width=577) (never executed)
->  Materialize  (cost=16594.42..16617.17 rows=2275 width=53) (actual 
time=0.031..2.017 rows=2296 loops=6)
->  Nested Loop Left Join  (cost=53.96..16592.15 rows=2275 width=53) 
(actual time=0.181..7.464 rows=2296 loops=1)
->  Bitmap Heap Scan on katello_host_installed_packages  
(cost=53.96..2986.55 rows=2275 width=8) (actual time=0.172..0.713 rows=2296 
loops=1)
Recheck Cond: (host_id = 934)
->  Bitmap Index Scan on index_katello_host_installed_packages_on_host_id  
(cost=0.00..53.39 rows=2275 width=0) (actual time=0.163..0.163 rows=2350 
loops=1)
Index Cond: (host_id = 934)
->  Index Scan using katello_installed_packages_pkey on 
katello_installed_packages  (cost=0.00..5.97 rows=1 width=49) (actual 
time=0.002..0.002 rows=1 loops=2296)
Index Cond: (katello_installed_packages.id = 
katello_host_installed_packages.installed_package_id)
->  Materialize  (cost=16.55..16.56 rows=1 width=1093) (actual 
time=0.000..0.000 rows=1 loops=13776)
->  Nested Loop Left Join  (cost=0.00..16.55 rows=1 width=1093) (actual 
time=0.008..0.009 rows=1 loops=1)
->  Index Scan using katello_content_facets_host_id on 
katello_content_facets content_facets_hosts_join  (cost=0.00..8.27 rows=1 
width=8) (actual time=0.003..0.004 rows=1 loops=1)
Index Cond: (host_id = 934)
->  Index Scan using katello_environments_pkey on katello_environments  
(cost=0.00..8.27 rows=1 width=1089) (actual time=0.003..0.003 rows=1 
loops=1)
Index Cond: (katello_environments.id = 
content_facets_hosts_join.lifecycle_environment_id)
->  Materialize  (cost=16.55..16.56 rows=1 width=1098) (actual 
time=0.000..0.000 rows=1 loops=13776)
->  Nested Loop Left Join  (cost=0.00..16.55 rows=1 width=1098) (actual 
time=0.006..0.007 rows=1 loops=1)
->  Index Scan using katello_content_facets_host_id on 
katello_content_facets  (cost=0.00..8.27 rows=1 width=8) (actual 
time=0.001..0.002 rows=1 loops=1)
Index Cond: (host_id = 934)
->  Index Scan using katello_content_views_pkey on katello_content_views  
(cost=0.00..8.27 rows=1 width=1094) (actual time=0.004..0.004 rows=1 
loops=1)
Index Cond: (katello_content_views.id = 
katello_content_facets.content_view_id)
->  Index Scan using katello_subscription_facets_host_id on 
katello_subscription_facets  (cost=0.00..0.27 rows=1 width=1094) (actual 
time=0.001..0.001 rows=1 loops=13776)
Index Cond: ((katello_subscription_facets.host_id = 934) AND 
(katello_subscription_facets.host_id = hosts.id))
->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 
rows=0 loops=13776)One-Time Filter: false
->  Index Scan using index_host_status_on_host_id on host_status  
(cost=0.00..0.34 rows=4 width=50) (actual time=0.001..0.002 rows=4 
loops=13776)
Index Cond: ((host_status.host_id = 934) AND (host_status.host_id = 
hosts.id))
Total runtime: 252.073 ms
(122 rows)

On Thursday, September 22, 2016 at 5:59:00 AM UTC+8, jsherril wrote:
>
> On 09/21/2016 10:48 AM, sinux shen wrote:
>
> we actually did what you suggested, it was pretty much like the following 
> two join that caused the problem: 
>
> > LEFT OUTER JOIN "katello_installed_packages" ON 
>  "katello_installed_packages"."id" 
> = "katello_host_installed_packages"."installed_package_id" 
> > LEFT OUTER JOIN "katello_content_facets" 
> ON "katello_content_facets"."host_id" = "hosts"."id" 
>
> each table has more than 500,000+ rows of records. I will try to get the 
> output when I get back to work tomorrow.
>
> Went ahead and opened an issue here:  
> http://projects.theforeman.org/issues/16647
> and a PR here: https://github.com/Katello/katello/pull/6338
>
> -Justin
>
> Thanks again for the help.
>
> On Wednesday, September 21, 2016 at 9:47:02 PM UTC+8, Chris Duryee wrote: 
>>
>>
>>
>> On 09/21/2016 09:24 AM, sinux shen wrote: 
>> > Hi, Lukas & Ohad, 
>> > 
>> > Thanks for the help, we do turned the slow log on and we found the 
>> heavy 
>> > SQL query while search a specific host and now we realized that we 
>> should 
>> > use auto completer to make the query more smart and lite, and here is 
>> the 
>> > SQL that almost join all the other tables that was associated to that 
>> > specific host, and in two of the table (katello_host_installed_packages 
>> and 
>> > katello_installed_packages), we have 500,000+ rows for each, and this 
>> > caused heavy load and slow query, just FYI: 
>> > 
>> > SELECT DISTINCT "hosts".id FROM "hosts" 
>> > LEFT OUTER JOIN "models" ON "models"."id" = "hosts"."model_id" 
>> > LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = 
>> "hosts"."hostgroup_id" 
>> > AND "hostgroups"."id" IN (14, 27, 19, 1, 2, 21, 9, 3, 10, 23, 11, 4, 
>> 18, 22) 
>> > LEFT OUTER JOIN "nics" ON "nics"."host_id" = "hosts"."id" AND 
>> > "nics"."primary" = 't' 
>> > LEFT OUTER JOIN "domains" ON "domains"."id" = "nics"."domain_id" AND 
>> > "domains"."id" IN (1) 
>> > LEFT OUTER JOIN "realms" ON "realms"."id" = "hosts"."realm_id" AND 1=0 
>> > LEFT OUTER JOIN "environments" ON "environments"."id" = 
>> > "hosts"."environment_id" AND "environments"."id" IN (2, 3, 4, 8, 5) 
>> > LEFT OUTER JOIN "architectures" ON "architectures"."id" = 
>> > "hosts"."architecture_id" 
>> > LEFT OUTER JOIN "compute_resources" ON "compute_resources"."id" = 
>> > "hosts"."compute_resource_id" AND 1=0 
>> > 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" "interfaces_hosts" ON 
>> "interfaces_hosts"."host_id" = 
>> > "hosts"."id" 
>> > LEFT OUTER JOIN "taxonomies" ON "taxonomies"."id" = 
>> "hosts"."location_id" 
>> > AND "taxonomies"."type" IN ('Location') 
>> > 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"."id" IN (5, 
>> 2, 1) 
>> > LEFT OUTER JOIN "nics" "provision_interfaces_hosts" ON 
>> > "provision_interfaces_hosts"."host_id" = "hosts"."id" AND 
>> > "provision_interfaces_hosts"."provision" = 't' 
>> > LEFT OUTER JOIN "smart_proxies" ON "smart_proxies"."id" = 
>> > "hosts"."content_source_id" AND "smart_proxies"."id" IN (7, 11, 1) 
>> > 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_host_installed_packages" ON 
>> > "katello_host_installed_packages"."host_id" = "hosts"."id" 
>> > LEFT OUTER JOIN "katello_installed_packages" ON 
>> > "katello_installed_packages"."id" = 
>> > "katello_host_installed_packages"."installed_package_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_subscription_facets" ON 
>> > "katello_subscription_facets"."host_id" = "hosts"."id" 
>> > LEFT OUTER JOIN "discovery_rules" ON "discovery_rules"."id" = 
>> > "hosts"."discovery_rule_id" AND 1=0 
>> > LEFT OUTER JOIN "host_status" ON "host_status"."host_id" = "hosts"."id" 
>> > WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" 
>> IN 
>> > (3) AND "hosts"."location_id" IN (4) AND (("hosts"."name" ILIKE 
>> > '%orangewolf%' OR "hosts"."comment" ILIKE '%orangewolf%' OR 
>> "models"."name" 
>> > ILIKE '%orangewolf%' OR "hostgroups"."name" ILIKE '%orangewolf%' OR 
>> > "hostgroups"."title" ILIKE '%orangewolf%' OR "hostgroups"."title" ILIKE 
>> > '%orangewolf%' OR "domains"."name" ILIKE '%orangewolf%' OR 
>> "realms"."name" 
>> > ILIKE '%orangewolf%' OR "environments"."name" ILIKE '%orangewolf%' OR 
>> > "architectures"."name" ILIKE '%orangewolf%' OR 
>> "compute_resources"."name" 
>> > ILIKE '%orangewolf%' OR "images"."name" ILIKE '%orangewolf%' OR 
>> > "operatingsystems"."name" ILIKE '%orangewolf%' OR 
>> > "operatingsystems"."description" ILIKE '%orangewolf%' OR 
>> > "operatingsystems"."title" ILIKE '%orangewolf%' OR 
>> > "operatingsystems"."major" ILIKE '%orangewolf%' OR 
>> > "operatingsystems"."minor" ILIKE '%orangewolf%' OR "nics"."ip" ILIKE 
>> > '%orangewolf%' OR "nics"."ip" ILIKE '%orangewolf%' OR "nics"."mac" 
>> ILIKE 
>> > '%orangewolf%' OR "taxonomies"."title" ILIKE '%orangewolf%' OR 
>> > "subnets"."network" ILIKE '%orangewolf%' OR "subnets"."name" ILIKE 
>> > '%orangewolf%' OR "hosts"."uuid" ILIKE '%orangewolf%' OR "nics"."mac" 
>> ILIKE 
>> > '%orangewolf%' OR "operatingsystems"."name" ILIKE '%orangewolf%' OR 
>> > "operatingsystems"."description" ILIKE '%orangewolf%' OR 
>> > "operatingsystems"."title" ILIKE '%orangewolf%' OR 
>> > "operatingsystems"."major" ILIKE '%orangewolf%' OR 
>> > "operatingsystems"."minor" ILIKE '%orangewolf%' OR 
>> "smart_proxies"."name" 
>> > ILIKE '%orangewolf%' OR "katello_host_collections"."name" ILIKE 
>> > '%orangewolf%' OR "katello_installed_packages"."nvra" ILIKE 
>> '%orangewolf%' 
>> > OR "katello_installed_packages"."name" ILIKE '%orangewolf%' OR 
>> > "katello_content_views"."name" ILIKE '%orangewolf%' OR 
>> > "katello_environments"."name" ILIKE '%orangewolf%' OR 1=0 OR 1=0 OR 
>> > "katello_subscription_facets"."release_version" ILIKE '%orangewolf%' OR 
>> > "katello_subscription_facets"."service_level" ILIKE '%orangewolf%' OR 
>> > "katello_subscription_facets"."uuid" ILIKE '%orangewolf%' OR 
>> > "discovery_rules"."name" ILIKE '%orangewolf%')) AND "hosts"."id" IN 
>> (874) 
>> > ORDER BY "hosts"."name" ASC; 
>> > 
>>
>> Thanks for obtaining this query. Can you try the following? 
>>
>> * sudo su - postgres 
>> * psql foreman 
>> * in psql console: explain (analyze, buffers) <query from above> 
>>
>> This will output an explain plan, if you send that output we can confirm 
>> which comparison or join is causing issues. 
>>
>>
>> > On Tuesday, September 20, 2016 at 3:24:02 PM UTC+8, ohad wrote: 
>> >> 
>> >> 
>> >> 
>> >> On Tue, Sep 20, 2016 at 3:16 AM, sinux shen <sinux...@gmail.com 
>> >> <javascript:>> wrote: 
>> >> 
>> >>> I just simply search a specific hostname in the search bar, for 
>> example 
>> >>> “foo”, and then have to wait for a long time, normally 20~30 secs, I 
>> will 
>> >>> try to turn the slow query on and see what happened. 
>> >>> 
>> >> 
>> >> You should not use queries like foo unless you want to search across 
>> all 
>> >> host associations, you should use the auto completer to use a more 
>> specific 
>> >> term, e.g. name ~ foo 
>> >> 
>> >> when not defining the field to search on, we have to query across 
>> multiple 
>> >> tables, which makes quite a few sql queries which are probably are not 
>> >> required if you define a more specific search term. 
>> >> 
>> >> Ohad 
>> >> 
>> >>> 
>> >>> Thanks 
>> >>> -Sinux 
>> >>>> On Sep 14, 2016, at 3:59 PM, Lukas Zapletal <lz...@redhat.com 
>> >>> <javascript:>> wrote: 
>> >>>> 
>> >>>>> what was the search query that you used? 
>> >>>> 
>> >>>> And do you experience this when loading "intelligent completion" or 
>> when 
>> >>>> doing actual query? 
>> >>>> 
>> >>>> Are you able to enable slow queries logging in your postgres and 
>> send us 
>> >>>> the output? 
>> >>>> 
>> >>>> https://wiki.postgresql.org/wiki/Logging_Difficult_Queries 
>> >>>> 
>> >>>> Easiest is to set log_min_duration_statement to reasonable value (1 
>> >>>> second). 
>> >>>> 
>> >>>> -- 
>> >>>> Later, 
>> >>>> Lukas #lzap Zapletal 
>> >>>> 
>> >>>> -- 
>> >>>> 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-user...@googlegroups.com <javascript:>. 
>> >>>> To post to this group, send email to forema...@googlegroups.com 
>> >>> <javascript:>. 
>> >>>> Visit this group at <https://groups.google.com/group/foreman-users>
>> https://groups.google.com/group/foreman-users. 
>> >>>> For more options, visit <https://groups.google.com/d/optout>
>> https://groups.google.com/d/optout. 
>> >>> 
>> >>> -- 
>> >>> 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-user...@googlegroups.com <javascript:>. 
>> >>> To post to this group, send email to forema...@googlegroups.com 
>> >>> <javascript:>. 
>> >>> Visit this group at <https://groups.google.com/group/foreman-users>
>> https://groups.google.com/group/foreman-users. 
>> >>> For more options, visit <https://groups.google.com/d/optout>
>> https://groups.google.com/d/optout. 
>> >>> 
>> >> 
>> >> 
>> > 
>>
> -- 
> 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-user...@googlegroups.com <javascript:>.
> To post to this group, send email to forema...@googlegroups.com 
> <javascript:>.
> Visit this group at https://groups.google.com/group/foreman-users.
> For more options, visit https://groups.google.com/d/optout.
>
>
>

-- 
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.

Reply via email to