Jira (PDB-3323) Stop checking if a report exists before inserting it

[Nick Walker (JIRA)]

Title: Message Title

Nick Walker created an issue   PuppetDB / PDB-3323 Stop checking if a report exists before inserting it Issue Type: Bug Assignee: Unassigned Attachments: Screen Shot 2017-03-07 at 10.45.00 AM.png Created: 2017/03/07 10:52 AM Labels: tcse Priority: Normal Reporter: Nick Walker The Problem Working with a customer we noticed performance degradation after updating from PE 2016.4 to 2016.5.2. Using PgBadger to analyze the PostgreSQL logs we can see that checking to see if a report already exists is taking longer than 5 seconds much more frequently than expected. Researching on my own test install I can see that the query performs a Seq Scan on reports because the where clause doesn't match an index. Adding an index on the hash column that isn't wrapped in a function causes the query plan to switch to using a Index Only Scan. pe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'; QUERY PLAN -------------------------------------------------------------------------------------------------- Seq Scan on reports (cost=0.00..39.31 rows=1 width=0) (actual time=0.012..0.105 rows=1 loops=1) Filter: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Rows Removed by Filter: 186 Planning time: 0.142 ms Execution time: 0.133 ms (5 rows)   pe-puppetdb=# create index test on reports(hash); CREATE INDEX pe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Index Only Scan using test on reports (cost=0.14..8.16 rows=1 width=0) (actual time=0.033..0.035 rows=1 loops=1) Index Cond: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Heap Fetches: 1 Planning time: 0.346 ms Execution time: 0.062 ms (5 rows) Additional Thoughts I think we should research the current index on the hash column and determine if queries are being designed to hit it or if queries are being constructed like the one above. We hopefully shouldn't need two indexes on the same column but we should make it easy to construct queries without special knowledge of how to make the where clause. 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.