Beth Cornils Ryan Senior I'm +1 on bumping. I just looked into it for a few minutes and I can see the issue. I don't think it'll be a hard fix.
The problem is right here:
That code is not covered by tests, and the latest_report column of certnames has since been renamed to latest_report_id. Additionally the latest_reports table does not exist. I think changing that line to
latest_events.report_id %s (SELECT certnames.latest_report_id FROM certnames)
and making another change to include the report_id column in the latest_events CTE will fix it. After that we'll need to test it against a large database to verify an improvement. If the optimizer is still getting tripped up on the CTE at that point we can do a nasty workaround by parsing the query here: https://github.com/puppetlabs/puppetdb/blob/master/src/puppetlabs/puppetdb/query/events.clj#L52
and baking the latest report restriction into the CTE itself.
Beth Cornils What's your timeframe for this?