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

On Thu, 22 May 2003, Ernest E Vogelsinger wrote:

[response only to -performance]

> sorry for reposting this to the lists, but I feel I posted this at the
> wrong time of day, since now a lot more of you gurus are reading, and I
> really need some knowledgeable input... thanks for consideration :)

It just takes time. :)

> I have a question concerning table/key layout.
>
> 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?

> I need an index on these columns. Insert, delete, and lookup operations
> this in this need to be as fast as possible. Now I have two options:
>
> (a) creating an index on all three columns, or
> (b) create a single varchar column combining all three components into a
> single string, like "ident1:ident2:nodeid" and indexing this column only.
>
> There will be a couple of million rows in this table, the values in
> question are not unique.
>
> Which would be faster in your opinion? (a) or (b)?

Generally, you're probably better off with an index on the three columns.
Otherwise either your clients need to composite the value for the varchar
column or the system does in triggers for insert/update.

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?