Re: Large table search question

"John Wells" <jb@sourceillustrated.com> writes:
> A common lookup the application will require is the full name, so prefix +
> first_name + middle_name + last_name.

> My friend's suggestion was to create a "lookup field" in the table itself,
> which would contain a concatenation of these fields created during insert.
>  So, for each record, we'd having each individual field and then a
> full_name field that would contain the combination of the ind. fields.
> His argument is that this will make lookups in this manner extremely fast
> and efficient.

Not unless you then add an index on that field, which would imply doubly
redundant storage of the data (primary fields, lookup field, lookup
field's index).

You don't actually need the lookup field in Postgres: you can create the
computed index directly.  For instance

    create index fooi on foo ((first_name || middle_name || last_name));

    select * from foo
    where (first_name || middle_name || last_name) = 'JohnQPublic';

This is still kinda grim on storage space, but at least it's 2x not 3x.

            regards, tom lane