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

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

<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.