From: | Luca Ferrari <fluca1978(at)infinito(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: help understanding analyze |
Date: | 2006-12-09 10:35:39 |
Message-ID: | 200612091135.39453.fluca1978@infinito.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2006-12-09 10:46:01 | Re: help understanding analyze |
Previous Message | Marcus Engene | 2006-12-09 09:51:50 | Re: TOAD-like query builder for PostgreSQL? |