Jonathan Tse <dev@tsez.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