Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "James Cooper" <jim@luckydigital.com>,
	"sql" <pgsql-sql@postgresql.org>
Subject: Re: indexing
Date: Mon, 24 Feb 2003 17:16:24 -0800
References: <006a01c2dc48$94d665f0$c900a8c0@jax>
In-Reply-To: <006a01c2dc48$94d665f0$c900a8c0@jax>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200302241716.24360.josh@agliodbs.com>

James,

> if I had three tables for a many to many relationship say A, B, AND C
> B being the lookup. B being a huge 50k rows plus column and made just two=
=20
forigen keys(b.a_id,b.c_id).
> is it best to create two non-unique indexes or one unique index on both=
=20
fields?

That depends on whether all three tables are usually queried toghether.  If=
=20
yes, then a 2-column index is probably better.  If not, use single-column=
=20
indexes.   If you want more specific advice, post your table structures.

> I also attempted creating an index on a table i have called person on=20
person_id and=20
> ran=20
> Explain=20
> select person_id from person where person_id < n=20
>=20
> but saw no results of my created index being used - am i doing something=
=20
incorrectly

Not a surprise.   If your the planner expects person_id < n to return a=20
significant portion of the table, a table scan is faster than an index scan.

> Pps
> When indexing if searching tables is more important than concurrency - wh=
ich=20
type of index is best?

You want to use a B-tree index for anything other than statistical and/or=
=20
geometic data.  You are unlikely to need any other kind of index.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco