Jira (PDB-4640) Add a file index to catalog_resources for CD4PE Impact Analysis

[Adam Bottchen (JIRA)]

Adam Bottchen created an issue

PuppetDB / PDB-4640 Add a file index to catalog_resources for CD4PE Impact Analysis Issue Type: Improvement Assignee: Unassigned Created: 2020/01/29 9:30 AM Priority: Major Reporter: Adam Bottchen As seen in CDPE-2850, CD4PE's impact analysis puts a great deal of stress on the catalog_resources table doing selects against the file row. We have seen in customer cases that performance can be greatly improved if an index is added and autovacuum frequency is increased: CREATE INDEX catalog_resources_file_idx on catalog_resources USING gin (file gin_trgm_ops) WHERE file IS NOT NULL; ALTER TABLE catalog_resources SET (autovacuum_analyze_scale_factor = 0.01); ALTER INDEX catalog_resources_file_idx SET (fastupdate=false); I do not see much downside to these changes being the default. Add Comment

This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93)

[Austin Blatt (JIRA)]

Austin Blatt commented on PDB-4640

Re: Add a file index to catalog_resources for CD4PE Impact Analysis Adam Bottchen any way we can get the database dump you mentioned so we can test its performance?

Add Comment

[Austin Blatt (JIRA)]

Austin Blatt updated an issue

PuppetDB / PDB-4640

Add a file index to catalog_resources for CD4PE Impact Analysis

Change By: Austin Blatt Team: HA

Add Comment

[Nick Walker (JIRA)]

Nick Walker commented on PDB-4640

Re: Add a file index to catalog_resources for CD4PE Impact Analysis oh cool well if we have test data and infracore I'd think we should consider additional optimizations in addition to the index

Add Comment

[Austin Blatt (JIRA)]

Austin Blatt updated an issue

PuppetDB / PDB-4640

Add a file index to catalog_resources for CD4PE Impact Analysis

Change By: Austin Blatt Team: HA

Add Comment

[Austin Blatt (JIRA)]

Austin Blatt assigned an issue to Austin Blatt

PuppetDB / PDB-4640 Add a file index to catalog_resources for CD4PE Impact Analysis

Change By: Austin Blatt Assignee: Austin Blatt

Add Comment

[Austin Blatt (JIRA)]

Austin Blatt updated an issue

PuppetDB / PDB-4640 Add a file index to catalog_resources for CD4PE Impact Analysis

Change By: Austin Blatt Release Notes: Not Needed New Feature

Add Comment

[Austin Blatt (JIRA)]

Austin Blatt updated an issue

PuppetDB / PDB-4640 Add a file index to catalog_resources for CD4PE Impact Analysis

Change By: Austin Blatt Release Notes Summary: If you have the PostgreSQL extension pg_trgm configured, adds an index on the catalog_resources table's file column. In a separate release notes entry, we have deprecated running PostgreSQL without the pg_trgm extension

Add Comment

[Heston Hoffman (JIRA)]

Heston Hoffman updated an issue

PuppetDB / PDB-4640 Add a file index to catalog_resources for CD4PE Impact Analysis

Change By: Heston Hoffman Labels: resolved-issue-added

Add Comment