Thread: indexes and keys
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
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
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
I understand that outer joins are not supported in PostgreSQL. I'm just starting my first PostgreSQL project, and have to admit that this concerns me. Outer joins have come in handy, at some point, in almost every database project I've ever worked on. I guess I'd like to hear how you all work around this issue. Thanks, -=michael=-
why it is a problem at all, if you use sql so long? outer join can be easily simulated. On Tue, 18 Apr 2000, Michael S. Kelly wrote: > I understand that outer joins are not supported in PostgreSQL. I'm just > starting my first PostgreSQL project, and have to admit that this concerns > me. Outer joins have come in handy, at some point, in almost every database > project I've ever worked on. I guess I'd like to hear how you all work > around this issue. > > Thanks, > > -=michael=- >
Actually found the answer to my question in Bruce Momjian's book. I'm shamefaced to say that I never considered UNION ALL solve the problem. thanks, -=michael=- ***************************************************** * Michael S. Kelly * 4800 SW Griffith Dr., Ste. 202 * Beaverton, OR 97005 USA * voice: (503)644-6106 x122 fax: (503)643-8425 * <michaelk@axian.com> * http://www.axian.com/ ***************************************************** * Axian: Software Consulting and Training ***************************************************** -----Original Message----- From: kaiq@realtyideas.com [mailto:kaiq@realtyideas.com] Sent: Tuesday, April 18, 2000 10:54 AM To: Michael S. Kelly Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Outer joins why it is a problem at all, if you use sql so long? outer join can be easily simulated. On Tue, 18 Apr 2000, Michael S. Kelly wrote: > I understand that outer joins are not supported in PostgreSQL. I'm just > starting my first PostgreSQL project, and have to admit that this concerns > me. Outer joins have come in handy, at some point, in almost every database > project I've ever worked on. I guess I'd like to hear how you all work > around this issue. > > Thanks, > > -=michael=- >
There is an FAQ item about it. > why it is a problem at all, if you use sql so long? outer join > can be easily simulated. > > On Tue, 18 Apr 2000, Michael S. Kelly wrote: > > > I understand that outer joins are not supported in PostgreSQL. I'm just > > starting my first PostgreSQL project, and have to admit that this concerns > > me. Outer joins have come in handy, at some point, in almost every database > > project I've ever worked on. I guess I'd like to hear how you all work > > around this issue. > > > > Thanks, > > > > -=michael=- > > > > -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
[Charset iso-8859-1 unsupported, filtering to ASCII...] > Actually found the answer to my question in Bruce Momjian's book. I'm > shamefaced to say that I never considered UNION ALL solve the problem. Don't be. It took me a few years of apologizing for no Outer joins until I found the answer. If others already knew it, I hadn't seen it. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026