comma vs cross join question - Mailing list pgsql-general

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