SEO

#1 (**permalink**) 04-18-2008, 11:53 AM

On Mon, 13 Jun 2005 04:54 pm, Yves Vindevogel wrote:
> Still, when I use explain, pg says it will first sort my tables instead
> of using my index
> How is that possible ?

Can we see the output of the explain analyze?
The definition of the view?

Regards

Russell Smith

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#2 (**permalink**) 04-18-2008, 11:53 AM

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 majordomo@postgresql.org)

#3 (**permalink**) 04-18-2008, 11:53 AM

Yves Vindevogel <yves.vindevogel@implements.be> writes:
> rvponp=# explain select * from vw_document_pagesperjob limit 10 ;
> QUERY PLAN
> ------------------------------------------------------------------------
> ----------------------
> Limit (cost=82796.59..82796.72 rows=10 width=706)
> -> Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20
> rows=588209 width=706)
> -> Sort (cost=82796.59..84267.11 rows=588209 width=74)
> Sort Key: tblprintjobs.descpages,
> tblprintjobs.documentname
> -> Seq Scan on tblprintjobs (cost=0.00..26428.61
> rows=588209 width=74)
> (5 rows)

In general, putting an ORDER BY inside a view isn't a great idea ---
it's not legal per SQL spec (hence not portable), and it defeats most
forms of optimization of the view.

CVS tip is actually able to do what you wish with the above case, but no
existing release will optimize the view's ORDER BY in light of a LIMIT
that's outside the view.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: View not using index