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