Re: Order by and index

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