Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l4MHc-0003EC-Sl for pgsql-admin@arkaria.postgresql.org; Tue, 26 Jan 2021 11:13:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l4MHb-0001nE-Pm for pgsql-admin@arkaria.postgresql.org; Tue, 26 Jan 2021 11:13:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l4MHb-0001n7-Gb for pgsql-admin@lists.postgresql.org; Tue, 26 Jan 2021 11:13:15 +0000 Received: from mout.kundenserver.de ([217.72.192.75]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l4MHX-0004EN-G4 for pgsql-admin@lists.postgresql.org; Tue, 26 Jan 2021 11:13:15 +0000 Received: from ANAT ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue108 [212.227.15.184]) with ESMTPSA (Nemesis) id 1N95qT-1m6B3U2oLf-016BhD; Tue, 26 Jan 2021 12:13:07 +0100 From: "Michel SALAIS" To: "'Kanninen Anssi EXT'" , References: <3d00c1f4-646c-59b7-e4dc-1d51190d2139@gmail.com> <5ebaba7ee42d89f8bfb2599de203612024c86e8e.camel@cybertec.at> <6a847296-d02f-6582-3dcb-e3e60dbc56b6@gmx.net> In-Reply-To: Subject: RE: Unique constraint across all partitions? Date: Tue, 26 Jan 2021 12:13:04 +0100 Message-ID: <00c201d6f3d4$381f9ae0$a85ed0a0$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00C3_01D6F3DC.99E49F20" X-Mailer: Microsoft Outlook 16.0 Content-Language: fr Thread-Index: AQIbk7mTZvVE9XaFdPy957vQXBbmbgLDRAsuAbKGqmcBU2S+dQJwinN8AoGuyYYCeIegEwIQog8VqTZadiA= X-Provags-ID: V03:K1:rdeN3EFKVRXsychuaElkmMrmNbXVedFa0dTvPnK6LjVakgwj0/7 UShjRMG1nUXL0hy76Lq5j8fL079Nng8RR0gfuhxzuyhqBUWAvpA+Tzt0aUOymzCPLEbOWZO KQxgOMvOJn+08k6EDoCXnIpfw2Tayh3o+5WpU44sCOX7OCSyHm36ZqGyykSKDXDrR6nGhkv WN4AJgPNa/rbJ1vlXG7cw== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:4VxYguuH3xc=:Ew3vkVrr52NpBNvzJGpLT9 jd2wy1+E3LiIndZfbErUuS3qK1cC3g4ClbZmjesJttbKX3CWwv4eoB8dHm387lyB+SMlNhXY1 c9yDwLg41w9vLlSMLE1PVnWM0r3UeJVFf3ywljLq6x7U1YEtM7wLSUxHnWn1gb6FuK/QE5rfE seu1iN4rJkfmxIpVv9VBsDTGCrW77DO7RPtWU4TlIiUMv/caIX0u+Pos8LDQEozAmtPpsAZ3n j5Xx5S7lkWNnzY5o30qYi9posll5swiV01xOAUNas6KUlaF+TtKyfWceoNbGBZ+4Ya8fD5uFe PZ2zBwWTFVUwG1TS+JYkdWJPxVvdKekvTM2aCd6duMHTS+YVThygpMnOJRheRKyPBxKtxy9xD Y30flg2o40GcjsKieGEcih8Pkv9I8NdKVshgod9QRcim2UzgQWog6kbxquRSE List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_00C3_01D6F3DC.99E49F20 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi all, =20 Global indexes are useful in Oracle for performance when indexed columns = are different from partitioning key. An =E2=80=9Cindex scan=E2=80=9D = (well it is named =E2=80=9Crange scan=E2=80=9D 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. =20 Regards =20 Michel SALAIS Consultant Oracle, PostgreSQL MSYM 23 rue du D=C3=A9part Bo=C3=AEte 37 75014 Paris =20 +33 6 68 94 19 60 msalais@msym.fr www.msym.fr =20 De : Kanninen Anssi EXT =20 Envoy=C3=A9 : mardi 26 janvier 2021 11:09 =C3=80 : pgsql-admin@lists.postgresql.org Objet : RE: Unique constraint across all partitions? =20 But why use global indexes? Oracle also has local partitioned indexes which are just like we would = like to have in PostgreSQL. =20 From: Victor Yegorov >=20 Sent: tiistai 26. tammikuuta 2021 11.54 To: Thomas Kellerer > Cc: pgsql-admin@lists.postgresql.org = =20 Subject: Re: Unique constraint across all partitions? =20 =D0=B2=D1=82, 26 =D1=8F=D0=BD=D0=B2. 2021 =D0=B3. =D0=B2 09:48, Thomas = Kellerer >: > 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. =20 --=20 Victor Yegorov ------=_NextPart_000_00C3_01D6F3DC.99E49F20 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi = all,

 

Global indexes are useful in Oracle = for performance when indexed columns are different from partitioning = key. An =E2=80=9Cindex scan=E2=80=9D (well it is named =E2=80=9Crange = scan=E2=80=9D 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=C3=A9part

Bo=C3=AEte = 37

75014 Paris

 

+33 6 68 94 19 = 60

msalais@msym.fr<= /p>

www.msym.fr

<= /div>

 

De : Kanninen Anssi EXT = <Anssi.Kanninen@digia.com>
Envoy=C3=A9 : mardi 26 = janvier 2021 11:09
=C3=80 : = pgsql-admin@lists.postgresql.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@gmail.com> =
Sent: tiistai 26. tammikuuta 2021 11.54
To: Thomas = Kellerer <shammat@gmx.net>
Cc: pgsql-admin@lists.postgr= esql.org
Subject: Re: Unique constraint across all = partitions?

 

=D0=B2=D1=82, 26 =D1=8F=D0=BD=D0=B2. = 2021 =D0=B3. =D0=B2 09:48, Thomas Kellerer <shammat@gmx.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

------=_NextPart_000_00C3_01D6F3DC.99E49F20--