Re: Is there a benefit to CLUSTER when retrieving individual records?

On Tue, May 15, 2012 at 3:58 PM, Robert James <srobertjames(at)gmail(dot)com> wrote:
> I see how CLUSTER can speed up a range query (eg WHERE val < 30),
> because it groups those records in contiguous pages.
>
> What about where I'm only pulling one record back? Eg WHERE user_id =
> 100.  Is there any benefit to a CLUSTER in that case?  Is there
> anything lost if I CLUSTER on a different index?
>
> If my usage is: A large number of single record SELECTs based on
> user_id, and a small number of range queries, usually based on val,
> would I be best off CLUSTERing on val?

I mostly already answered this on your cluster thread but yeah -- in
this case you'd probably want to CLUSTER on val in this case. Lack of
logical tuple grouping on the primary key is one of the big reasons
why *not* to use surrogate keys that everybody tends to overlook.
There are many other good reasons to use surrogates though.

merlin