Imagine you have inherited some project with extensive usage of SQL queries.

Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point.

A question is: could you suggest a plan to sequentially scan the system and optimize the SQL queries performance?

I suggest the following steps of that plan:

  1. check which queries take the longest time to execute - how can we do it in PostgreSQL?
  2. check which queries are executed most often - how can we do it in PostgreSQL?
  3. find out which queries are not using the indexes - how can we do it in PostgreSQL?
  4. find out if the connection pool is operating without any delays - - how can we do it in PostgreSQL?
  5. check the cache usage of Hibernate: are the queries from the first two points are cached? - how can we do it in Hibernate?
  6. which queries tend to hit the database instead of hitting the cache - how can we do it in Hibernate?

Please suggest your own options.

May be there is some soft to recommend for this task?

May be there are some other database parameters to check, to log, to plot to monitor?

2 Answers 2


Here are some more checks I can think of:

  • Check that PostgreSQL prepared statements use is enabled.

  • If you have a table with many elements, check if some of those can be "moved" to another table of archived items. (for instance, one query that returns the transactions of the last 7 days)

  • Check that you are not only using the first level cache, but 2nd level as well. Maybe with ehcache or infinispan. Those can cache many objects, even persisting some to disk. You have to make sure that modifications in other threads invalidates those objects. This also depends on the transaction isolation that you are using.

  • If you have entities that are readonly (they are never modified) you may consider loading all of those into memory as java objects. Then you can reduce some joins.

  • For your entities, if you have a dependency to another object, you may save its ID in your entity field. Then when you load your object it doesn't do a join.

  • Related to the previous point, you can also use lazy loading of entities.

  • Use P6Spy or a similar library to know how long the queries are taking.

  1. Get the dynamic SQL generated by Hibernate and ask PostgreSQL to EXPLAIN PLAN.
  2. Log the queries to your database using an aspect. Add in wall time while you're at it so you can calculate mean wall time and standard deviation.
  3. EXPLAIN PLAN and look for TABLE SCAN
  4. Don't know - it's not under PostgreSQL control. Better to look into your app server.
  5. Aspects and JMX
  6. Aspects and JMX

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.