Re: View not using index - Mailing list 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:


<excerpt>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)



<fixed><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></fixed>






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


<excerpt>On Mon, 13 Jun 2005 04:54 pm, Yves Vindevogel wrote:

<excerpt>Still, when I use explain, pg says it will first sort my
tables instead

of using my index

How is that possible ?

</excerpt>

Can we see the output of the explain analyze?

The definition of the view?


Regards


Russell Smith



</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller><<Pasted Graphic 2.tiff><smaller>


Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91


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


Web: http://www.implements.be

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller></excerpt><excerpt>


---------------------------(end of
broadcast)---------------------------

TIP 5: Have you checked our extensive FAQ?


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


</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>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.vindevogel@implements.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

<smaller>


Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91


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


Web: http://www.implements.be

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller>



Mail: yves.vindevogel@implements.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.