Re: Indexing Question

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461