Re: Large table search question - Mailing list pgsql-general

I don't want to but in, I just find this an interesting discussion and would like to add my 2 cents:

I have read this in the manual: (PostgreSQL 7.4beta4 documentation, Chapter 11.3 Multicolumn Indexes)
Qoute:
"Multicolumn indexes should be used sparingly. Most of the time, an index on a single column is sufficient and saves
spaceand time. Indexes with more than three columns are unlikely to be helpful unless the usage of the table is
extremelystylized." 
This makes me think of the usefullness in means of performance off multi-column indices. Furthermore it states that
mulicolumnindeces will only be used by the planner if the fields of the index are used with the AND operator in the
whereclause of your select. (Same chapter). 

We had a table with 6million+ records and a few tests with explain reveiled that none of the multi-column indeces where
actuallyused! This while regualar analyzes where done, and the data never changes (no mutations). 

I don't seem to grasp the full meaning of the above. Am I better of using several single field indices, or do
mulitcolumnindices offer an advantage? If so in which case? Just made me wander... 

Regards,

Stijn Vanroye

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: dinsdag 1 juni 2004 10:44
To: John Wells
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Large table search question[Scanned]


John Wells wrote:
> Guys,
>
> I have a general question about designing databases for large data sets.
>
> I was speaking with a colleague about an application we're preparing to
> build.  One of the application's tables will potentially contain 2 million
> or more names, containing (at least) the fields first_name, last_name,
> middle_name and prefix.
>
> 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.

Might, might not. No figures to back up his argument. It'll certainly
make updates slower and less efficient. In fact, since each row will
store the data twice you'll get less rows per disk-page which means
(potentially) more disk reads when you need to get several rows.

> I agree with his assertion, but get the feeling that this is sort of an
> ugly design.  Would a compound index on these fields really be less
> efficient?

Doubtful, I'd certainly not try his solution until I'd tried the simple
way first.

If you really want to try your friend's approach on PG you can build a
functional index. As of 7.4, these can be expressions rather than just
indexes so you can do something like:

CREATE INDEX my_idx_1 ON table1 ( prefix || ' ' || first_name ...);

If you're using 7.3.x you'll need to wrap that expression in a function
and index the function instead.

In your case though, I'd just build a compound index and leave it at that.

--
   Richard Huxton
   Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend