Jira (PDB-3219) Deleting from reports spends most of the time enforcing a constraint on certnames

[Nick Walker (JIRA)]

Title: Message Title

Nick Walker created an issue   PuppetDB / PDB-3219 Deleting from reports spends most of the time enforcing a constraint on certnames Issue Type: Bug Assignee: Unassigned Created: 2016/12/08 7:28 AM Labels: tcse Priority: Normal Reporter: Nick Walker The Problem https://explain.depesz.com/s/Ve5S QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Delete on reports (cost=2112.19..255114.61 rows=77903 width=6) (actual time=9879.603..9879.603 rows=0 loops=1) -> Bitmap Heap Scan on reports (cost=2112.19..255114.61 rows=77903 width=6) (actual time=3081.974..3542.301 rows=3134 loops=1) Recheck Cond: (producer_timestamp < (now() - '7 days'::interval)) Heap Blocks: exact=34281 -> Bitmap Index Scan on idx_reports_producer_timestamp (cost=0.00..2092.71 rows=77903 width=0) (actual time=38.582..38.582 rows=95352 loops=1) Index Cond: (producer_timestamp < (now() - '7 days'::interval)) Planning time: 0.895 ms Trigger for constraint certnames_reports_id_fkey: time=74064.742 calls=3134 Trigger for constraint resource_events_report_id_fkey: time=215.914 calls=3134 Execution time: 84169.223 ms (10 rows) We can see 74 of the 84 seconds are spent on certnames_reports_id_fkey constraint. This is related to past tickets about reducing the use of cascading deletes and updates in puppetdb. PDB-2415 This ticket has a PR that I can be used if we also recreate the constraints at the end of the GC to be NOT VALID. Which means they would not be validated at creation time. PDB-2477 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.