Re: Slow views

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
takes the same time as using a view.  It's clearly an optimisation
issue:

select * from a join b optimises differently to select * from a join
(select * from b) as b

It's a pity because views express the code more clearly, but I can't use
them because they're just too slow.