Re: [PERFORM] View not using index

[Yves Vindevogel]

Note the last query below (prev post)
There it does use the index


rvponp=# create type tpJobsPerDay as
rvponp-# ( documentname varchar(1000),
rvponp(# eventdate date,
rvponp(# eventtime time,
rvponp(# loginuser varchar(255),
rvponp(# pages varchar(20)
rvponp(# ) ;
CREATE TYPE
rvponp=# create function fnJobsPerDay (bigint, bigint) returns setof tpJobsPerDay as
rvponp-# '
rvponp'# select documentname, eventdate, eventtime, loginuser, fnFormatInt(pages) as pages
rvponp'# from tblPrintjobs order by descpages, documentname
rvponp'# offset $1 limit $2 ;
rvponp'# ' language 'sql' ;
CREATE FUNCTION

rvponp=# analyze ;
ANALYZE
rvponp=# explain select * from fnJobsperday (1, 10) ;
QUERY PLAN
-----------------------------------------------------------------------
Function Scan on fnjobsperday (cost=0.00..12.50 rows=1000 width=697)
(1 row)


With the function, it still is very slow. I can't see anything in the explain here, but it seems to be using a table scan.

On 13 Jun 2005, at 09:18, Yves Vindevogel wrote:

rvponp=# explain select * from vw_document_pagesperjob ;
QUERY PLAN
----------------------------------------------------------------------------------------
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)
(4 rows)

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)

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)

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)


<x-tad-bigger>create or replace view vw_document_pagesperjob as
select documentname, eventdate, eventtime, loginuser,
fnFormatInt(pages) as pages
from tblPrintjobs
order by descpages, documentname ;
</x-tad-bigger>





On 13 Jun 2005, at 09:05, Russell Smith wrote:

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

Met vriendelijke groeten,
Bien à vous,
Kind regards,

Yves Vindevogel
Implements

<Pasted Graphic 2.tiff>

Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91

Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76

Web: http://www.implements.be
<x-tad-smaller>
First they ignore you. Then they laugh at you. Then they fight you. Then you win.
Mahatma Ghandi.</x-tad-smaller>

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

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

Met vriendelijke groeten,
Bien à vous,
Kind regards,

Yves Vindevogel
Implements

<<inline: Pasted Graphic 2.tiff>>

Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91

Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76

Web: http://www.implements.be
<x-tad-smaller>
First they ignore you. Then they laugh at you. Then they fight you. Then you win.
Mahatma Ghandi.</x-tad-smaller>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])