[GENERAL] 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
Re: [GENERAL] comma vs cross join question
Jason Long mailing.li...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] comma vs cross join question
Jason Long mailing.li...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] comma vs cross join question
On Fri, 2011-04-08 at 14:45 -0400, Tom Lane wrote: Jason Long mailing.li...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
