From: | Yves Vindevogel <yves(dot)vindevogel(at)implements(dot)be> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: View not using index |
Date: | 2005-06-13 07:35:47 |
Message-ID: | d0e84110335f1e6d7e4c9b0c0b2b2266@implements.be |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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)
>
>
> create or replace view vw_document_pagesperjob as
> select documentname, eventdate, eventtime, loginuser,
> fnFormatInt(pages) as pages
> from tblPrintjobs
> order by descpages, documentname ;
>
>
>
>
>
>
> 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: yves(dot)vindevogel(at)implements(dot)be - Mobile: +32 (478) 80 82 91
>
> Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
>
> Web: http://www.implements.be
>
> First they ignore you. Then they laugh at you. Then they fight you.
> Then you win.
> Mahatma Ghandi.
>
> ---------------------------(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
Attachment | Content-Type | Size |
---|---|---|
Pasted Graphic 2.tiff | image/tiff | 5.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Kirkwood | 2005-06-13 08:54:23 | Re: Updates on large tables are extremely slow |
Previous Message | Russell Smith | 2005-06-13 07:18:59 | Re: View not using index |