Re: indexes and keys

Andrew Perrin - Demography <aperrin(at)demog(dot)berkeley(dot)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