From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | Anna Akenteva <a(dot)akenteva(at)postgrespro(dot)ru>, pgsql-hackers(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ... |
Date: | 2020-09-04 17:31:27 |
Message-ID: | 20200904173127.GA23596@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2020-Sep-04, Laurenz Albe wrote:
> On Fri, 2020-09-04 at 10:41 -0400, Alvaro Herrera wrote:
> > > The value I see in this is:
> > > - replacing a primary key index
> > > - replacing the index behind a constraint targeted by a foreign key
> >
> > But why is this better than using REINDEX CONCURRENTLY?
>
> It is not better, but it can be used to replace a constraint index
> with an index with a different INCLUDE clause, which is something
> that cannot easily be done otherwise.
I can see that there is value in having an index that serves both a
uniqueness constraint and coverage purposes. But this seems a pretty
roundabout way to get that -- I think you should have to do "CREATE
UNIQUE INDEX ... INCLUDING ..." instead. That way, the fact that this
is a Postgres extension remains clear.
55432 14devel 24138=# create table foo (a int not null, b int not null, c int);
CREATE TABLE
Duración: 1,775 ms
55432 14devel 24138=# create unique index on foo (a, b) include (c);
CREATE INDEX
Duración: 1,481 ms
55432 14devel 24138=# create table bar (a int not null, b int not null, foreign key (a, b) references foo (a, b));
CREATE TABLE
Duración: 2,559 ms
Now you have a normal index that you can reindex in the normal way, if you need
it.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2020-09-04 17:35:19 | Re: Improving connection scalability: GetSnapshotData() |
Previous Message | Bruce Momjian | 2020-09-04 17:28:11 | Re: [PATCH] Comments related to " buffer descriptors“ cache line size" |