Exploring and optimizing SQL performance of your system (PostgreSQL and Hibernate)?

Question

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:

check which queries take the longest time to execute - how can we do it in PostgreSQL?
check which queries are executed most often - how can we do it in PostgreSQL?
find out which queries are not using the indexes - how can we do it in PostgreSQL?
find out if the connection pool is operating without any delays - - how can we do it in PostgreSQL?
check the cache usage of Hibernate: are the queries from the first two points are cached? - how can we do it in Hibernate?
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?

Luciano · Accepted Answer · 2013-04-04 13:17:29Z

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.

duffymo · Answer 2 · 2013-04-04 13:29:09Z

Get the dynamic SQL generated by Hibernate and ask PostgreSQL to EXPLAIN PLAN.
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.
EXPLAIN PLAN and look for TABLE SCAN
Don't know - it's not under PostgreSQL control. Better to look into your app server.
Aspects and JMX
Aspects and JMX

migrated from stackoverflow.com Apr 4 at 16:37