Обсуждение: 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.
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
A related question: Are any of these indexes redundant: CREATE UNIQUE INDEX user_list_id_email ON user_list (owner_id,user_email); CREATE INDEX user_list_owner_id ON user_list (owner_id); CREATE INDEX user_list_oid_created ON user_list (owner_id,user_created); In particular, is user_list_owner_id redundant to user_list_oid_created? Will the latter be used for queries such as SELECT user_fname from user_list where owner_id=34 If so, I can drop the owner_id index. the _id columns are integers, created is a datetime, and email is a string. owner_id is also a foreign key into the owners table (via REFERENCES), if that matters. I'd try it out by dropping the index, but reindexing it takes a *LONG* time which I cannot afford to be unavailable. Thanks. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
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
On Fri, May 23, 2003 at 11:09:00 -0400, Vivek Khera <khera@kcilink.com> wrote: > A related question: > > Are any of these indexes redundant: > > CREATE UNIQUE INDEX user_list_id_email ON user_list (owner_id,user_email); > CREATE INDEX user_list_owner_id ON user_list (owner_id); > CREATE INDEX user_list_oid_created ON user_list (owner_id,user_created); > > In particular, is user_list_owner_id redundant to > user_list_oid_created? Will the latter be used for queries such as Yes. Any prefix of a multicolumn index can be used for queries. They (prefixes) won't be usable by foreign key references because even if the index as a whole is unique, the prefixes won't necessarily be.
Vivek Khera <khera@kcilink.com> writes:
> Are any of these indexes redundant:
> CREATE UNIQUE INDEX user_list_id_email ON user_list (owner_id,user_email);
> CREATE INDEX user_list_owner_id ON user_list (owner_id);
> CREATE INDEX user_list_oid_created ON user_list (owner_id,user_created);
> In particular, is user_list_owner_id redundant to
> user_list_oid_created?
Any of the three indexes can be used for a search on owner_id alone, so
yeah, user_list_owner_id is redundant. It would be marginally faster to
use user_list_owner_id for such a search, just because it's physically
smaller than the other two indexes, but against that you have to balance
the extra update cost of maintaining the additional index.
Also, I can imagine scenarios where even a pure SELECT query load could
find the extra index to be a net loss: if you have a mix of queries that
use two or all three indexes, and the indexes don't fit in kernel disk
cache but just one or two would, then you'll lose on extra I/O as the
indexes compete for cache space. Not sure how likely that scenario is,
but it's something to think about.
regards, tom lane
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: >> In particular, is user_list_owner_id redundant to >> user_list_oid_created? TL> Any of the three indexes can be used for a search on owner_id alone, so TL> yeah, user_list_owner_id is redundant. It would be marginally faster to TL> use user_list_owner_id for such a search, just because it's physically TL> smaller than the other two indexes, but against that you have to balance TL> the extra update cost of maintaining the additional index. This is great info. That extra index is gonna be nuked in about 37.23 seconds... It takes up a lot of space and is wasting time with updates and inserts, which happen a *lot* on that table (nearly 10 million rows). Thanks!
On 23 May 2003 11:09:00 -0400, Vivek Khera <khera@kcilink.com> wrote: > CREATE UNIQUE INDEX user_list_id_email ON user_list (owner_id,user_email); > CREATE INDEX user_list_owner_id ON user_list (owner_id); > CREATE INDEX user_list_oid_created ON user_list (owner_id,user_created); > >In particular, is user_list_owner_id redundant to >user_list_oid_created? In theory yes, but in practice it depends ... > Will the latter be used for queries such as > > SELECT user_fname from user_list where owner_id=34 All other things being equal, the planner tends to estimate higher costs for the multi column index. This has to do with its attempt to adjust correlation for the additional index columns. So unless the physical order of tuples is totally unrelated to owner_id, I'd expect it to choose the single column index. >If so, I can drop the owner_id index. If the planner estimates the cost for an user_list_id_email or user_list_oid_created index scan lower than for a seq scan, you will notice no difference. But under unfortunate circumstances it might choose a seq scan ... Servus Manfred