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 <[email protected]
>> >> <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 <[email protected]
>> >>> <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 [email protected] <javascript:>.
>> >>>> To post to this group, send email to [email protected]
>> >>> <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 [email protected] <javascript:>.
>> >>> To post to this group, send email to [email protected]
>> >>> <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 [email protected] <javascript:>.
> To post to this group, send email to [email protected]
> <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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/foreman-users.
For more options, visit https://groups.google.com/d/optout.
