X-Original-To: pgsql-admin@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 36BE192506C; Fri, 23 May 2003 12:59:12 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 18064-07; Fri, 23 May 2003 12:59:03 -0400 (EDT) Received: from djinn.gene.com (socks-open.gene.com [192.12.78.2]) by developer.postgresql.org (Postfix) with ESMTP id C1D069234FB; Fri, 23 May 2003 12:46:33 -0400 (EDT) Received: from faraday.gene.com (faraday.gene.com [128.137.49.205]) by djinn.gene.com (8.12.9/8.12.4) with ESMTP id h4NGkQuk374098; Fri, 23 May 2003 09:46:26 -0700 (PDT) Received: from tallac.gene.com (meitner.gene.com [128.137.49.207]) by faraday.gene.com (Switch-3.0.4/Switch-3.0.0) with ESMTP id h4NGkPqV023852; Fri, 23 May 2003 09:46:25 -0700 (PDT) Subject: Re: [GENERAL] Q: Structured index - which one runs faster? From: Reece Hart To: Ernest E Vogelsinger Cc: pgsql-admin , pgsql-general , pgsql-performance In-Reply-To: <5.1.1.6.2.20030522223909.05929600@mail.vogelsinger.at> References: <5.1.1.6.2.20030522223909.05929600@mail.vogelsinger.at> Content-Type: text/plain Organization: Genentech, Inc. Message-Id: <1053708385.29339.26.camel@tallac> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 23 May 2003 09:46:25 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/350 X-Sequence-Number: 8912 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