pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedindexes and keys
3+ messages / 3 participants
[nested] [flat]
* indexes and keys
@ 2000-04-17 05:01 Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
2000-04-17 06:03 ` Re: indexes and keys Tom Lane <tgl@sss.pgh.pa.us>
2000-04-17 06:09 ` Re: indexes and keys Ryan Bradetich <ryan_bradetich@hp.com>
0 siblings, 2 replies; 3+ messages in thread
From: Andrew Perrin - Demography @ 2000-04-17 05:01 UTC (permalink / raw)
To: pgsql-sql
Greetings - I hope this is a simple question.
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 ...);
to create it, so \d shows:
| postgres | personal_data | table |
| postgres | personal_data | table |
| postgres | personal_data_id_key | index |
| postgres | personal_data_id_seq | sequence |
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?
Thanks.
---------------------------------------------------------------------
Andrew J. Perrin - aperrin@demog.berkeley.edu - NT/Unix Admin/Support
Department of Demography - University of California at Berkeley
2232 Piedmont Avenue #2120 - Berkeley, California, 94720-2120 USA
http://demog.berkeley.edu/~aperrin --------------------------SEIU1199
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: indexes and keys
2000-04-17 05:01 indexes and keys Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
@ 2000-04-17 06:03 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2000-04-17 06:03 UTC (permalink / raw)
To: Andrew Perrin - Demography <aperrin@demog.berkeley.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: indexes and keys
2000-04-17 05:01 indexes and keys Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
@ 2000-04-17 06:09 ` Ryan Bradetich <ryan_bradetich@hp.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ryan Bradetich @ 2000-04-17 06:09 UTC (permalink / raw)
To: Andrew Perrin - Demography <aperrin@demog.berkeley.edu>; +Cc: pgsql-sql
Andrew,
The one created should be sufficient. You can check to make sure
it fits your needs exactly by: \d <index name>
Ryan
Andrew Perrin - Demography wrote:
> Greetings - I hope this is a simple question.
>
> 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 ...);
>
> to create it, so \d shows:
>
> | postgres | personal_data | table |
> | postgres | personal_data | table |
> | postgres | personal_data_id_key | index |
> | postgres | personal_data_id_seq | sequence |
>
> 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?
>
> Thanks.
>
> ---------------------------------------------------------------------
> Andrew J. Perrin - aperrin@demog.berkeley.edu - NT/Unix Admin/Support
> Department of Demography - University of California at Berkeley
> 2232 Piedmont Avenue #2120 - Berkeley, California, 94720-2120 USA
> http://demog.berkeley.edu/~aperrin --------------------------SEIU1199
--
Ryan Bradetich
AIT Operations
Unix Platform Team
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-04-17 06:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-17 05:01 indexes and keys Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
2000-04-17 06:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-04-17 06:09 ` Ryan Bradetich <ryan_bradetich@hp.com>
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