Any was to prevent a join if no columns are selected from a view?
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.
On Sep 29, 2011, at 4:57 PM, Jason Long wrote:
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?
This sounds like incorrect logic to me, so I would be surprised if it was possible.
On 30/09/2011, at 8:57 AM, Jason Long wrote:
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?
You might be thinking of this enhancement introduced in 9.0:
http://www.postgresql.org/docs/9.0/static/release-9-0.html#AEN99923
Remove unnecessary outer joins (Robert Haas)
Outer joins where the inner side is unique and not referenced above the join are unnecessary and are therefore now removed. This will accelerate many automatically generated queries, such as those created by object-relational mappers (ORMs).
On 30/09/2011, at 8:57 AM, Jason Long wrote:
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?You might be thinking of this enhancement introduced in 9.0:
http://www.postgresql.org/docs/9.0/static/release-9-0.html#AEN99923
Remove unnecessary outer joins (Robert Haas)
Outer joins where the inner side is unique and not referenced above the join are unnecessary and are therefore now removed. This will accelerate many automatically generated queries, such as those created by object-relational mappers (ORMs).
Ack! Hit send too early.
Note that enhancement addresses only the case where the inner join is unique (ie a primary key or covered by a unique constraint). My understanding of this is that in this case the outer join won't affect the number of rows returned, so if it's not used it's not necessary. Without the unique constraint, or if it's not an outer join it still needs to be included, even if you're not referencing the table in select / conditions because the join affects the number of rows…. HTH.. and I hope I understand this correctly
On Thu, 2011-09-29 at 22:54 -0600, Ben Chobot wrote:
On Sep 29, 2011, at 4:57 PM, Jason Long wrote:
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?This sounds like incorrect logic to me, so I would be surprised if it
was possible.
That is the way it is looking. I just modified my application to
generate the join manually.