On 5/15/12, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> On 05/15/2012 02:02 PM, Robert James wrote:
>> Besides the one time spent CLUSTERing, do I loose anything by doing it
>> for every table? Does a CLUSTER slow anything down?
> Cluster should have better performance but it depends on the index you
> choose relative to the queries you typically run. Let's say that you
> have an accounting system where you most often grab the most recent
> month worth of data. Clustering that keeps that data together will be
> beneficial but you could easily have a different index, item-number for
> instance, that would, if used for clustering, leave the commonly used
> data scattered throughout the table. If that table was an append-only
> detail table the most commonly used data would naturally clump together
> which clustering would then destroy.
Okay, I understand why we still need VACUUM and why we can't always
CLUSTER. But my question remains: assuming I have some down time, do
I loose anything by CLUSTER. Your answer is, I believe: Not normally,
but there is one case where you do. That's an append-only table,
where you're generally interested in the most recent data, but you
cluster on something else.
Does clustering really hurt in that case? Is the planner smart enough
to realize that the data you want is towards the end only? I would
think that it doesn't know this, and will, let's say, assume it is
scattered regardless and perform a full scan. I guess the question
is: Does the natural order of data help if there's no explicit means
for the planner to prove it?