Re: Indexing Question

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Mike <akiany(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Indexing Question
Date: 2006-01-13 22:39:38
Message-ID: 20060113223938.GZ9017@pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sally Sally 2006-01-13 22:45:51 Unable to connect to a dabase
Previous Message Greg Sabino Mullane 2006-01-13 22:39:06 Re: Plans for 8.2?