FAQ
Hi guys,

Why postgres does not disallow creating redundants indexes ? Is it the
same behaviour in postgresql 8.3 ?

postgres=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
name | character varying(20) |
Indexes:
"t1_pkey" PRIMARY KEY, btree (id)
"idx" btree (id)
"idx2" btree (id)
"idx3" btree (id)
"idx4" btree (id)
"idx5" btree (id)


Regards
--
Cyril SCETBON

Search Discussions

  • Hubert depesz lubaczewski at Jul 4, 2008 at 10:03 am

    On Fri, Jul 04, 2008 at 11:54:37AM +0200, Cyril SCETBON wrote:
    Why postgres does not disallow creating redundants indexes ? Is it the
    same behaviour in postgresql 8.3 ?
    Why should it? Redundant indexes are not "bugs". And can be very useful
    sometimes (thing concurrent reindexing).

    depesz
  • Cyril SCETBON at Jul 4, 2008 at 10:10 am

    hubert depesz lubaczewski wrote:
    On Fri, Jul 04, 2008 at 11:54:37AM +0200, Cyril SCETBON wrote:

    Why postgres does not disallow creating redundants indexes ? Is it the
    same behaviour in postgresql 8.3 ?
    Why should it? Redundant indexes are not "bugs". And can be very useful
    sometimes (thing concurrent reindexing).
    in this case your right, but lot of people are confused with primary key
    and unique key. So they create a unique key on the same column that
    constitute the primary key. For example, Oracle inhib it :

    SQL> create table toto(id int primary key);

    Table created.

    SQL> create unique index idx_toto_id on toto(id);
    create unique index idx_toto_id on toto(id)
    *
    ERROR at line 1:
    ORA-01408: such column list already indexed

    concurrent reindexing is the matter of postgresql, it should create it
    transparently when needed. And if I take into account the concurrent
    reindexing, why permitting more than 2 index on the same column ?

    --
    Cyril SCETBON
  • Richard Huxton at Jul 4, 2008 at 10:04 am

    Cyril SCETBON wrote:
    Hi guys,

    Why postgres does not disallow creating redundants indexes ? Is it the
    same behaviour in postgresql 8.3 ?
    Why should it stop you?
    Do you have an application that randomly generates indexes?

    --
    Richard Huxton
    Archonet Ltd
  • Cyril SCETBON at Jul 4, 2008 at 10:13 am

    Richard Huxton wrote:
    Cyril SCETBON wrote:
    Hi guys,

    Why postgres does not disallow creating redundants indexes ? Is it the
    same behaviour in postgresql 8.3 ?
    Why should it stop you?
    Do you have an application that randomly generates indexes?
    I'm just wondering why postgresql/mysql work like that.

    --
    Cyril SCETBON
  • Richard Huxton at Jul 4, 2008 at 10:26 am

    Cyril SCETBON wrote:
    Richard Huxton wrote:
    Cyril SCETBON wrote:
    Hi guys,

    Why postgres does not disallow creating redundants indexes ? Is it the
    same behaviour in postgresql 8.3 ?
    Why should it stop you?
    Do you have an application that randomly generates indexes?
    I'm just wondering why postgresql/mysql work like that.
    You ask for a particular index, it creates the index. Why complicate the
    code checking for possible duplicate indexes if it's not a problem
    people tend to encounter?

    --
    Richard Huxton
    Archonet Ltd
  • Bernd Helmle at Jul 4, 2008 at 1:33 pm
    --On Freitag, Juli 04, 2008 11:26:31 +0100 Richard Huxton
    wrote:
    You ask for a particular index, it creates the index. Why complicate the
    code checking for possible duplicate indexes if it's not a problem people
    tend to encounter?
    And what would characterize a "duplicate" index? Think about partial
    indexes, where we actually _want_ to have multiple indexes for certain
    ranges on the same column.

    --
    Thanks

    Bernd
  • Cyril SCETBON at Jul 4, 2008 at 1:57 pm

    Bernd Helmle wrote:
    --On Freitag, Juli 04, 2008 11:26:31 +0100 Richard Huxton
    wrote:
    You ask for a particular index, it creates the index. Why complicate the
    code checking for possible duplicate indexes if it's not a problem
    people
    tend to encounter?
    And what would characterize a "duplicate" index? Think about partial
    indexes, where we actually _want_ to have multiple indexes for certain
    ranges on the same column.
    this case is not included in duplicate index for me.

    --
    Cyril SCETBON - Ingénieur bases de données
    AUSY pour France Télécom - OPF/PORTAILS/DOP/HEBEX

    Tél : +33 (0)4 97 12 87 60
    Jabber : cscetbon@jabber.org
    France Telecom - Orange
    790 Avenue du Docteur Maurice Donat
    Bâtiment Marco Polo C2 - Bureau 202
    06250 Mougins
    France

    ***********************************
    Ce message et toutes les pieces jointes (ci-apres le 'message') sont
    confidentiels et etablis a l'intention exclusive de ses destinataires.
    Toute utilisation ou diffusion non autorisee est interdite.
    Tout message electronique est susceptible d'alteration. Le Groupe France
    Telecom decline toute responsabilite au titre de ce message s'il a ete
    altere, deforme ou falsifie.
    Si vous n'etes pas destinataire de ce message, merci de le detruire
    immediatement et d'avertir l'expediteur.
    ***********************************
    This message and any attachments (the 'message') are confidential and
    intended solely for the addressees.
    Any unauthorised use or dissemination is prohibited.
    Messages are susceptible to alteration. France Telecom Group shall not be
    liable for the message if altered, changed or falsified.
    If you are not recipient of this message, please cancel it immediately and
    inform the sender.
    ************************************

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJul 4, '08 at 9:54a
activeJul 4, '08 at 1:57p
posts8
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase