On Saturday 09 December 2006 03:48 Tom Lane's cat, walking on the keyboard,
wrote:
> 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.
And when the cluster is rebuilt? I mean, in theory, a clustered index should
be sorted at any point in time, that means I've got much cost for
insert/update cause I need to sort again the index when I'm performing the
insert/update, isn't it? For me, at least in theory, a clustered index is
always sorted. Now, assuming that my table is not changing (the number of
people hired/fired is very low!), it makes sense to me use a clustered index
cause I should not have the cost of insert/update but should have better
performances. Maybe I cannot understand something...
>
> 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.
I did run analyze, and the explain shows me the seq scan and then a sort. The
only difference I've seen between a only vacuum and a analyze is that the
seq. scan cost changes, but the final cost (i.e., seq. scan and sort) is the
same either with or without the index. This is the point I cannot understand.
And of course, as you stated, the problem is that the system is not
considering the created index (of course I can suggest it within the select
statement), and I don't know why.
Any explaination?
Thanks,
Luca