Thread: Two efficiency questions - clustering and ints
I have a good-size DB (some tables approaching 100M rows), with essentially static data. Should I always cluster the tables? That is, even if no column jumps out as being involved in most queries, should I pick a likely one and cluster on it? (Of course, this assumes that doing so won't cause bad correlation with any other oft-used column.) Another question, about integer types - if no cross-type coercion is involved, is there any reason not to choose the smallest int type that will fit my data? In particular, I have a column of small- integer ratings with, say, values in [1, 10]. If I'm only comparing within such ratings, and possibly computing floating point averages, etc., what are the good and bad points of using, say, SMALLINT? What about NUMERIC(1) or (2)? Thanks in advance for the usual brilliant replies! - John D. Burger MITRE
John D. Burger wrote: > I have a good-size DB (some tables approaching 100M rows), with > essentially static data. > > Should I always cluster the tables? That is, even if no column jumps > out as being involved in most queries, should I pick a likely one and > cluster on it? (Of course, this assumes that doing so won't cause bad > correlation with any other oft-used column.) Well you cluster on an index, and if you don't think the index is useful, I'd drop it. If you have an index, clustering isn't necessarily going to help you unless you regularly read a series of rows in order. > Another question, about integer types - if no cross-type coercion is > involved, is there any reason not to choose the smallest int type that > will fit my data? In particular, I have a column of small-integer > ratings with, say, values in [1, 10]. If I'm only comparing within such > ratings, and possibly computing floating point averages, etc., what are > the good and bad points of using, say, SMALLINT? What about NUMERIC(1) > or (2)? (int2, int2) should pack into 4 bytes on-disk, but due to alignment issues I think (int2, int4) still takes up 8 bytes. There has been discussion about being able to have different physical column ordering on-disk vs. in SQL but no decision as to whether the effort will be worthwhile. Numeric types tend to be slower than their int equivalent, and though I've not checked their storage requirements, I'd assume they take more space too. HTH -- Richard Huxton Archonet Ltd
Richard Huxton wrote: >> Should I always cluster the tables? That is, even if no column >> jumps out as being involved in most queries, should I pick a >> likely one and cluster on it? > > Well you cluster on an index, and if you don't think the index is > useful, I'd drop it. If you have an index, clustering isn't > necessarily going to help you unless you regularly read a series of > rows in order. Right, but clustering on that index means an index scan (with that index) will be faster. This is uninteresting if the index doesn't get used, but is there any =downside= to doing it? Here's a simpler question - for static data, should I always cluster on the index I think will be used the most? Thanks. - John D. Burger MITRE
On Oct 6, 2006, at 11:12 AM, John D. Burger wrote: > Richard Huxton wrote: > >>> Should I always cluster the tables? That is, even if no column >>> jumps out as being involved in most queries, should I pick a >>> likely one and cluster on it? >> >> Well you cluster on an index, and if you don't think the index is >> useful, I'd drop it. If you have an index, clustering isn't >> necessarily going to help you unless you regularly read a series >> of rows in order. > > Right, but clustering on that index means an index scan (with that > index) will be faster. This is uninteresting if the index doesn't > get used, but is there any =downside= to doing it? > > Here's a simpler question - for static data, should I always > cluster on the index I think will be used the most? Depends on the queries. If the index is on a foreign key value where there may be many rows with the same key scattered about it will help queries that lookup using that foreign key. Clustering on a column with high cardinality isn't much of a win typically for single key lookups (depends on the lookup pattern), but would be for ranges and possibly for sorting on that column. It also depends on the size of the table and indices. If they are small enough to fit in memory then clustering to reduce random access isn't really helpful. I would suggest doing some timing tests on typical queries with the data unclustered and clustered to know what you are gaining. -Casey