Re: [ADMIN] Q: Structured index - which one runs faster?

On Fri, 23 May 2003, Ernest E Vogelsinger wrote:

> Thanks for replying :)
>
> At 01:00 23.05.2003, Stephan Szabo said:
> --------------------[snip]--------------------
> >On Thu, 22 May 2003, Ernest E Vogelsinger wrote:
> >
> >> I need to store an ID value that consists of three numerical elements:
> >> - ident1 char(5)
> >> - ident2 char(5)
> >> - nodeid int4
> >
> >This seems like a somewhat odd key layout, why char(5) for the first
> >two parts if they're numeric as well?
>
> It's not odd - ident1 and ident2 are in fact logical identifiers that _are_
> character values, no numbers.

The reason I mentioned it is that the original said, "three numerical
elements" ;)

> >Also, what kinds of lookups are you going to be doing? Only lookups based
> >on all three parts of the key or will you ever be searching based on parts
> >of the keys?
>
> Hmm. Yes, lookups on parts of the keys will be possible, but only from left
> to right, ident1 having the highest precedence, followed by ident2 and
> finally by nodeid.

The multi-column index helps for those as well, as long as you put the
columns in the precedence order. If they're ordered ident1,ident2,nodeid
then it'll potentially use it for searches on ident1 or ident1 and ident2
if it thinks that the condition is selective enough.