X-Original-To: pgsql-performance@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 2AD00925926 for ; Thu, 22 May 2003 21:47:16 -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 46243-06 for ; Thu, 22 May 2003 21:46:45 -0400 (EDT) Received: from relay11.austria.eu.net (relay11.Austria.eu.net [193.154.160.115]) by developer.postgresql.org (Postfix) with ESMTP id BCF61925FE1 for ; Thu, 22 May 2003 19:35:47 -0400 (EDT) Received: from sisyphus.vogelsinger.at (vogelsinger.at [193.154.189.26]) by relay11.austria.eu.net (8.12.9/8.12.0.Beta10) with ESMTP id h4MNZbc8027968; Fri, 23 May 2003 01:35:42 +0200 Message-Id: <5.1.1.6.2.20030523012954.0400a730@mail.vogelsinger.at> X-Sender: homebird@mail.vogelsinger.at X-Mailer: QUALCOMM Windows Eudora Version 5.1.1 Date: Fri, 23 May 2003 01:36:14 +0200 To: Stephan Szabo From: Ernest E Vogelsinger Subject: Re: [ADMIN] Q: Structured index - which one runs faster? Cc: pgsql-performance In-Reply-To: <20030522155056.R47562-100000@megazone23.bigpanda.com> References: <5.1.1.6.2.20030522223909.05929600@mail.vogelsinger.at> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/219 X-Sequence-Number: 1998 Thanks for replying :) At 01:00 23.05.2003, Stephan Szabo said: --------------------[snip]-------------------- >On Thu, 22 May 2003, Ernest E Vogelsinger wrote: > >> I need to store an ID value that consists of three numerical elements: >> - ident1 char(5) >> - ident2 char(5) >> - nodeid int4 > >This seems like a somewhat odd key layout, why char(5) for the first >two parts if they're numeric as well? It's not odd - ident1 and ident2 are in fact logical identifiers that _are_ character values, no numbers. >Generally, you're probably better off with an index on the three columns. >Otherwise either your clients need to composite the value for the varchar >column or the system does in triggers for insert/update. This table will be used by a PHP library accessing it - no direct client intervention (except the developers and they should know what they're doing ;-) >Also, what kinds of lookups are you going to be doing? Only lookups based >on all three parts of the key or will you ever be searching based on parts >of the keys? Hmm. Yes, lookups on parts of the keys will be possible, but only from left to right, ident1 having the highest precedence, followed by ident2 and finally by nodeid. These columns will never be modified once inserted. The only operations these columns will be affected are insert and delete, and lookup of course. I'm not so concerned with delete since this will not happen too often, but inserts will, and lookups of course permanently, and both operations must be as fast as possible, even with gazillions of rows... -- >O Ernest E. Vogelsinger (\) ICQ #13394035 ^ http://www.vogelsinger.at/