Re: help understanding analyze

On Sat, Dec 09, 2006 at 11:35:39AM +0100, Luca Ferrari wrote:
> 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...

I think you're confused about what CLUSTER does. There's is no such
thing as a "clustered index". An index is always organised in some way,
if it's a b-tree index it has the information of the key in sorted
order. When you cluster a table it rearranges the data so it is in the
same order as the index. But it's not kept that way. The index is kept
sorted but the data is not.

> 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.

If it doesn't say "Index Scan" it's not using the index.

At a guess your table is not big enough to make an index worthwhile. If
your table is only a few pages long, it's just not efficient to lookup
an index first.

If you post the results of EXPLAIN ANALYZE we can tell you for sure.

Have a ncie day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.