Sami Dalouche <skoobi@free.fr> writes: > -- For some reason, my message doesn't seem to go through the mailing > list, so I am trying without any attachment Please don't do that, at least not that way. These explain outputs have been line-wrapped to the point of utter unreadability. The main problem looks to me that you're trying to do a 25-way join. You'll want to increase join_collapse_limit and maybe fool with the geqo parameters. I fear you won't get a plan in a sane amount of time if you try to do the full query as a single exhaustive search. You can either raise join_collapse_limit all the way and trust geqo to find a decent plan repeatably (not a real safe assumption unfortunately) or raise both join_collapse_limit and geqo_threshold to some intermediate level and hope that a slightly wider partial plan search will find the plan you need. It's also possible that you're just stuck and the outer join is inherently harder to execute. I didn't study the query closely enough to see if it's joining to any left join right-hand-sides, or anything else that would forbid picking a nice join order. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Sami Dalouche <skoobi@free.fr> writes: > Compare that to the following query, that is exactly the same except > that the City table is inner'joined instead of outer joined > ... > the explain analyze is available at : > http://www.photosdesami.com/temp/exp6.txt AFAICS it's just absolutely blind luck that that query is fast. The planner chooses to do the contactinf7_/city8_ join first, and because that happens to return no rows at all, all the rest of the query falls out in no time, even managing to avoid the scan of adcreatedevent. If there were any rows out of that join it would be a great deal slower. There is a pretty significant semantic difference between the two queries, too, now that I look closer: when you make "... join City city8_ on contactinf7_.city_id=city8_.id" a plain join instead of left join, that means the join to contactinf7_ can be reduced to a plain join as well, because no rows with nulls for contactinf7_ could possibly contribute to the upper join's result. That optimization doesn't apply in the original form of the query, which restricts the planner's freedom to rearrange things. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org