Professional Web Applications Themes

default index created for primary key - PostgreSQL / PGSQL

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! [url]http://creative.mediaturf.net/creatives/suvidha/suvidha_hmtagoffline_dec04.htm[/url] Apply & get FREE watch! ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings...

  1. #1

    Default 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!
    [url]http://creative.mediaturf.net/creatives/suvidha/suvidha_hmtagoffline_dec04.htm[/url]
    Apply & get FREE watch!


    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    vinita bansal Guest

  2. #2

    Default Re: default index created for primary key

    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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Michael Fuhr Guest

  3. #3

    Default Re: 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?
    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

    Sander Steffann Guest

  4. #4

    Default Re: default index created for primary key

    "vinita bansal" <sagivinihotmail.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

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Tom Lane Guest

  5. #5

    Default Re: default index created for primary key

    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" <steffannnederland.net>
    >To: "'vinita bansal'" <sagivinihotmail.com>,<pgsql-generalpostgresql.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!
    [url]http://www.msn.co.in/Cinema/screensaver/[/url] Get these vibrant screensavers!


    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    vinita bansal Guest

  6. #6

    Default Re: default index created for primary key

    On Wed, Dec 22, 2004 at 17:09:26 +0000,
    vinita bansal <sagivinihotmail.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.

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Bruno Wolff III Guest

  7. #7

    Default Re: default index created for primary key


    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. <fde101fjrhome.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.
    $

    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.2.4 (Darwin)

    iD8DBQFBycQ87aqtWrR9cZoRAig7AJ9q4xZntGWm7Vdz2YHkBU c48JFJtwCbBe3j
    T5YksTA2AzKzTllHa+cxq8Q=
    =QPam
    -----END PGP SIGNATURE-----

    Frank D. Engel, Jr. Guest

  8. #8

    Default Re: default index created for primary key

    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.



    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Sander Steffann Guest

Similar Threads

  1. default index for primary key of a table
    By Greg Stark in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: December 24th, 08:15 PM
  2. Replies: 1
    Last Post: October 17th, 01:00 PM
  3. Replies: 5
    Last Post: October 14th, 09:47 PM
  4. Replies: 1
    Last Post: October 14th, 01:24 PM
  5. Newb query: index.htm & index.php & the server default
    By Lab309 in forum PHP Development
    Replies: 7
    Last Post: September 22nd, 02:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139