Re: Slow views

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.