X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4641B52809 for ; Mon, 13 Jun 2005 11:18:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30931-06 for ; Mon, 13 Jun 2005 14:18:22 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E3C7552921 for ; Mon, 13 Jun 2005 11:18:21 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j5DEIOdL019467; Mon, 13 Jun 2005 10:18:24 -0400 (EDT) To: Yves Vindevogel Cc: pgsql-performance@postgresql.org Subject: Re: View not using index In-reply-to: References: <9e1745a6fe1ae29c6f1c824128871d36@implements.be> <200506131705.35852.mr-russ@pws.com.au> Comments: In-reply-to Yves Vindevogel message dated "Mon, 13 Jun 2005 09:18:50 +0200" Date: Mon, 13 Jun 2005 10:18:24 -0400 Message-ID: <19466.1118672304@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/287 X-Sequence-Number: 12924 Yves Vindevogel 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