PostgreSQL has a slow query - Troubleshooting

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

A single or few selected queries are or have become very slow in Clarity on a PostgreSQL database. Performance/ slowness issue that is linked to a database culprit

Environment

Release : Any

Resolution

1. Check if you are able to run the same query with the exact parameters on the database - is it also slow
2. If it is, you can run an explain analyze on this query in sql analyzer tool. I.e. 
   explain analyze select * from inv_investments
3. Get the output and review for the slowest part
4. If it's a specific table - check the bloat on that table, make sure if specific columns are selected, that the index is there or try creating a custom index if indicated by the explain plan
5. Additionally, you may run PG Badger report and establish what else was running on the database level at the same time if the problem is no longer reproducible