From: | "Michel SALAIS" <msalais(at)msym(dot)fr> |
---|---|
To: | "'Kanninen Anssi EXT'" <Anssi(dot)Kanninen(at)digia(dot)com>, <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | RE: Unique constraint across all partitions? |
Date: | 2021-01-26 11:13:04 |
Message-ID: | 00c201d6f3d4$381f9ae0$a85ed0a0$@msym.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi all,
Global indexes are useful in Oracle for performance when indexed columns are different from partitioning key. An “index scan” (well it is named “range scan” in Oracle) using a local index has to navigate through all index partitions in that case. For a global index, it is only one tree to scan and this is more efficient than doing the same for a lot of them.
Regards
Michel SALAIS
Consultant Oracle, PostgreSQL
MSYM
23 rue du Départ
Boîte 37
75014 Paris
+33 6 68 94 19 60
<mailto:msalais(at)msym(dot)fr> msalais(at)msym(dot)fr
<http://www.msym.fr/> www.msym.fr
De : Kanninen Anssi EXT <Anssi(dot)Kanninen(at)digia(dot)com>
Envoyé : mardi 26 janvier 2021 11:09
À : pgsql-admin(at)lists(dot)postgresql(dot)org
Objet : RE: Unique constraint across all partitions?
But why use global indexes?
Oracle also has local partitioned indexes which are just like we would like to have in PostgreSQL.
From: Victor Yegorov <vyegorov(at)gmail(dot)com <mailto:vyegorov(at)gmail(dot)com> >
Sent: tiistai 26. tammikuuta 2021 11.54
To: Thomas Kellerer <shammat(at)gmx(dot)net <mailto:shammat(at)gmx(dot)net> >
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org <mailto:pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: Unique constraint across all partitions?
вт, 26 янв. 2021 г. в 09:48, Thomas Kellerer <shammat(at)gmx(dot)net <mailto:shammat(at)gmx(dot)net> >:
> Global indexes are likely more pain than gain.
While not extremely popular, many Oracle users do find them useful.
I've heard and been advised many times exactly the opposite for ORACLE,
especially when performance is important.
Global indexes makes a 15-minute maintenance job into a 9-hour downtime experience.
--
Victor Yegorov
From | Date | Subject | |
---|---|---|---|
Next Message | Dirk Krautschick | 2021-01-26 11:20:10 | productive usage of PostgreSQL with Windows Server |
Previous Message | Thomas Kellerer | 2021-01-26 11:08:08 | Re: Unique constraint across all partitions? |