pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Reece Hart <rkh@gene.COM>
To: Ernest E Vogelsinger <ernest@vogelsinger.at>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Cc: pgsql-general <pgsql-general@postgresql.org>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: [GENERAL] Q: Structured index - which one runs faster?
Date: 23 May 2003 09:46:25 -0700
Message-ID: <1053708385.29339.26.camel@tallac> (raw)
In-Reply-To: <5.1.1.6.2.20030522223909.05929600@mail.vogelsinger.at>
References: <5.1.1.6.2.20030522223909.05929600@mail.vogelsinger.at>

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@gene.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@in-machina.com, GPG: 0x25EC91A0




view thread (14+ messages)  latest in thread

Message-ID: <1053708385.29339.26.camel@tallac>
Permalink:  ../1053708385.29339.26.camel@tallac/
Also on:    postgresql.org/message-id/1053708385.29339.26.camel@tallac

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: rkh@gene.COM, ernest@vogelsinger.at, pgsql-admin@postgresql.org, pgsql-general@postgresql.org
  Subject: Re: [GENERAL] Q: Structured index - which one runs faster?
  In-Reply-To: <1053708385.29339.26.camel@tallac>
  References: <5.1.1.6.2.20030522223909.05929600@mail.vogelsinger.at> <1053708385.29339.26.camel@tallac>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox