From: Josh Berkus <josh@agliodbs.com>
To: James Cooper <jim@luckydigital.com>
To: sql <pgsql-sql@postgresql.org>
Subject: Re: indexing
Date: Mon, 24 Feb 2003 17:16:24 -0800
Message-ID: <200302241716.24360.josh@agliodbs.com> (raw)
In-Reply-To: <006a01c2dc48$94d665f0$c900a8c0@jax>
References: <006a01c2dc48$94d665f0$c900a8c0@jax>
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
forigen keys(b.a_id,b.c_id).
> is it best to create two non-unique indexes or one unique index on both
fields?
That depends on whether all three tables are usually queried toghether. If
yes, then a 2-column index is probably better. If not, use single-column
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
person_id and
> ran
> Explain
> select person_id from person where person_id < n
>
> but saw no results of my created index being used - am i doing something
incorrectly
Not a surprise. If your the planner expects person_id < n to return a
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 - which
type of index is best?
You want to use a B-tree index for anything other than statistical and/or
geometic data. You are unlikely to need any other kind of index.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, jim@luckydigital.com
Subject: Re: indexing
In-Reply-To: <200302241716.24360.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox