Thread: comma vs cross join question
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
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
Jason Long <mailing.lists@octgsoftware.com> writes: > 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. > 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. Those should be semantically equivalent AFAICS. Do you maybe have join_collapse_limit set to a smaller-than-default value? If not, are any of those tables really join views? Please see http://wiki.postgresql.org/wiki/SlowQueryQuestions if you need further help, because there's not enough information here to do more than guess wildly. regards, tom lane
Jason Long <mailing.lists@octgsoftware.com> writes:
> I am using 9.0.3 and the only setting I have changed is
> geqo_effort = 10
> One of the joins is a view join.
Ah. The explain shows there are actually nine base tables in that
query, which is more than the default join_collapse_limit. Try cranking
up both join_collapse_limit and from_collapse_limit to 10 or so.
(I'm not sure offhand if from_collapse_limit affects this case, but it
might.)
regards, tom lane
On Fri, 2011-04-08 at 14:45 -0400, Tom Lane wrote: > Jason Long <mailing.lists@octgsoftware.com> writes: > > I am using 9.0.3 and the only setting I have changed is > > geqo_effort = 10 > > > One of the joins is a view join. > > Ah. The explain shows there are actually nine base tables in that > query, which is more than the default join_collapse_limit. Try cranking > up both join_collapse_limit and from_collapse_limit to 10 or so. > (I'm not sure offhand if from_collapse_limit affects this case, but it > might.) > > regards, tom lane I have to say I love this mailing list and thank you Tom for your expertise. I played with the settings with the following results. Worked like a charm from_collapse_limit = 10 join_collapse_limit = 10 Worked like a charm from_collapse_limit = 10 join_collapse_limit = 8 Failed from_collapse_limit = 8 join_collapse_limit = 10 It looks like from_collapse_limit was the key. I am going to leave them both at 10.