Re: View not using index

Yves Vindevogel <yves(dot)vindevogel(at)implements(dot)be> writes:
> rvponp=# explain select * from vw_document_pagesperjob limit 10 ;
> QUERY PLAN
> ------------------------------------------------------------------------
> ----------------------
> Limit (cost=82796.59..82796.72 rows=10 width=706)
> -> Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20
> rows=588209 width=706)
> -> Sort (cost=82796.59..84267.11 rows=588209 width=74)
> Sort Key: tblprintjobs.descpages,
> tblprintjobs.documentname
> -> Seq Scan on tblprintjobs (cost=0.00..26428.61
> rows=588209 width=74)
> (5 rows)

In general, putting an ORDER BY inside a view isn't a great idea ---
it's not legal per SQL spec (hence not portable), and it defeats most
forms of optimization of the view.

CVS tip is actually able to do what you wish with the above case, but no
existing release will optimize the view's ORDER BY in light of a LIMIT
that's outside the view.

regards, tom lane