Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by hub.org (8.9.3/8.9.3) with ESMTP id CAA88579
	for <pgsql-sql@postgresql.org>; Mon, 17 Apr 2000 02:04:10 -0400 (EDT)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id CAA06503;
	Mon, 17 Apr 2000 02:03:44 -0400 (EDT)
To: Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
cc: pgsql-sql@postgresql.org
Subject: Re: indexes and keys 
In-reply-to: 
 <Pine.SUN.4.10.10004162158200.2584-100000@davis.DEMOG.Berkeley.EDU>
References: 
 <Pine.SUN.4.10.10004162158200.2584-100000@davis.DEMOG.Berkeley.EDU>
Comments: In-reply-to Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
	message dated "Sun, 16 Apr 2000 22:01:17 -0700"
Date: Mon, 17 Apr 2000 02:03:44 -0400
Message-ID: <6500.955951424@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Archive-Number: 200004/162

Andrew Perrin - Demography <aperrin@demog.berkeley.edu> writes:
> Is there any reason to create an index on a column that already has one
> created as part of a SERIAL datatype?  That is, I used:
> CREATE TABLE personal_data (id serial ...);
> is there any reason to create another index on id:
> CREATE INDEX pd_id_idx on personal_data;
> or will the automatically created one be sufficient?

Waste of space and cycles ... it should work, as far as I know,
but there's no possible value in it.

Offhand, the only reason I can see for having multiple indexes
on a column is if some are functional indexes.  For example,
given the right query mix it could make sense to have both a
plain index on a text column t1, and an index on lower(t1).
But each index costs you space and update time, so you want to
be pretty sure that all of them are earning their keep.

			regards, tom lane