Thanks for your answer. So, basically, what you are saying is that there is nothing particularly wrong with the query, nor with its optimization ? So if I need performance for this query, I should just revert to other techniques (giving more memory to postgres, caching outside postgres, etc..) ? Regards, Sami Dalouche Le jeudi 01 novembre 2007 à 09:29 -0400, Tom Lane a écrit : > Sami Dalouche <skoobi@xxxxxxx> 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 2: Don't 'kill -9' the postmaster
