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.