From: Russell Smith <mr-russ@pws.com.au>
To: Yves Vindevogel <yves.vindevogel@implements.be>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: View not using index
Date: Mon, 13 Jun 2005 17:18:59 +1000
Message-ID: <200506131719.00219.mr-russ@pws.com.au> (raw)
In-Reply-To: <7c61b484cf630efdb375ca61f92a68b9@implements.be>
References: <9e1745a6fe1ae29c6f1c824128871d36@implements.be>
<200506131705.35852.mr-russ@pws.com.au>
<7c61b484cf630efdb375ca61f92a68b9@implements.be>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mr-russ@pws.com.au, yves.vindevogel@implements.be
Subject: Re: View not using index
In-Reply-To: <200506131719.00219.mr-russ@pws.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox