Luca Ferrari <[EMAIL PROTECTED]> wrote: > > Hi all, > 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? > Someone can explain me that?
I doubt that the planner has any way to know that the table, at any point in time, is still 100% clustered. If even one row has been added since the cluster was done, the table will need resorted. Might be an optimization that could be done, except that I expect there will be very few cases where it will actually make a difference. How often do you have a table that never changes and can always be assured of being in index order? -Bill ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
