Subject: Re: Patch: Global Unique Index
To: pgsql-hackers@lists.postgresql.org
References: <184879c5306.12490ea581628934.7312528450011769010@highgo.ca>
 <3866851668762233@ja6lqgzbqb6tniny.sas.yp-c.yandex.net>
 <2187643.1668783992@sss.pgh.pa.us>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <6cdcb7b1-2567-1373-79d1-96467a9b305b@gmx.net>
Date: Wed, 23 Nov 2022 23:42:28 +0100
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21)
 Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
MIME-Version: 1.0
In-Reply-To: <2187643.1668783992@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:B3G4NUS2mWI=;y/rPC4y54XH6sf/KCUOUGFW/5RU
 uwD3i50EWJ276ZzKJfgrQOJCa0LRl197+38yJ6X/rxPi7vvuB4uqqfOSvZjxV997gnPMRbZFm
 fKd1GnInbpD27NZWmD5LWnHa0CMedH0lpdn3pMTX4zM4kIyUc4BVbR7AySA/7agBJP3Xvl0v9
 Wd7TvEnVzVv50Vz5u4PxBapnya0MwSP9CHIaMIJsVn/LITFoxrThRqrJH+YgFF1cQ9WEurDdv
 E02Eed07WUpKCZXhQiZDy9V19HcN18gyLkGiBmLT8SloUN8qKLEzjPBR1mzJsic/J9SdlxEUP
 OoxwXyQ2YkPaTlOM79DkaS/0b+Ga400aAHI8w+avGc0f1w2KDgJl7o4pBhwHXXsdy4wNRuLnc
 DR5ybJIToGK3OuKguOJZrCI/UQIw5m7kPOUL52XuBXsp3Joa5wmip3REZtEL1YSBNc9HIQpy8
 N9rIIQ/jCsZFqaQ2aF7e79OxHaQJeu1ipZzdqCtbsvemwVCgGoDnYvv35bMoeCKWZy7UZ6+xI
 5s407YeJpum0AFmDN7iQQTiTk2eiBMfQrW4MIDYigtt7ZiPVPuZTyAhSB72ujWYZAcZONNFU3
 s1++zXg+YyRFWmJQhxBU6sPMPBiZizk2yXof8/eJCjUv6DV1Yer8AnrjsqFtr9VovU+PlPk+i
 AeSKdTlhC6fc/1i3p/kt7XEYPf/RSrCylwE5e0FYpHtnKuCcVuDxPbFAtImH4h5Wyv68X0MAt
 J65ynvw3bHQnTDwS2sLpgpvs6U7roUHQ0QbcmTCsdpn7C8INeEnT7jQy31XM/oKwpPXsotT/Z
 sxE3fj9gAmNpfFfeLUBi3Kw24fQajK6TmZ+PY/HpORoGwsT2wl3Lchr4gLE0CmcJovfFSGmkB
 G/lj5egzAXCv8VB4YGeqdvBeeMsaZ66gLXo84gUaT6WbhtCRZw89lm4sY523msTKorG1IeYzD
 c3tqKw==
Archived-At: 
 <https://www.postgresql.org/message-id/6cdcb7b1-2567-1373-79d1-96467a9b305b%40gmx.net>
Precedence: bulk

Tom Lane schrieb am 18.11.2022 um 16:06:
>> Do we need new syntax actually? I think that a global unique index
>> can be created automatically instead of raising an error "unique
>> constraint on partitioned table must include all partitioning
>> columns"
>
> I'm not convinced that we want this feature at all: as far as I can
> see, it will completely destroy the benefits of making a partitioned
> table in the first place.  But if we do want it, I don't think it
> should be so easy to create a global index by accident as that syntax
> approach would make it.  I think there needs to be a pretty clear YES
> I WANT TO SHOOT MYSELF IN THE FOOT clause in the command.

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 (=3DSeq 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 c=
ases

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 automatica=
lly.

Something like CREATE GLOBAL [UNIQUE] INDEX ... would be a lot better


Just my 0.05=E2=82=AC