Re: Is there a benefit to CLUSTER when retrieving individual records? - Mailing list pgsql-general

From Merlin Moncure
Subject Re: Is there a benefit to CLUSTER when retrieving individual records?
Date
Msg-id CAHyXU0zMqK3nHi-_SrnpmeEee=ru0v_rF4dzvBLS+qUgp0CZsg@mail.gmail.com
Whole thread Raw
In response to Is there a benefit to CLUSTER when retrieving individual records?  (Robert James <srobertjames@gmail.com>)
List pgsql-general
On Tue, May 15, 2012 at 3:58 PM, Robert James <srobertjames@gmail.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

pgsql-general by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: Disadvantage to CLUSTER?
Next
From: Steve Crawford
Date:
Subject: Re: Disadvantage to CLUSTER?