On Sat, 17 Jul 2004, David Newall wrote: > On Sat, 2004-07-17 at 13:34, Tom Lane wrote: > > I suspect the real issue is that the implied join order is not the same. > > With respect, the real issue is that using the view takes 100 times > longer than not using it. > > > The view-based query is really > > > > a LEFT JOIN (b LEFT JOIN c LEFT JOIN d LEFT JOIN e) > > > > while the allegedly equivalent hand expansion is > > > > a LEFT JOIN b LEFT JOIN c LEFT JOIN d LEFT JOIN e > > Reversing the two terms, ie view right join table, gives the same (slow) > result. Adding a sub-select to the expansion, as Stephan said, gives
I think you're still fundamentally misunderstanding that the two queries above are not always the same. The reason you got better results from your version is that a left join b was faster, however, if the join conditions in your view were more complicated, your version would give the wrong results. Unless/until we know for certain under what conditions we can reorder the joins, we can't do so without causing bugs in other queries. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
