Обсуждение: Limit clause not using index

Поиск
Список
Период
Сортировка

Limit clause not using index

От
Yves Vindevogel
Дата:
Hi,


I have a very simple query on a big table.  When I issue a "limit"
and/or "offset" clause, the query is not using the index.

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)


rvponp=# explain select * from tblprintjobs order by loginuser,
desceventdate, desceventtime ;

                                 QUERY PLAN

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

 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)

(3 rows)


Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>Hi,

I have a very simple query on a big table.  When I issue a "limit"
and/or "offset" clause, the query is not using the index.
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)

rvponp=# explain select * from tblprintjobs order by loginuser,
desceventdate, desceventtime ;
                                  QUERY PLAN
------------------------------------------------------------------------
-----
  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)
(3 rows)

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.
Вложения

Re: Limit clause not using index

От
Bricklen Anderson
Дата:
Yves Vindevogel wrote:
> Hi,
>
> rvponp=# explain select * from tblprintjobs order by loginuser,
> desceventdate, desceventtime offset 25 limit 25 ;
>
> I have a very simple query on a big table.  When I issue a "limit"
> and/or "offset" clause, the query is not using the index.
> Can anyone explain me this ?

Do you have an index on (loginuser,desceventdate,desceventtime)?

--
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________

Re: Limit clause not using index

От
John A Meinel
Дата:
Yves Vindevogel wrote:

> Hi,
>
> I have a very simple query on a big table. When I issue a "limit"
> and/or "offset" clause, the query is not using the index.
> Can anyone explain me this ?

You didn't give enough information. What does you index look like that
you are expecting it to use?
Generally, you want to have matching columns. So you would want
CREATE INDEX blah ON tblprintjobs(loginuser, desceventdate, desceventtime);

Next, you should post EXPLAIN ANALYZE instead of regular explain, so we
can have an idea if the planner is actually making correct estimations.

John
=:->

>
> 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)
>
> rvponp=# explain select * from tblprintjobs order by loginuser,
> desceventdate, desceventtime ;
> QUERY PLAN
> -----------------------------------------------------------------------------
>
> 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)
> (3 rows)
>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> *Yves Vindevogel*
> *Implements*
Вложения

Re: Limit clause not using index

От
Tom Lane
Дата:
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

Re: Limit clause not using index

От
Yves Vindevogel
Дата:
<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.
Вложения

Re: Limit clause not using index

От
Yves Vindevogel
Дата:
rvponp=# explain analyze select * from tblPrintjobs order by
loginuser, desceventdate, desceventtime ;

                                                           QUERY PLAN
        


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

 Sort  (cost=345699.06..347256.49 rows=622972 width=203) (actual
time=259438.952..268885.586 rows=622972 loops=1)

   Sort Key: loginuser, desceventdate, desceventtime

   ->  Seq Scan on tblprintjobs  (cost=0.00..25596.72 rows=622972
width=203) (actual time=21.155..8713.810 rows=622972 loops=1)

 Total runtime: 271583.422 ms

(4 rows)


On 21 Jun 2005, at 16:42, John A Meinel wrote:


<excerpt>Yves Vindevogel wrote:


<excerpt>Hi,


I have a very simple query on a big table. When I issue a "limit"
and/or "offset" clause, the query is not using the index.

Can anyone explain me this ?

</excerpt>

You didn't give enough information. What does you index look like that
you are expecting it to use?

Generally, you want to have matching columns. So you would want

CREATE INDEX blah ON tblprintjobs(loginuser, desceventdate,
desceventtime);


Next, you should post EXPLAIN ANALYZE instead of regular explain, so
we can have an idea if the planner is actually making correct
estimations.


John

=:->


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


rvponp=# explain select * from tblprintjobs order by loginuser,
desceventdate, desceventtime ;

QUERY PLAN

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

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)

(3 rows)


Met vriendelijke groeten,

Bien à vous,

Kind regards,


*Yves Vindevogel*

*Implements*

</excerpt>




</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>rvponp=# explain analyze select * from tblPrintjobs order by loginuser,
desceventdate, desceventtime ;
                                                            QUERY PLAN
------------------------------------------------------------------------
--------------------------------------------------------
  Sort  (cost=345699.06..347256.49 rows=622972 width=203) (actual
time=259438.952..268885.586 rows=622972 loops=1)
    Sort Key: loginuser, desceventdate, desceventtime
    ->  Seq Scan on tblprintjobs  (cost=0.00..25596.72 rows=622972
width=203) (actual time=21.155..8713.810 rows=622972 loops=1)
  Total runtime: 271583.422 ms
(4 rows)

On 21 Jun 2005, at 16:42, John A Meinel wrote:

> Yves Vindevogel wrote:
>
>> Hi,
>>
>> I have a very simple query on a big table. When I issue a "limit"
>> and/or "offset" clause, the query is not using the index.
>> Can anyone explain me this ?
>
> You didn't give enough information. What does you index look like that
> you are expecting it to use?
> Generally, you want to have matching columns. So you would want
> CREATE INDEX blah ON tblprintjobs(loginuser, desceventdate,
> desceventtime);
>
> Next, you should post EXPLAIN ANALYZE instead of regular explain, so
> we can have an idea if the planner is actually making correct
> estimations.
>
> John
> =:->
>
>>
>> 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)
>>
>> rvponp=# explain select * from tblprintjobs order by loginuser,
>> desceventdate, desceventtime ;
>> QUERY PLAN
>> ----------------------------------------------------------------------
>> -------
>> 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)
>> (3 rows)
>>
>> Met vriendelijke groeten,
>> Bien à vous,
>> Kind regards,
>>
>> *Yves Vindevogel*
>> *Implements*
>
>
>
>
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.
Вложения

Re: Limit clause not using index

От
Yves Vindevogel
Дата:
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.
Вложения

Re: Limit clause not using index

От
John A Meinel
Дата:
Yves Vindevogel wrote:

> rvponp=# explain analyze select * from tblPrintjobs order by
> loginuser, desceventdate, desceventtime ;
> QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------------
>
> Sort (cost=345699.06..347256.49 rows=622972 width=203) (actual
> time=259438.952..268885.586 rows=622972 loops=1)
> Sort Key: loginuser, desceventdate, desceventtime
> -> Seq Scan on tblprintjobs (cost=0.00..25596.72 rows=622972
> width=203) (actual time=21.155..8713.810 rows=622972 loops=1)
> Total runtime: 271583.422 ms
> (4 rows)


Can you post it with the limit? I realize the query takes a long time,
but that is the more important query to look at.

Also, just as a test, if you can, try dropping most of the indexes
except for the important one. It might be that the planner is having a
hard time because there are too many permutations to try.
I believe if you drop the indexes inside a transaction, they will still
be there for other queries, and if you rollback instead of commit, you
won't lose anything.

BEGIN;
DROP INDEX ...
EXPLAIN ANALYZE SELECT *...
ROLLBACK;

John
=:->
Вложения

Re: Limit clause not using index

От
Tom Lane
Дата:
Yves Vindevogel <yves.vindevogel@implements.be> writes:
>     create index ixPrintjobsLoginDescEventdateDesceventtime on
> tblPrintjobs (loginuser, desceventdate, desceventtime) ;

Hmm, that certainly looks like it should match the query.  What happens
to the EXPLAIN output if you do "set enable_sort = false"?

            regards, tom lane

Re: Limit clause not using index

От
Tobias Brox
Дата:
[John A Meinel - Tue at 10:14:24AM -0500]
> I believe if you drop the indexes inside a transaction, they will still
> be there for other queries, and if you rollback instead of commit, you
> won't lose anything.

Has anyone tested this?

(sorry, I only have the production database to play with at the moment,
and I don't think I should play with it ;-)

--
Tobias Brox, Beijing

Re: Limit clause not using index

От
Michael Fuhr
Дата:
On Tue, Jun 21, 2005 at 09:46:39PM +0200, Tobias Brox wrote:
> [John A Meinel - Tue at 10:14:24AM -0500]
> > I believe if you drop the indexes inside a transaction, they will still
> > be there for other queries, and if you rollback instead of commit, you
> > won't lose anything.
>
> Has anyone tested this?

Observations from tests with 8.0.3:

DROP INDEX acquires an AccessExclusiveLock on the table and on the
index.  This will cause the transaction executing the DROP INDEX
to block until no other transaction holds any kind of lock on either,
and once the locks are acquired, no other transaction will be able
to access the table or the index until the transaction doing the
DROP INDEX commits or rolls back.  Rolling back leaves the index
in place.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Limit clause not using index

От
Tom Lane
Дата:
Tobias Brox <tobias@nordicbet.com> writes:
> [John A Meinel - Tue at 10:14:24AM -0500]
>> I believe if you drop the indexes inside a transaction, they will still
>> be there for other queries, and if you rollback instead of commit, you
>> won't lose anything.

> Has anyone tested this?

Certainly.  Bear in mind though that DROP INDEX will acquire exclusive
lock on the index's table, so until you roll back, no other transaction
will be able to touch the table at all.  So the whole thing may be a
nonstarter in a production database anyway :-(.  You can probably get
away with
    BEGIN;
    DROP INDEX ...
    EXPLAIN ...
    ROLLBACK;
if you fire it from a script rather than by hand --- but EXPLAIN
ANALYZE might be a bad idea ...

            regards, tom lane

Re: Limit clause not using index

От
Tobias Brox
Дата:
[Tom Lane - Tue at 05:20:07PM -0400]
>
> Certainly.  Bear in mind though that DROP INDEX will acquire exclusive
> lock on the index's table, so until you roll back, no other transaction
> will be able to touch the table at all.  So the whole thing may be a
> nonstarter in a production database anyway :-(.

That's what I was afraid of.  I was running psql at the production DB
without starting a transaction (bad habit, I know) and tried to drop an
index there, but I had to cancel the transaction, it took forever and
in the same time blocking all the revenue-generating activity.

--
Tobias Brox, +86-13521622905
Nordicbet, IT dept

Re: Limit clause not using index

От
"Kevin Grittner"
Дата:
I just tried this on 8.0.3.  A query which runs very fast through an
index on a 25 million row table blocked when I dropped the index within
a database transaction.  As soon as I rolled back the database
transactiton, the query completed, the index appears fine, and the query
runs fast, as usual.

So, it looks like this is right except for the assertion that the index
is still available for other queries.

-Kevin


>>> Tobias Brox <tobias@nordicbet.com> 06/21/05 2:46 PM >>>
[John A Meinel - Tue at 10:14:24AM -0500]
> I believe if you drop the indexes inside a transaction, they will
still
> be there for other queries, and if you rollback instead of commit, you
> won't lose anything.

Has anyone tested this?

(sorry, I only have the production database to play with at the moment,
and I don't think I should play with it ;-)

--
Tobias Brox, Beijing


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly