pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedis a good practice to create an index on the oid?
11+ messages / 4 participants
[nested] [flat]
* is a good practice to create an index on the oid?
@ 2004-04-26 16:38 Edoardo Ceccarelli <eddy@axa.it>
2004-04-26 23:04 ` Re: [JDBC] is a good practice to create an index on the oid? Dave Cramer <pg@fastcrypt.com>
2004-04-27 02:48 ` Re: [PERFORM] is a good practice to create an index on the oid? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 2 replies; 11+ messages in thread
From: Edoardo Ceccarelli @ 2004-04-26 16:38 UTC (permalink / raw)
To: pgsql-jdbc@postgresql.org; pgsql-performance; pgsql-admin@postgresql.org
I am using the oid of the table as the main key and I've found that is
not indexed (maybe because I have declared another primary key in the table)
it is a good practice to create an index like this on the oid of a table?
CREATE INDEX idoid annuncio400 USING btree (oid);
does it work as a normal index?
Thank you
Edoardo
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [JDBC] is a good practice to create an index on the oid?
2004-04-26 16:38 is a good practice to create an index on the oid? Edoardo Ceccarelli <eddy@axa.it>
@ 2004-04-26 23:04 ` Dave Cramer <pg@fastcrypt.com>
1 sibling, 0 replies; 11+ messages in thread
From: Dave Cramer @ 2004-04-26 23:04 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org>; pgsql-performance; pgsql-admin@postgresql.org
Yes, you can create an index on the oid, but unless you are selecting on
it, it is of little use.
you would have to do select * from foo where oid=? to get any value out
of the index.
Dave
On Mon, 2004-04-26 at 12:38, Edoardo Ceccarelli wrote:
> I am using the oid of the table as the main key and I've found that is
> not indexed (maybe because I have declared another primary key in the table)
>
> it is a good practice to create an index like this on the oid of a table?
> CREATE INDEX idoid annuncio400 USING btree (oid);
>
>
> does it work as a normal index?
>
> Thank you
> Edoardo
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>
> !DSPAM:408d7c38183971270217895!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PERFORM] is a good practice to create an index on the oid?
2004-04-26 16:38 is a good practice to create an index on the oid? Edoardo Ceccarelli <eddy@axa.it>
@ 2004-04-27 02:48 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 11:59 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
1 sibling, 1 reply; 11+ messages in thread
From: Christopher Kings-Lynne @ 2004-04-27 02:48 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-jdbc@postgresql.org; pgsql-performance; pgsql-admin@postgresql.org
> I am using the oid of the table as the main key and I've found that is
> not indexed (maybe because I have declared another primary key in the
> table)
>
> it is a good practice to create an index like this on the oid of a table?
> CREATE INDEX idoid annuncio400 USING btree (oid);
Yes it is - in fact you really should add a unique index, not just a
normal index, as you want to enforce uniqueness of the oid column. It
is theoretically possible to end up with duplicate oids in wraparound
situations.
Even better though is to not use oids at all, of course...
Chris
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [JDBC] [PERFORM] is a good practice to create an index on the
2004-04-26 16:38 is a good practice to create an index on the oid? Edoardo Ceccarelli <eddy@axa.it>
2004-04-27 02:48 ` Re: [PERFORM] is a good practice to create an index on the oid? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2004-04-27 11:59 ` Dave Cramer <pg@fastcrypt.com>
2004-04-27 15:01 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 11+ messages in thread
From: Dave Cramer @ 2004-04-27 11:59 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Edoardo Ceccarelli <eddy@axa.it>; pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org>; pgsql-performance; pgsql-admin@postgresql.org
AFAIK, oids aren't used for anything internally, so duplicates don't
really matter. Besides, what would you do about duplicate oid's ?
The best suggestion is of course his last, don't use them.
On Mon, 2004-04-26 at 22:48, Christopher Kings-Lynne wrote:
> > I am using the oid of the table as the main key and I've found that is
> > not indexed (maybe because I have declared another primary key in the
> > table)
> >
> > it is a good practice to create an index like this on the oid of a table?
> > CREATE INDEX idoid annuncio400 USING btree (oid);
>
> Yes it is - in fact you really should add a unique index, not just a
> normal index, as you want to enforce uniqueness of the oid column. It
> is theoretically possible to end up with duplicate oids in wraparound
> situations.
>
> Even better though is to not use oids at all, of course...
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
> !DSPAM:408dcc51235334924183622!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [JDBC] [PERFORM] is a good practice to create an index on the
2004-04-26 16:38 is a good practice to create an index on the oid? Edoardo Ceccarelli <eddy@axa.it>
2004-04-27 02:48 ` Re: [PERFORM] is a good practice to create an index on the oid? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 11:59 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
@ 2004-04-27 15:01 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 15:18 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Christopher Kings-Lynne @ 2004-04-27 15:01 UTC (permalink / raw)
To: pg@fastcrypt.com; +Cc: Edoardo Ceccarelli <eddy@axa.it>; pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org>; pgsql-performance; pgsql-admin@postgresql.org
> AFAIK, oids aren't used for anything internally, so duplicates don't
> really matter. Besides, what would you do about duplicate oid's ?
If he's using them _externally_, then he does have to worry about
duplicates.
Chris
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [JDBC] [PERFORM] is a good practice to create an index on the
2004-04-26 16:38 is a good practice to create an index on the oid? Edoardo Ceccarelli <eddy@axa.it>
2004-04-27 02:48 ` Re: [PERFORM] is a good practice to create an index on the oid? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 11:59 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 15:01 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2004-04-27 15:18 ` Dave Cramer <pg@fastcrypt.com>
2004-04-27 21:42 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Edoardo Ceccarelli <eddy@axa.it>
0 siblings, 1 reply; 11+ messages in thread
From: Dave Cramer @ 2004-04-27 15:18 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Edoardo Ceccarelli <eddy@axa.it>; pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org>; pgsql-performance; pgsql-admin@postgresql.org
Edoardo,
Are you using them for referential integrity? If so you would be wise to
use sequences instead.
Christopher: yes you are correct, I wasn't sure if that is what he was
doing.
Dave
On Tue, 2004-04-27 at 11:01, Christopher Kings-Lynne wrote:
> > AFAIK, oids aren't used for anything internally, so duplicates don't
> > really matter. Besides, what would you do about duplicate oid's ?
>
> If he's using them _externally_, then he does have to worry about
> duplicates.
>
> Chris
>
>
>
> !DSPAM:408e75e0137721921318500!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [JDBC] [PERFORM] is a good practice to create an index on the
2004-04-26 16:38 is a good practice to create an index on the oid? Edoardo Ceccarelli <eddy@axa.it>
2004-04-27 02:48 ` Re: [PERFORM] is a good practice to create an index on the oid? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 11:59 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 15:01 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 15:18 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
@ 2004-04-27 21:42 ` Edoardo Ceccarelli <eddy@axa.it>
2004-04-28 01:30 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 11+ messages in thread
From: Edoardo Ceccarelli @ 2004-04-27 21:42 UTC (permalink / raw)
To: pg@fastcrypt.com; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org>; pgsql-performance; pgsql-admin@postgresql.org
I am going to use them as primary key of the table, so I'll surely need
them unique :)
thank you for you help
Edoardo
Dave Cramer ha scritto:
>Edoardo,
>
>Are you using them for referential integrity? If so you would be wise to
>use sequences instead.
>
>Christopher: yes you are correct, I wasn't sure if that is what he was
>doing.
>
>Dave
>On Tue, 2004-04-27 at 11:01, Christopher Kings-Lynne wrote:
>
>
>>>AFAIK, oids aren't used for anything internally, so duplicates don't
>>>really matter. Besides, what would you do about duplicate oid's ?
>>>
>>>
>>If he's using them _externally_, then he does have to worry about
>>duplicates.
>>
>>Chris
>>
>>
>>
>>!DSPAM:408e75e0137721921318500!
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [JDBC] [PERFORM] is a good practice to create an index on the
2004-04-26 16:38 is a good practice to create an index on the oid? Edoardo Ceccarelli <eddy@axa.it>
2004-04-27 02:48 ` Re: [PERFORM] is a good practice to create an index on the oid? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 11:59 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 15:01 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 15:18 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 21:42 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Edoardo Ceccarelli <eddy@axa.it>
@ 2004-04-28 01:30 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-28 08:13 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Edoardo Ceccarelli <eddy@axa.it>
0 siblings, 1 reply; 11+ messages in thread
From: Christopher Kings-Lynne @ 2004-04-28 01:30 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pg@fastcrypt.com, "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>; pgsql-performance; pgsql-admin@postgresql.org
> I am going to use them as primary key of the table, so I'll surely need
> them unique :)
Eduoardo, I REALLY suggest you don't use them at all. You should make a
primary key like this:
CREATE TABLE blah (
id SERIAL PRIMARY KEY,
...
);
Also note that by default, OIDs are NOT dumped by pg_dump. You will
need to add extra switches to your pg_dump backup to ensure that they are.
Chris
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [JDBC] [PERFORM] is a good practice to create an index on the
2004-04-26 16:38 is a good practice to create an index on the oid? Edoardo Ceccarelli <eddy@axa.it>
2004-04-27 02:48 ` Re: [PERFORM] is a good practice to create an index on the oid? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 11:59 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 15:01 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 15:18 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 21:42 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Edoardo Ceccarelli <eddy@axa.it>
2004-04-28 01:30 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2004-04-28 08:13 ` Edoardo Ceccarelli <eddy@axa.it>
2004-04-28 08:25 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-28 08:32 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 11+ messages in thread
From: Edoardo Ceccarelli @ 2004-04-28 08:13 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pg@fastcrypt.com, "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>; pgsql-performance; pgsql-admin@postgresql.org
do you mean that, declaring an index serial, I'd never have to deal with
incrementing its primary key? good to know!
anyway in this particular situation I don't need such accurate
behaviour: this table is filled up with a lot of data twice per week and
it's used only to answer queries.
I could drop it whenever I want :)
Thanks again,
eddy
Christopher Kings-Lynne ha scritto:
>> I am going to use them as primary key of the table, so I'll surely
>> need them unique :)
>
>
> Eduoardo, I REALLY suggest you don't use them at all. You should make
> a primary key like this:
>
> CREATE TABLE blah (
> id SERIAL PRIMARY KEY,
> ...
> );
>
> Also note that by default, OIDs are NOT dumped by pg_dump. You will
> need to add extra switches to your pg_dump backup to ensure that they
> are.
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [JDBC] [PERFORM] is a good practice to create an index on the
2004-04-26 16:38 is a good practice to create an index on the oid? Edoardo Ceccarelli <eddy@axa.it>
2004-04-27 02:48 ` Re: [PERFORM] is a good practice to create an index on the oid? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 11:59 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 15:01 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 15:18 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 21:42 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Edoardo Ceccarelli <eddy@axa.it>
2004-04-28 01:30 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-28 08:13 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Edoardo Ceccarelli <eddy@axa.it>
@ 2004-04-28 08:25 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 11+ messages in thread
From: Christopher Kings-Lynne @ 2004-04-28 08:25 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pg@fastcrypt.com, "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>; pgsql-performance; pgsql-admin@postgresql.org
> do you mean that, declaring an index serial, I'd never have to deal with
> incrementing its primary key? good to know!
Yep. You can use 'DEFAULT' as the value, eg:
INSERT INTO blah (DEFAULT, ...);
> anyway in this particular situation I don't need such accurate
> behaviour: this table is filled up with a lot of data twice per week and
> it's used only to answer queries.
> I could drop it whenever I want :)
Sure.
Chris
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [JDBC] [PERFORM] is a good practice to create an index on the
2004-04-26 16:38 is a good practice to create an index on the oid? Edoardo Ceccarelli <eddy@axa.it>
2004-04-27 02:48 ` Re: [PERFORM] is a good practice to create an index on the oid? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 11:59 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 15:01 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 15:18 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 21:42 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Edoardo Ceccarelli <eddy@axa.it>
2004-04-28 01:30 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-28 08:13 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Edoardo Ceccarelli <eddy@axa.it>
@ 2004-04-28 08:32 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 11+ messages in thread
From: Bruno Wolff III @ 2004-04-28 08:32 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pg@fastcrypt.com, "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>; pgsql-performance; pgsql-admin@postgresql.org
On Wed, Apr 28, 2004 at 10:13:14 +0200,
Edoardo Ceccarelli <eddy@axa.it> wrote:
> do you mean that, declaring an index serial, I'd never have to deal with
> incrementing its primary key? good to know!
That isn't what is happening. Serial is a special type. It is int plus
a default rule linked to a sequence. No index is created by default
for the serial type. Declaring a column as a primary key will however
create a unique index on that column.
Also note that you should only assume that the serial values are unique.
(This assumes that you don't use setval and that you don't roll a sequence
over.) Within a single session you can assume the sequence values will
be monotonicly increasing. The values that end up in your table can have
gaps. Typically this happens when a transaction rolls back after obtaining
a new value from a sequence. It can also happen if you grab sequence
values in larger blocks (which might be more efficient if a session normally
acquires mulitple values from a particular sequence) than the default 1.
> anyway in this particular situation I don't need such accurate
> behaviour: this table is filled up with a lot of data twice per week and
> it's used only to answer queries.
> I could drop it whenever I want :)
You really don't want to use oids.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2004-04-28 08:32 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-26 16:38 is a good practice to create an index on the oid? Edoardo Ceccarelli <eddy@axa.it>
2004-04-26 23:04 ` Dave Cramer <pg@fastcrypt.com>
2004-04-27 02:48 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 11:59 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 15:01 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-27 15:18 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Dave Cramer <pg@fastcrypt.com>
2004-04-27 21:42 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Edoardo Ceccarelli <eddy@axa.it>
2004-04-28 01:30 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-28 08:13 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Edoardo Ceccarelli <eddy@axa.it>
2004-04-28 08:25 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-04-28 08:32 ` Re: [JDBC] [PERFORM] is a good practice to create an index on the Bruno Wolff III <bruno@wolff.to>
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