[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Large table search question
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2004-05-31 4:53:00
Message-ID: 7188.1085979180 () sss ! pgh ! pa ! us
[Download RAW message or body]

"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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
[prev in list] [next in list] [prev in thread] [next in thread]