On 2/12/06, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Aaron Turner <synfinatic@xxxxxxxxx> writes: > > Well before I go about re-architecting things, it would be good to > > have a strong understanding of just what is going on. Obviously, the > > unique index on the char(48) is the killer. What I don't know is: > > You have another unique index on the integer primary key, so it's not > the mere fact of a unique index that's hurting you. Understood. I just wasn't sure if in general unique indexes are some how more expensive then non-unique indexes. > > 1) Is this because the column is so long? > > Possibly. Allowing for 12 bytes index-entry overhead, the char keys > would be 60 bytes vs 16 for the integer column, so this index is > physically almost 4x larger than the other. You might say "but that > should only cause 4x more I/O" but it's not necessarily so. What's > hard to tell is whether you are running out of RAM disk cache space, > resulting in re-reads of pages that could have stayed in memory when > dealing with one-fifth as much index data. You did not show us the > iostat numbers for the two cases, but it'd be interesting to look at > the proportion of writes to reads on the data drive in both cases. Sounds a lot like what Marc mentioned. > > 2) Is this because PG is not optimized for char(48) (maybe it wants > > powers of 2? or doesn't like even numbers... I don't know, just > > throwing it out there) > > Are the key values really all 48 chars long? If not, you made a > bad datatype choice: varchar(n) (or even text) would be a lot > smarter. char(n) wastes space on blank-padding. Yep, everything exactly 48. Looks like I'll be storing it as a bytea in the near future though. > The only one of these effects that looks to me like it could result in > worse-than-linear degradation of I/O demand is maxing out the available > RAM for disk cache. So while improving the datatype choice would > probably be worth your while, you should first see if fooling with > shared_buffers helps, and if not it's time to buy RAM not disk. Yeah, that's what it's beginning to sound like. Thanks Tom. -- Aaron Turner http://synfin.net/
