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

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

> Hi all,
>
> 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 :)
>
>
> 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
>
> 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 speaking, b should be faster, but a should be more versatile.