On Thu, Jan 12, 2006 at 01:16:00PM -0800, Mike wrote: > Hi, > > My table structure is the following: > > tbl_A ----one-to-many---> tbl_B ----one-to-many---> tbl_C > > Since it was important for me to trace back tbl_C records back to > tbl_A, I placed a tbl_A_id inside tbl_C. > > Now, in order to optimize my database for speed, I want to index my > tbl_B for it's tbl_A_id. So far so good. Now, with tbl_C, it makes > sense that all records of tbl_A sit next to eachother so I could index > tbl_A_id (which are not used as often in my queries), or index by > tbl_B_id. Or both of them. Your breaking a tenant of good database design: normalize 'til it hurts, denormalize 'til it works (where works in this case means performs adequately). In other words, don't keep tbl_a_id in tbl_c unless you know for certain you need it for performance reasons. > To be clear, my question is: Does it make sense for me to index a table > by field_1 with the intention of having postgreSQL place those records > next to each other for faster queries that wouldn't necessarily > reference field_1? Indexes have absolutely nothing to do with the order in which rows are stored in a table, unless you cluster the table on an index (which is still only temporary). Without knowing what your normal access patterns on tbl_c will be it's impossible to say if clustering on an index on tbl_a_id would help or not. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
