Re: Limit clause not using index - Mailing list pgsql-performance

Nevermind guys ....

There's an error in a function that is creating these indexes.

The function never completed succesfully so the index is not there


Very sorry about this !!



On 21 Jun 2005, at 16:57, Yves Vindevogel wrote:


<excerpt><fixed><x-tad-bigger>These are my indexes


    create index ixprintjobsapplicationtype on tblPrintjobs
(applicationtype);

    create index ixprintjobsdesceventdate on tblPrintjobs (desceventdate);

    create index ixprintjobsdesceventtime on tblPrintjobs (desceventtime);

    create index ixprintjobsdescpages on tblPrintjobs (descpages);

    create index ixprintjobsdocumentname on tblPrintjobs (documentname) ;

    create index ixprintjobseventcomputer on tblPrintjobs
(eventcomputer);

    create index ixprintjobseventdate on tblPrintjobs (eventdate);

    create index ixprintjobseventtime on tblPrintjobs (eventtime);

    create index ixprintjobseventuser on tblPrintjobs (eventuser);

    create index ixprintjobshostname on tblPrintjobs (hostname) ;

    create index ixprintjobsipaddress on tblPrintjobs (ipaddress) ;

    create index ixprintjobsloginuser on tblPrintjobs (loginuser) ;

    create index ixprintjobspages on tblPrintjobs (pages) ;

    create index ixprintjobsprintport on tblPrintjobs (printport) ;

    create index ixprintjobsprintqueue on tblPrintjobs (printqueue) ;

    create index ixprintjobsrecordnumber on tblPrintjobs (recordnumber) ;

    create index ixprintjobssize on tblPrintjobs (size) ;

    create index ixprintjobsusertype on tblPrintjobs (usertype) ;

    create index ixPrintjobsDescpagesDocumentname on tblPrintjobs
(descpages, documentname) ;

    create index ixPrintjobsHostnamePrintqueueDesceventdateDesceventtime
on tblPrintjobs (hostname, printqueue, desceventdate, desceventtime) ;

    create index ixPrintjobsLoginDescEventdateDesceventtime on
tblPrintjobs (loginuser, desceventdate, desceventtime) ;

</x-tad-bigger></fixed>


On 21 Jun 2005, at 16:42, Tom Lane wrote:


<excerpt>Yves Vindevogel <<yves.vindevogel@implements.be> writes:

<excerpt>Can anyone explain me this ?

</excerpt>

<excerpt>rvponp=# explain select * from tblprintjobs order by
loginuser,

desceventdate, desceventtime offset 25 limit 25 ;

                                     QUERY PLAN

------------------------------------------------------------------------

-----------

  Limit  (cost=349860.62..349860.68 rows=25 width=206)

    ->  Sort  (cost=349860.56..351416.15 rows=622236 width=206)

          Sort Key: loginuser, desceventdate, desceventtime

          ->  Seq Scan on tblprintjobs  (cost=0.00..25589.36
rows=622236 width=206)

(4 rows)

</excerpt>


Do you have an index matching that sort key?  I'd certainly expect the

above to use it if it were there.  For the full table case it's not so

clear --- an indexscan isn't always better.


            regards, tom lane



</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 8: explain analyze is your friend


</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>Nevermind guys ....
There's an error in a function that is creating these indexes.
The function never completed succesfully so the index is not there

Very sorry about this !!


On 21 Jun 2005, at 16:57, Yves Vindevogel wrote:

> These are my indexes
>
>     create index ixprintjobsapplicationtype on tblPrintjobs
> (applicationtype);
>     create index ixprintjobsdesceventdate on tblPrintjobs (desceventdate);
>     create index ixprintjobsdesceventtime on tblPrintjobs (desceventtime);
>     create index ixprintjobsdescpages on tblPrintjobs (descpages);
>     create index ixprintjobsdocumentname on tblPrintjobs (documentname) ;
>     create index ixprintjobseventcomputer on tblPrintjobs (eventcomputer);
>     create index ixprintjobseventdate on tblPrintjobs (eventdate);
>     create index ixprintjobseventtime on tblPrintjobs (eventtime);
>     create index ixprintjobseventuser on tblPrintjobs (eventuser);
>     create index ixprintjobshostname on tblPrintjobs (hostname) ;
>     create index ixprintjobsipaddress on tblPrintjobs (ipaddress) ;
>     create index ixprintjobsloginuser on tblPrintjobs (loginuser) ;
>     create index ixprintjobspages on tblPrintjobs (pages) ;
>     create index ixprintjobsprintport on tblPrintjobs (printport) ;
>     create index ixprintjobsprintqueue on tblPrintjobs (printqueue) ;
>     create index ixprintjobsrecordnumber on tblPrintjobs (recordnumber) ;
>     create index ixprintjobssize on tblPrintjobs (size) ;
>     create index ixprintjobsusertype on tblPrintjobs (usertype) ;
>     create index ixPrintjobsDescpagesDocumentname on tblPrintjobs
> (descpages, documentname) ;
>     create index ixPrintjobsHostnamePrintqueueDesceventdateDesceventtime
> on tblPrintjobs (hostname, printqueue, desceventdate, desceventtime) ;
>     create index ixPrintjobsLoginDescEventdateDesceventtime on
> tblPrintjobs (loginuser, desceventdate, desceventtime) ;
>
>
> On 21 Jun 2005, at 16:42, Tom Lane wrote:
>
>> Yves Vindevogel <yves.vindevogel@implements.be> writes:
>>> Can anyone explain me this ?
>>
>>> rvponp=# explain select * from tblprintjobs order by loginuser,
>>> desceventdate, desceventtime offset 25 limit 25 ;
>>>                                      QUERY PLAN
>>> ---------------------------------------------------------------------
>>> ---
>>> -----------
>>>   Limit  (cost=349860.62..349860.68 rows=25 width=206)
>>>     ->  Sort  (cost=349860.56..351416.15 rows=622236 width=206)
>>>           Sort Key: loginuser, desceventdate, desceventtime
>>>           ->  Seq Scan on tblprintjobs  (cost=0.00..25589.36
>>> rows=622236 width=206)
>>> (4 rows)
>>
>>
>> Do you have an index matching that sort key?  I'd certainly expect the
>> above to use it if it were there.  For the full table case it's not so
>> clear --- an indexscan isn't always better.
>>
>>             regards, tom lane
>>
>>
> 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 8: explain analyze is your friend
>
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.