Thread: default index created for primary key
Hi, I want to turn off the default setting in postgres for index creation on primary key of a table. Is it possible and how? Regards Vinita _________________________________________________________________ Citibank Suvidha account at No Minimum Balance! http://creative.mediaturf.net/creatives/suvidha/suvidha_hmtagoffline_dec04.htm Apply & get FREE watch!
On Wed, Dec 22, 2004 at 01:32:14PM +0000, vinita bansal wrote: > I want to turn off the default setting in postgres for index creation on > primary key of a table. Is it possible and how? PostgreSQL uses the index to enforce the primary key's uniqueness. What problem are you trying to solve by removing it? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi, > I want to turn off the default setting in postgres for index > creation on primary key of a table. Is it possible and how? That is not possible, because the index is used to guarantee the uniqueness of the primary key. What is the reason you want to turn it off? Sander.
"vinita bansal" <sagivini@hotmail.com> writes:
> I want to turn off the default setting in postgres for index creation on
> primary key of a table. Is it possible and how?
No. The index is needed to enforce the unique constraint.
regards, tom lane
Hi, I am actually migrating indexes from oracle database to postgres. I wanted to turn it off so that index on the same columns is not created again (index created for primary key of a table). I'll probably need to check in that case and not create the index if it is on the primary key of the table since that will be created by default. I am still not clear on why postgres has this restriction? By uniqueness, you mean to say that if later anyone wants to add a primary key constraint on a table which already has a primary key defined, postgres will use this index to determine that there is already a primary key defined and would not allow to add this constraint since a table cannot have two primary keys?? Thanks, Vinita Bansal >From: "Sander Steffann" <steffann@nederland.net> >To: "'vinita bansal'" <sagivini@hotmail.com>,<pgsql-general@postgresql.org> >Subject: Re: [GENERAL] default index created for primary key >Date: Wed, 22 Dec 2004 16:50:58 +0100 > >Hi, > > > I want to turn off the default setting in postgres for index > > creation on primary key of a table. Is it possible and how? > >That is not possible, because the index is used to guarantee >the uniqueness of the primary key. > >What is the reason you want to turn it off? >Sander. > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster _________________________________________________________________ Pep up your screen! Kickstart your day! http://www.msn.co.in/Cinema/screensaver/ Get these vibrant screensavers!
On Wed, Dec 22, 2004 at 17:09:26 +0000, vinita bansal <sagivini@hotmail.com> wrote: > > I am actually migrating indexes from oracle database to postgres. I wanted > to turn it off so that index on the same columns is not created again > (index created for primary key of a table). I'll probably need to check in > that case and not create the index if it is on the primary key of the table > since that will be created by default. You might be able to delete the indexes after the fact using data from the catalog to find duplicates. This might be useful if there are so many that looking for them by hand might be error prone. > I am still not clear on why postgres has this restriction? > By uniqueness, you mean to say that if later anyone wants to add a primary > key constraint on a table which already has a primary key defined, postgres > will use this index to determine that there is already a primary key > defined and would not allow to add this constraint since a table cannot > have two primary keys?? When you declare a primary key you are declaring a primary key constraint at the same time. The way postgres implements that constraint is with an index.
On Dec 22, 2004, at 12:09 PM, vinita bansal wrote:
<excerpt>
I am still not clear on why postgres has this restriction?
By uniqueness, you mean to say that if later anyone wants to add a
primary key constraint on a table which already has a primary key
defined, postgres will use this index to determine that there is
already a primary key defined and would not allow to add this
constraint since a table cannot have two primary keys??
</excerpt>
No, an index is required for efficiency.
Consider a table with a column which must be unique. Assume there are
350,000 rows in the table. Now *every time* you insert a new row or
perform an update which changes that unique column, assuming no index
on the column, the database would need to check all 350,000 rows
individually to determine that the value is in fact unique.
With an index on the column, it is relatively quick for the database
to determine that the value is unique, as it does not need to check
nearly as many values..
To see this (rough example), start with an empty table with a single
column, which is a unique integer column. Now add values and watch
what happens to an index (use a fixed-width font):
4 53 72 15 23 19 3 12 8
Index:
<fixed><bigger>4
4
\
53
53
/ \
4 72
53
/ \
4 72
\
15
53
/ \
15 72
/ \
4 23
19
/ \
15 53
/ \ \
4 23 72
15
/ \
/ \
/ \
4 53
/ \ / \
3 23 19 72
15
/ \
/ \
/ \
4 53
/ \ / \
3 23 19 72
\
12
15
/ \
/ \
/ \
4 53
/ \ / \
8 23 19 72
/ \
3 12</bigger></fixed>
Now the user tries to insert 12, which is already in the table. In
order to determine that 12 is in the table, the database could scan
every value in the table until it finds it. In this case, it would
need to check 8 rows. Using the index, it would only need to check 4
values, cutting the time in half. In a few cases, it may take
marginally longer (2 as opposed to 1 for the value 4), but on average,
5 rows for unindexed vs. 2.8 rows for indexed, shows that the index
has a definite advantage.
Now extend this to 350,000 rows. Without an index, you'd need to
check an average of about 175,000 rows just to determine that a value
was there. And if the value is not there, as will more commonly be
the case, you'd need to check them all. With an index like the ones I
used above, you would need to check *at most* 19 values. You begin to
see why PostgreSQL requires an index here.
-----------------------------------------------------------
Frank D. Engel, Jr. <<fde101@fjrhome.net>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
On Dec 22, 2004, at 12:09 PM, vinita bansal wrote:
>
> I am still not clear on why postgres has this restriction?
> By uniqueness, you mean to say that if later anyone wants to add a
> primary key constraint on a table which already has a primary key
> defined, postgres will use this index to determine that there is
> already a primary key defined and would not allow to add this
> constraint since a table cannot have two primary keys??
No, an index is required for efficiency.
Consider a table with a column which must be unique. Assume there are
350,000 rows in the table. Now *every time* you insert a new row or
perform an update which changes that unique column, assuming no index
on the column, the database would need to check all 350,000 rows
individually to determine that the value is in fact unique.
With an index on the column, it is relatively quick for the database to
determine that the value is unique, as it does not need to check nearly
as many values..
To see this (rough example), start with an empty table with a single
column, which is a unique integer column. Now add values and watch
what happens to an index (use a fixed-width font):
4 53 72 15 23 19 3 12 8
Index:
4
4
\
53
53
/ \
4 72
53
/ \
4 72
\
15
53
/ \
15 72
/ \
4 23
19
/ \
15 53
/ \ \
4 23 72
15
/ \
/ \
/ \
4 53
/ \ / \
3 23 19 72
15
/ \
/ \
/ \
4 53
/ \ / \
3 23 19 72
\
12
15
/ \
/ \
/ \
4 53
/ \ / \
8 23 19 72
/ \
3 12
Now the user tries to insert 12, which is already in the table. In
order to determine that 12 is in the table, the database could scan
every value in the table until it finds it. In this case, it would
need to check 8 rows. Using the index, it would only need to check 4
values, cutting the time in half. In a few cases, it may take
marginally longer (2 as opposed to 1 for the value 4), but on average,
5 rows for unindexed vs. 2.8 rows for indexed, shows that the index has
a definite advantage.
Now extend this to 350,000 rows. Without an index, you'd need to check
an average of about 175,000 rows just to determine that a value was
there. And if the value is not there, as will more commonly be the
case, you'd need to check them all. With an index like the ones I used
above, you would need to check *at most* 19 values. You begin to see
why PostgreSQL requires an index here.
-----------------------------------------------------------
Frank D. Engel, Jr. <fde101@fjrhome.net>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
Attachment
Hi, > I am actually migrating indexes from oracle database to postgres. I wanted > to turn it off so that index on the same columns is not created again > (index created for primary key of a table). I'll probably need to check in > that case and not create the index if it is on the primary key of the > table since that will be created by default. That is the most simple sollution I think. > I am still not clear on why postgres has this restriction? > By uniqueness, you mean to say that if later anyone wants to add a primary > key constraint on a table which already has a primary key defined, > postgres will use this index to determine that there is already a primary > key defined and would not allow to add this constraint since a table > cannot have two primary keys?? No, PostgreSQL uses the index to check that the same value can not occur twice in the primary key field. A pretty important part of primary keys :-) Sander.