I started an application around 5 years ago using Hibernate and writing my queries in HQL. The primary search screen has many options to filter and joins many tables and views. As the application grew the SQL Hibernate is generating is out of hand and needs optimization. As with other parts of the application, I usually fall back to raw SQL with good results. This is what I am attempting now. I thought I had read somewhere that Postges could ignore a join if it was not necessary because there were no columns from the table or view selected in the query. Is this possible? What I want to do is to create a view with all the columns I need for the search, and have my search pull from this view. Here are some query times: 396 ms without the table joined 2008 ms with the query joined, but no columns selected The time is the same if I select the calculated values from the view. This way I can just generate the select, group by, and order by terms and just select from view without having to manually join the views if they are required. I also tried another approach and tried to join the more expensive views to a simpler view that was not expensive. select * from v_no_expensive_calcs vne join v_expensive_calcs ve.id=vne.id This takes about 2000 ms when joining v_expensive_calcs directly inside v_no_expensive_calcs only takes 1100 ms. I thought these would be equivalent. The idea is that some users do not need cost, price, look up, or profit calculations which are expensive. Hibernate handles this currently well, but it also generates a crazy mess that is dragging down the whole application. I will probably end up doing this manually, but I am trying to make the application logic simpler. Any ideas or advice? I am using Postgres 9.1.1. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
