From jim@luckydigital.com Mon Feb 24 23:29:14 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 749A24763C5 for ; Mon, 24 Feb 2003 18:29:14 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 01397-03 for ; Mon, 24 Feb 2003 18:28:46 -0500 (EST) Received: from luckydigital.com (www.luckydigital.com [203.109.157.4]) by postgresql.org (Postfix) with ESMTP id 9991C476067 for ; Mon, 24 Feb 2003 16:06:22 -0500 (EST) Received: from jax (203-109-198-1.akl.ihugultra.co.nz [203.109.198.1]) by luckydigital.com (8.11.6/8.10.0) with SMTP id h1OL6MT02636 for ; Tue, 25 Feb 2003 10:06:22 +1300 Message-ID: <006a01c2dc48$94d665f0$c900a8c0@jax> From: "James Cooper" To: "sql" Subject: indexing Date: Tue, 25 Feb 2003 09:38:28 +1300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_005F_01C2DCB1.A709E1C0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/536 X-Sequence-Number: 11934 This is a multi-part message in MIME format. ------=_NextPart_000_005F_01C2DCB1.A709E1C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I've been doing a little reading on indexing in prelude to indexing my db. I have the following to ask: 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 f= origen keys(b.a_id,b.c_id). is it best to create two non-unique indexes or one unique index on both fie= lds? Please advise Ps=20 I also attempted creating an index on a table i have called person on perso= n_id and=20 ran=20 Explain=20 select person_id from person where person_id < n=20 but saw no results of my created index being used - am i doing something in= correctly Pps When indexing if searching tables is more important than concurrency - whic= h type of index is best? ------=_NextPart_000_005F_01C2DCB1.A709E1C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi all,

I've been doing a little reading on indexi= ng in=20 prelude to indexing my db.

I have the following to ask:

if I had three tables for a many to many= =20 relationship say A, B, AND C

B being the lookup. B being a huge 50k row= s plus=20 column and made just two forigen keys(b.a_id,b.c_id).

is it best to create two non-unique indexe= s or one=20 unique index on both fields?

Please advise

I also attempted creating an index on a ta= ble i=20 have called person on person_id and

ran

Explain

select person_id from person where person_= id < n=20

but saw no results of my created index bei= ng used -=20 am i doing something incorrectly

Pps

When indexing if searching tables is more = important=20 than concurrency - which type of index is best?

------=_NextPart_000_005F_01C2DCB1.A709E1C0-- From jim@luckydigital.com Tue Feb 25 01:33:37 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0BCAE476265 for ; Mon, 24 Feb 2003 20:33:37 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 27298-03 for ; Mon, 24 Feb 2003 20:33:09 -0500 (EST) Received: from luckydigital.com (www.luckydigital.com [203.109.157.4]) by postgresql.org (Postfix) with ESMTP id 1DDF4476389 for ; Mon, 24 Feb 2003 17:38:58 -0500 (EST) Received: from jax (203-109-198-1.akl.ihugultra.co.nz [203.109.198.1]) by luckydigital.com (8.11.6/8.10.0) with SMTP id h1OMcxT08390 for ; Tue, 25 Feb 2003 11:38:59 +1300 Message-ID: <00cb01c2dc55$85b3d000$c900a8c0@jax> From: "James Cooper" To: "sql" Subject: indexing Date: Tue, 25 Feb 2003 11:38:59 +1300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00C8_01C2DCC2.7C7F2A30" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/538 X-Sequence-Number: 11936 This is a multi-part message in MIME format. ------=_NextPart_000_00C8_01C2DCC2.7C7F2A30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I've been doing a little reading on indexing in prelude to indexing my db. I have the following to ask: 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 f= origen keys(b.a_id,b.c_id). is it best to create two non-unique indexes or one unique index on both fie= lds? Please advise Ps=20 I also attempted creating an index on a table i have called person on perso= n_id and=20 ran=20 Explain=20 select person_id from person where person_id < n=20 but saw no results of my created index being used - am i doing something in= correctly Pps When indexing if searching tables is more important than concurrency - whic= h type of index is best? ------=_NextPart_000_00C8_01C2DCC2.7C7F2A30 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi all,

I've been doing a little reading on indexi= ng in=20 prelude to indexing my db.

I have the following to ask:

if I had three tables for a many to many= =20 relationship say A, B, AND C

B being the lookup. B being a huge 50k row= s plus=20 column and made just two forigen keys(b.a_id,b.c_id).

is it best to create two non-unique indexe= s or one=20 unique index on both fields?

Please advise

I also attempted creating an index on a ta= ble i=20 have called person on person_id and

ran

Explain

select person_id from person where person_= id < n=20

but saw no results of my created index bei= ng used -=20 am i doing something incorrectly

Pps

When indexing if searching tables is more = important=20 than concurrency - which type of index is=20 best?

------=_NextPart_000_00C8_01C2DCC2.7C7F2A30-- From josh@agliodbs.com Tue Feb 25 04:39:15 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5427F475E30 for ; Mon, 24 Feb 2003 23:39:15 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 81368-04 for ; Mon, 24 Feb 2003 23:38:46 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id F347C4761C2 for ; Mon, 24 Feb 2003 20:16:19 -0500 (EST) Received: from [66.219.92.2] (HELO chocolate-mousse) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2855699; Mon, 24 Feb 2003 17:16:22 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "James Cooper" , "sql" Subject: Re: indexing Date: Mon, 24 Feb 2003 17:16:24 -0800 X-Mailer: KMail [version 1.4] 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> X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/540 X-Sequence-Number: 11938 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