Uploaded image for project: 'Puppet Enterprise'
  1. Puppet Enterprise
  2. ENTERPRISE-1414

Console fact filtering generates queries with many nested loops

    XMLWordPrintable

Details

    • Improvement
    • Status: Open
    • Major
    • Resolution: Unresolved
    • PE 2019.8.0
    • None
    • Console, PuppetDB
    • CentOS 7
    • Dumpling
    • 41637
    • 1
    • Needs Assessment

    Description

      Using the "Filter by fact value" feature of the Console creates a poorly performing postgres query that contains a nested loop for each fact.  Here is the explain analyze output for a query filtering on `operatingsystem` and `operatingsystemmajrelease`. Each filter condition is evaluated in a separate nested loop, and this is exacerbated for each added fact.

      Conversely, here is the explain analyze output for this equivalent PQL query:

      puppet query 'reports [certname, environment, end_time, hash, status, noop, noop_pending, corrective_change, job_id] { inventory { facts.operatingsystem = "CentOS" and facts.operatingsystemmajrelease = "7" } limit 50 order by end_time}' 

      The two filters are evaluated in the same step and the query is faster.

      In addition to PE-29836, this can result in multiple slowly running queries that can negatively impact performance as a result of using this feature. I'll attach the explain outputs in case they are pruned from the website.

       

      Attachments

        Activity

          People

            Unassigned Unassigned
            henry.wang Henry Wang
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Zendesk Support