Luca Ferrari <fluca1978@infinito.it> writes:
> excuse me for this trivial question, but here's my doubt:
> create table person(varchar id, varchar surname, varchar name)
> with id primary key. Now, the query:
> select * from person order by surname,name
> provide me an explaination that is sequential scan + sort, as I expected.
> After that I build an index on surname,name (clustered) and run vacuum to
> update statistics. Then I ran again the query and got the same results (scan
> + sort) with the same time.
> Now my trivial question is: why another sort? The index is clustered so the
> database should not need to sort the output, or am I using wrong the tools?
Well, CLUSTER does not guarantee that the data remains sorted --- as
soon as you do any updates it won't be anymore. So the planner can
never assume that a plain seqscan delivers correctly sorted output.
The real question you should be asking in the above case is why it
didn't use an indexscan on that index, and the answer is probably
that you didn't ANALYZE. VACUUM does not update the statistics
about index correlation.
regards, tom lane
