From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: indexes and keys
Date: Mon, 17 Apr 2000 02:03:44 -0400
Message-ID: <6500.955951424@sss.pgh.pa.us> (raw)
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>
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
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: tgl@sss.pgh.pa.us, aperrin@demog.berkeley.edu
Subject: Re: indexes and keys
In-Reply-To: <6500.955951424@sss.pgh.pa.us>
* 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