Re: Order by and index

Jonathan Tse <dev(at)tsez(dot)net> writes:
> 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

"Cannot"? Or "chooses not to"? And are you sure the choice is wrong?

A simple test case for me says that a seqscan/hashaggregate plan is
faster than an indexscan/groupaggregate plan for this sort of problem.
It'll depend on many factors, but for a query that necessarily involves
visiting every row of the table, you're seriously in error to think that
an indexscan plan must be a win.

Try playing around with enable_seqscan and related settings to see what
the alternatives really are.

regards, tom lane