Jira (PDB-3544) Fact Updating is appears slower with Fact Reduplication

[Nick Walker (JIRA)]

Title: Message Title

Nick Walker created an issue   PuppetDB / PDB-3544 Fact Updating is appears slower with Fact Reduplication Issue Type: Bug Assignee: Unassigned Created: 2017/06/01 3:12 PM Priority: Normal Reporter: Nick Walker The Problem I'm seeing an increase in time to update the facts table in PE 2017.2.1. See, https://explain.depesz.com/s/GG28 I'm also see a large increase in log messages where queries on the facts table are making temporary files. 2017-06-01 13:04:53.254 CDT [db:pe-puppetdb,sess:59305624.1a9d,pid:6813,vtid:16/146,tid:0] LOG: temporary file: path "pg_tblspc/16384/PG_9.4_201409291/pgsql_tmp/pgsql_tmp6813.3", size 45768 2017-06-01 13:04:53.254 CDT [db:pe-puppetdb,sess:59305624.1a9d,pid:6813,vtid:16/146,tid:0] STATEMENT: select path, id, value_type_id, large_value_hash, case when large_value_hash is null and value_type_id = 5 then value end as value, case when large_value_hash is null then value_string end as value_string, value_integer, value_float, value_boolean from facts as f inner join fact_paths fp on f.fact_path_id = fp.id where f.factset_id = $1 That query has a plan like this: https://explain.depesz.com/s/Zsc Suggested Solution I believe both of these plans can be improved with the following index. CREATE INDEX facts_factset_id_fact_path_id_idx ON facts(factset_id, fact_path_id) Add Comment   This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.