Database/query optimizer for Tomcat/Java application

Question

Can anybody recommend the software to analyze/optimize existent queries in the Java/Tomcat application. About 70% of queries are generated by Hibernate. The database is PostgreSQL but I would like to use general solution, not for a particular database

Answer 1

There's no magic product for this, no "make my queries go faster".

What you can do is use a profiling tool like Hibernate Profiler to trace your database activity. Hibernate's built-in logging is also useful. See how to measure hibernate performance? for more detail.

These won't give you a recipe to make things go faster; you'll have to look at the hot-spots and:

• see if you can find ways to reduce the query rates through caching or smarter logic;
• reformulate HQL or Criteria queries more efficiently
• Rewrite bad HQL or Criteria queries in native SQL where necessary
• add indexes (where the update cost of adding the index doesn't exceed the benefit of having it for reads); etc.

You can also examine the PostgreSQL logs:

• Enable detailed logging (log_statement = 'all', log_min_duration_statement = 0)
• Install and use pgbadger to get log reports
• Read the log reports
• Look at whether you can improve the query patterns in your app based on the "hot" queries and tables

It can also be useful to:

• install the auto_explain module and enable it
• Examine the query plans of slow queries
• Identify the bits of your app they came from and see if you can reformulate the query more efficiently, cache where appropriate, or add indexes where the increased write cost doesn't exceed the benefits of the read speed-up.