Thread: Q: Structured index - which one runs faster?
Hi all,
sorry for reposting this to the lists, but I feel I posted this at the
wrong time of day, since now a lot more of you gurus are reading, and I
really need some knowledgeable input... thanks for consideration :)
I have a question concerning table/key layout.
I need to store an ID value that consists of three numerical elements:
- ident1 char(5)
- ident2 char(5)
- nodeid int4
I need an index on these columns. Insert, delete, and lookup operations
this in this need to be as fast as possible. Now I have two options:
(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.
Which would be faster in your opinion? (a) or (b)?
Thanks for any insight,
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
On Thu, 22 May 2003, Ernest E Vogelsinger wrote: > Hi all, > > sorry for reposting this to the lists, but I feel I posted this at the > wrong time of day, since now a lot more of you gurus are reading, and I > really need some knowledgeable input... thanks for consideration :) > > > I have a question concerning table/key layout. > > I need to store an ID value that consists of three numerical elements: > - ident1 char(5) > - ident2 char(5) > - nodeid int4 > > I need an index on these columns. Insert, delete, and lookup operations > this in this need to be as fast as possible. Now I have two options: > > (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. > > Which would be faster in your opinion? (a) or (b)? Generally speaking, b should be faster, but a should be more versatile.
On Thu, 22 May 2003, Ernest E Vogelsinger wrote: [response only to -performance] > sorry for reposting this to the lists, but I feel I posted this at the > wrong time of day, since now a lot more of you gurus are reading, and I > really need some knowledgeable input... thanks for consideration :) It just takes time. :) > I have a question concerning table/key layout. > > 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? > I need an index on these columns. Insert, delete, and lookup operations > this in this need to be as fast as possible. Now I have two options: > > (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. > > Which would be faster in your opinion? (a) or (b)? 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. 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?
Ernest E Vogelsinger <ernest@vogelsinger.at> writes:
> (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.
I can't imagine that (b) is a good idea ... it's dubious that you are
saving anything on the indexing, and you're sure adding a lot of space
to the table, not to mention maintenance effort, potential for bugs,
etc.
It might be worth creating the index so that the "least non-unique"
column is mentioned first, if there's a clear winner in those terms.
That would minimize the number of times that comparisons have to look at
the additional columns.
regards, tom lane
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/
On Fri, 23 May 2003, Ernest E Vogelsinger wrote: > 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. The reason I mentioned it is that the original said, "three numerical elements" ;) > >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. The multi-column index helps for those as well, as long as you put the columns in the precedence order. If they're ordered ident1,ident2,nodeid then it'll potentially use it for searches on ident1 or ident1 and ident2 if it thinks that the condition is selective enough.
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