Project

General

Profile

Actions

Bug #35689

open

Queries take too long to return when you have lots of facts

Added by Guilherme M. Schroeder over 1 year ago. Updated 10 months ago.

Status:
Ready For Testing
Priority:
Normal
Assignee:
Category:
Search
Target version:
-
Difficulty:
Triaged:
No
Fixed in Releases:
Found in Releases:

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

debug.txt debug.txt 166 KB Guilherme M. Schroeder, 10/28/2022 03:39 PM

Related issues 1 (1 open0 closed)

Copied to Discovery - Bug #36491: Queries take too long to return when you have lots of factsReady For TestingAdam RuzickaActions
Actions #1

Updated by Ewoud Kohl van Wijngaarden over 1 year ago

  • Project changed from Foreman to Discovery
  • Category set to Discovery plugin
Actions #2

Updated by Adam Ruzicka 10 months ago

  • Project changed from Discovery to Foreman
  • Category changed from Discovery plugin to Search
Actions #3

Updated by The Foreman Bot 10 months ago

  • Status changed from New to Ready For Testing
  • Assignee set to Adam Ruzicka
  • Pull request https://github.com/theforeman/foreman/pull/9724 added
Actions #4

Updated by Adam Ruzicka 10 months ago

  • Copied to Bug #36491: Queries take too long to return when you have lots of facts added
Actions

Also available in: Atom PDF