From: Ernest E Vogelsinger <ernest@vogelsinger.at>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: [ADMIN] Q: Structured index - which one runs faster?
Date: Fri, 23 May 2003 01:36:14 +0200
Message-ID: <5.1.1.6.2.20030523012954.0400a730@mail.vogelsinger.at> (raw)
In-Reply-To: <20030522155056.R47562-100000@megazone23.bigpanda.com>
References: <5.1.1.6.2.20030522223909.05929600@mail.vogelsinger.at>
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/
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: ernest@vogelsinger.at, sszabo@megazone23.bigpanda.com
Subject: Re: [ADMIN] Q: Structured index - which one runs faster?
In-Reply-To: <5.1.1.6.2.20030523012954.0400a730@mail.vogelsinger.at>
References: <5.1.1.6.2.20030522223909.05929600@mail.vogelsinger.at> <5.1.1.6.2.20030523012954.0400a730@mail.vogelsinger.at>
* 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