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

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

From: Reece Hart <rkh(at)gene(dot)COM>
To: Ernest E Vogelsinger <ernest(at)vogelsinger(dot)at>
Cc: pgsql-admin <pgsql-admin(at)postgresql(dot)org>,pgsql-general <pgsql-general(at)postgresql(dot)org>,pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: [GENERAL] Q: Structured index - which one runs faster?
Date: 2003-05-23 16:46:25
Message-ID: 1053708385.29339.26.camel@tallac (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-generalpgsql-performance
Ernest-

> (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.

I'd go with (a).  (b) is not very flexible (e.g., lookup by ident2
only), and any speed advantage will require knowing in advance the
optimal key order (i1:i2:n v. n:i2:i1 v. ...).  I'd expect it would be
comparable to a multi-column index for speed.

(a) can really be implemented in 3 ways:
(a1) an index of all 3 columns
(a2) an index on /each/ of 3 columns
(a3) a multi-column index AND separate indices on the others.
     e.g., index (i1,i2,n), and index (i2) and index (n)

The choice of which is fastest depends a lot on the distribution of keys
in each column and whether you need to do lookups on only one or two
columns.  Again, once you choose (b), you're kinda stuck with treating
the compound key as a single entity (without incurring a big performance
hit); (a) will allow you to experiment with optimal indexing without
affecting code.

Since it sounds like you've already got the data loaded, I (probably
others) would be interested in any timing runs you do.

-Reece

-- 
Reece Hart, Ph.D.                       rkh(at)gene(dot)com, http://www.gene.com/
Genentech, Inc.                         650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93                        http://www.in-machina.com/~reece/
South San Francisco, CA  94080-4990     reece(at)in-machina(dot)com, GPG: 0x25EC91A0


In response to

pgsql-performance by date

Next:From: Bruno Wolff IIIDate: 2003-05-23 16:50:20
Subject: Re: [ADMIN] Q: Structured index - which one runs faster?
Previous:From: scott.marloweDate: 2003-05-23 16:13:47
Subject: Re: postgres on a beowulf? (AMD)opteron?

pgsql-admin by date

Next:From: Bruno Wolff IIIDate: 2003-05-23 16:50:20
Subject: Re: [ADMIN] Q: Structured index - which one runs faster?
Previous:From: David F. SkollDate: 2003-05-23 16:23:22
Subject: Re: upgrade issue

pgsql-general by date

Next:From: Bruno Wolff IIIDate: 2003-05-23 16:50:20
Subject: Re: [ADMIN] Q: Structured index - which one runs faster?
Previous:From: alex b.Date: 2003-05-23 16:41:30
Subject: Re: caching query results

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