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 (=Seq 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 > automatically. > > Something like CREATE GLOBAL [UNIQUE] INDEX ... would be a lot better > > > 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.