public inbox for [email protected]  

help / color / mirror / Atom feed

From: Thomas Kellerer <[email protected]>
To: [email protected]
Subject: Re: Patch: Global Unique Index
Date: Wed, 23 Nov 2022 23:42:28 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

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 (=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


Just my 0.05€

view thread (33+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Patch: Global Unique Index
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox