Skip site navigation (1) Skip section navigation (2)

Re: Large table search question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "John Wells" <jb(at)sourceillustrated(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Large table search question
Date: 2004-05-31 04:53:00
Message-ID: 7188.1085979180@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
"John Wells" <jb(at)sourceillustrated(dot)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

In response to

pgsql-general by date

Next:From: Jurgen DefurneDate: 2004-05-31 05:28:19
Subject: Re: Error handling in stored functions/procedures
Previous:From: Tom LaneDate: 2004-05-31 04:30:58
Subject: Re: Backend crash

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group