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
