Subject: Re: Patch: Global Unique Index
Cc: pgsql-hackers@lists.postgresql.org
References: <184879c5306.12490ea581628934.7312528450011769010@highgo.ca>
 <3866851668762233@ja6lqgzbqb6tniny.sas.yp-c.yandex.net>
 <2187643.1668783992@sss.pgh.pa.us>
 <6cdcb7b1-2567-1373-79d1-96467a9b305b@gmx.net>
 <CAFj8pRCXadc7aCdcYKG-CP-v8q5rpoXLEGbH8z13g-3X4Y6MKQ@mail.gmail.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <fa359051-7474-671b-ce36-675fe9b5622b@gmx.net>
Date: Thu, 24 Nov 2022 16:00:59 +0100
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
In-Reply-To: 
 <CAFj8pRCXadc7aCdcYKG-CP-v8q5rpoXLEGbH8z13g-3X4Y6MKQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:Qg/yw/gBPN8=;vapu0Ph6oDG1xc00O7k5zTlL9rQ
 aiEZBUGxVOJ7bB8teCYxitYpfbWmEyWEi8tuRxv8WE2zQWQUQj6MBkVq+AzlraPRUc0xVTkJc
 ATy4M4mqxgfg9ahUbUSuF6MJrrkSviTmRCu4aKaxOAtI/RZJPxim70DhPjHCU0gmHHyDzJ/67
 NzESYphAsqwi/e79fceMaLUHRWHWRsFjAJIbEmrfW+lhTGwz5jc0yHTX3OXL+ViCzTXPhUV9W
 CCfgaNby1iMqtzzFPNYle8neE7yrEB7VkRkQPzoI70SPGBX3wa/ZN3QsHfdtgWu2fORb6nkT5
 CofMLsQa8jGSShkjhQqnPEZ+j9z5SrXmhSGrQ4dNEB+6hIpyAymd0Z3ucx7qbEweSiPWssMV4
 gxZo4aTycsD2eD64nHeiQQp0mkD/9bhh9YRXcRygddUVXiGQE+WSfCPqgiw8XVEoBhwDwySKL
 NsSVUNy/rGs0muTyKok0MIxjIvRUiSQZ76cOlD2hfDtbKdm3LrpNYSdnJJHgMOrSRDJbxZDDq
 sW+Q0Ns5xciEgZqnxRCodAUSjYhVEGhW1R8bBYiWu8x8bUl1qJYf1FgoPYxgJUOALwaSSoxp7
 YKtIe60nl/xRZFMlYlLX216WESR8QKP1fozOEdz10a/nyU3PQ1aCWx0tjdKLeY0k+5T4FVwNM
 a/TIo59d+fsPAHCTsNwYaPmaMfaln48lSaD/R6sfgrOg5qELlkpU8EOKBEgha0DO7vYz/1rWa
 zhOZj4LR1gK1+/X9kA5P8I1XITzBfadQZ5PvvUNsMj/sJfGS+OfZZLqBS3JQqSgZqUQcK4TVJ
 bJTCosjXZ8dybNQ1c2r3i/q714McwckP9S74pGTvRg2Sd+fLrqDjqhannmVeju3OSx3v8+k3D
 pvu1Tv4rjIfkMFpxHiuWxnmRB0ti999kqS/uUk8nIeYf1Yn+yo+FpleL+FRiZCpHlU6dsGo6m
 4PDVVQ==
Archived-At: 
 <https://www.postgresql.org/message-id/fa359051-7474-671b-ce36-675fe9b5622b%40gmx.net>
Precedence: bulk

Pavel Stehule schrieb am 24.11.2022 um 07:03:
>     There are many Oracle users that find global indexes useful despite
>     their disadvantages.
>
>     I have seen this mostly when the goal was to get the benefits of
>     partition pruning at runtime which turned the full table scan (=3DSe=
q Scan)
>     on huge tables to partition scans on much smaller partitions.
>     Partition wise joins were also helpful for query performance.
>     The substantially slower drop partition performance was accepted in =
thos cases
>
>
>     I think it would be nice to have the option in Postgres as well.
>
>     I do agree however, that the global index should not be created auto=
matically.
>
>     Something like CREATE GLOBAL [UNIQUE] INDEX ... would be a lot bette=
r
>
>
> Is it necessary to use special marks like GLOBAL if this index will
> be partitioned, and uniqueness will be ensured by repeated
> evaluations?
>
> Or you think so there should be really forced one relation based
> index?
>
> I can imagine a unique index on partitions without a special mark,
> that will be partitioned,  and a second variant classic index created
> over a partitioned table, that will be marked as GLOBAL.


My personal opinion is, that a global index should never be created
automatically.

The user should consciously decide on using a feature
that might have a serious impact on performance in some areas.