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])

Reply via email to