Thread: redundants indexes can be created
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
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 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
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 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
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
--On Freitag, Juli 04, 2008 11:26:31 +0100 Richard Huxton
<dev@archonet.com> 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
Bernd Helmle wrote: > --On Freitag, Juli 04, 2008 11:26:31 +0100 Richard Huxton > <dev@archonet.com> 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. ************************************