I recently upgraded to JBoss AS 6.0.0.Final which includes a newer version of Hibernate.
Previously the Postgres dialect was using a comma, but now is is using cross join.
In order do to the migration I had to override the cross join operator to a comma in HIbernate so it would generate the same query.
With the cross join this query never completes. With the comma the query is identical to what was there before and takes less than 300 ms.
The rest of the application seems fine, but this one query is a show stopper.
I have attached the queries below for reference. The only difference is the use of cross join vs comma.
Do you think this is the right way to correct this or should I be looking to tune Postgres to work when cross join is used?
**************************************************************************************
--Hibernate 3.6.0
select count(pipe0_.id) as col_0_0_,
sum(pipe0_.numFeet) as col_1_0_,
sum(pipecalc1_.nt) as col_2_0_,
sum(pipecalc1_.mt) as col_3_0_,
sum(pipe0_1_.numPieces) as col_4_0_,
sum(pipecalc1_.wt100) as col_5_0_
from inventory.t_pipe pipe0_
inner join inventory.t_generic_item pipe0_1_ on pipe0_.id=pipe0_1_.id,
public.v_pipe_calc pipecalc1_
cross join state.t_state state4_
cross join property.t_status status5_
cross join state.t_po_pipe popipe6_
inner join state.t_state popipe6_1_ on popipe6_.id=popipe6_1_.id
where
pipe0_.id=pipecalc1_.id and
pipe0_1_.state_id=state4_.id and
state4_.status_id=status5_.id and
pipe0_.poPipe_id=popipe6_.id and
status5_.activeStatus=true and
popipe6_1_.spec=true
--Hibernate 3.3.1
select count(pipe0_.id) as col_0_0_,
sum(pipe0_.numFeet) as col_1_0_,
sum(pipecalc1_.nt) as col_2_0_,
sum(pipecalc1_.mt) as col_3_0_,
sum(pipe0_1_.numPieces) as col_4_0_,
sum(pipecalc1_.wt100) as col_5_0_
from inventory.t_pipe pipe0_
inner join inventory.t_generic_item pipe0_1_ on pipe0_.id=pipe0_1_.id,
public.v_pipe_calc pipecalc1_,
state.t_state state4_,
property.t_status status5_,
state.t_po_pipe popipe6_
inner join state.t_state popipe6_1_ on popipe6_.id=popipe6_1_.id
where pipe0_.id=pipecalc1_.id and
pipe0_1_.state_id=state4_.id and
state4_.status_id=status5_.id and
pipe0_.poPipe_id=popipe6_.id and
status5_.activeStatus=true and
popipe6_1_.spec=true
