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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])