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 EFA765292A for ; Mon, 13 Jun 2005 04:19:14 -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 50362-06 for ; Mon, 13 Jun 2005 07:19:06 +0000 (GMT) Received: from pws.com.au (mail.pws.com.au [210.23.138.139]) by svr1.postgresql.org (Postfix) with SMTP id 5633C52914 for ; Mon, 13 Jun 2005 04:19:04 -0300 (ADT) Received: (qmail 17820 invoked from network); 13 Jun 2005 07:19:05 -0000 Received: from unknown (HELO wizzard.pws.com.au) (russell@pws.com.au@138.217.54.70) by mail.pws.com.au with SMTP; 13 Jun 2005 07:19:05 -0000 From: Russell Smith To: Yves Vindevogel Subject: Re: View not using index Date: Mon, 13 Jun 2005 17:18:59 +1000 User-Agent: KMail/1.7.2 Cc: Postgresql Performance References: <9e1745a6fe1ae29c6f1c824128871d36@implements.be> <200506131705.35852.mr-russ@pws.com.au> <7c61b484cf630efdb375ca61f92a68b9@implements.be> In-Reply-To: <7c61b484cf630efdb375ca61f92a68b9@implements.be> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200506131719.00219.mr-russ@pws.com.au> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.034 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/277 X-Sequence-Number: 12914 Please CC the list. On Mon, 13 Jun 2005 05:11 pm, Yves Vindevogel wrote: > create or replace view vw_document_pagesperjob as > select documentname, eventdate, eventtime, loginuser, > fnFormatInt(pages) as pages > from tblPrintjobs > order by descpages, documentname ; > > rvponp=# explain select documentname, eventdate, eventtime, loginuser, > pages from tblPrintjobs order > by descpages, documentname ; > QUERY PLAN > ------------------------------------------------------------------------ > ---- > Sort (cost=81326.07..82796.59 rows=588209 width=74) > Sort Key: descpages, documentname > -> Seq Scan on tblprintjobs (cost=0.00..24958.09 rows=588209 > width=74) > (3 rows) > Postgresql must scan the entire heap anyway, so ordering in memory will be faster, and you don't have to load the pages from disk in a random order. > rvponp=# explain select documentname, eventdate, eventtime, loginuser, > pages from tblPrintjobs order > by descpages, documentname limit 10 ; > QUERY PLAN > ------------------------------------------------------------------------ > ------------------------------------- > Limit (cost=0.00..33.14 rows=10 width=74) > -> Index Scan using ixprintjobspagesperjob on tblprintjobs > (cost=0.00..1949116.68 rows=588209 width=74) > (2 rows) > That's because an index scan is only useful if you are scanning a small percentage of the table. Which you are doing when you have the limit clause. > Strange thing is, when I immediately add the limit clause, it runs like > I want it to run. I am not sure of the usefulness of the first query anyway, it returns a lot of data. How do you expect it not to scan the whole table when you want all the data form the table? > Problem is that I run this from Cocoon. Cocoon adds the limit clause > itself. > Maybe I need to rewrite everything in functions instead of views. > Functions, views. It will make not difference. The issue is the amount of data returned relative to the amount of data in the table. Regards Russell Smith