Jira (PDB-5450) Slow fact queries after PuppetDB 7.9.0 upgrade

[Austin Blatt (Jira)]

Title: Message Title

Austin Blatt commented on PDB-5450   Re: Slow fact queries after PuppetDB 7.9.0 upgrade I think the facts queries are creating the significant load due to the JIT optimization that looks to be enabled on your Postgres, and that load is slowing the Postgres down and creating the poor query performance. I spun up a totally empty database and ran a similar query. With JIT disabled, it takes less than a millisecond. Planning Time: 1.312 ms Execution Time: 0.278 ms and then with JIT enabled it takes over 700ms Planning Time: 1.224 ms JIT: Functions: 56 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 12.209 ms, Inlining 81.108 ms, Optimization 268.355 ms, Emission 160.117 ms, Total 521.789 ms Execution Time: 735.923 ms It appears that the Postgres query planner is generating a huge cost estimate {{cost=745615.49..90016708.23 }} and then Postgres attempts as much JIT optimization as possible against the query because it expects it to be slow (even though it is not). This is in line with the explain analyzes that you provides, where most of the time for both queries seems to be spent in attempting the various JIT optimizations rather than the actual query. We can look into why Postgres's planner is getting such a wrong estimate, but in the mean time I would recommend trying to load the UI after disabling JIT. If that is not possible, it would also be safe to downgrade your PuppetDB to 7.8.0 because we did not have any database migrations between the two versions. Add Comment   This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.434144.1643897486000.9539.1644023940031%40Atlassian.JIRA.