Re: View not using index

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