Re: Order by and index - Mailing list pgsql-novice

From Jonathan Tse
Subject Re: Order by and index
Date
Msg-id 4345DEC8.7060105@tsez.net
Whole thread Raw
In response to Re: Order by and index  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Order by and index  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-novice
Hi Tom,

    Thanks for your reply, making us aware of the way that postgresql's
query plan.

    One more question, I got an index on ownerid , but postgresql 8.03
still cannot use index to execute the following sql:

    select ownerid,count(ownerid) from t_post group by ownerid order by
ownerid limit 100

    Why is that? Is 8.1 more capable to use the index? Thanks a lot.

Best regards,
Jonathan Tse

Tom Lane wrote:

>Jonathan Tse <dev@tsez.net> writes:
>
>
>>    It is good. However, if I execute the following query:
>>
>>
>
>
>
>>    select * from t_post where ownerid = 2
>>    order by t_stamp
>>
>>
>
>
>
>>    The query planner doesn't use the index for sorting.
>>
>>
>
>Try "order by ownerid, t_stamp".
>
>Postgres 8.1 will recognize that the where clause makes it OK to
>consider the ORDER BY as matching the index order, but existing
>releases do not know that.
>
>            regards, tom lane
>
>



pgsql-novice by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: create table as problem
Next
From: Tom Lane
Date:
Subject: Re: Order by and index