Bug #35689
openQueries take too long to return when you have lots of facts
Description
Foreman 3.4.0 with ~ 6k hosts + 900 in discovery mode. Receiving facts from Puppet. ~1.8 million facts in the fact_values table.
When trying to do a query like "facts.architecture=x86_64" in the search box of "Hosts -> Discovered Hosts", it takes more than 4 minutes (check debug.txt attached).
The query is constructed as the following:
irb(main):001:0> puts Host::Discovered.search_for("facts.architecture=x86_64").to_sql
SELECT "hosts".* FROM "hosts" INNER JOIN "fact_values" fact_values_588605 ON (hosts.id = fact_values_588605.host_id)
INNER JOIN "fact_names" fact_names_588605 ON (fact_names_588605.id = fact_values_588605.fact_name_id) WHERE "hosts"."type" = 'Host::Discovered' AND ((fact_names_588605."name" = 'architecture') AND ("hosts"."id" IN (SELECT "host_id" FROM "fact_values" WHERE "fact_values_588605"."value" = 'x86_64' ))) ORDER BY "hosts"."created_at" DESC NULLS LAST
=> nil
irb(main):002:0>
It can be simplified to:
SELECT "hosts".* FROM "hosts" INNER JOIN "fact_values" fact_values_588605 ON (hosts.id = fact_values_588605.host_id) INNER JOIN "fact_names" fact_names_588605 ON (fact_names_588605.id = fact_values_588605.fact_name_id) WHERE "hosts"."type" = 'Host::Discovered' AND "hosts"."organization_id" = '33' AND "hosts"."organization_id" = '33' AND fact_names_588605."name" = 'architecture' AND "fact_values_588605"."value" = 'x86_64' ORDER BY "hosts"."created_at" DESC NULLS LAST LIMIT '100' OFFSET '0';
And it takes only 32ms to return.
Files
Updated by Ewoud Kohl van Wijngaarden almost 2 years ago
- Project changed from Foreman to Discovery
- Category set to Discovery plugin
Updated by Adam Ruzicka over 1 year ago
- Project changed from Discovery to Foreman
- Category changed from Discovery plugin to Search
Updated by The Foreman Bot over 1 year ago
- Status changed from New to Ready For Testing
- Assignee set to Adam Ruzicka
- Pull request https://github.com/theforeman/foreman/pull/9724 added
Updated by Adam Ruzicka over 1 year ago
- Copied to Bug #36491: Queries take too long to return when you have lots of facts added