Re: [GENERAL] Q: Structured index - which one runs faster?
От | Reece Hart |
---|---|
Тема | Re: [GENERAL] Q: Structured index - which one runs faster? |
Дата | |
Msg-id | 1053708385.29339.26.camel@tallac обсуждение исходный текст |
Ответ на | Q: Structured index - which one runs faster? (Ernest E Vogelsinger <ernest@vogelsinger.at>) |
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления: