Postgres 고유 제약 조건과 인덱스 비교

Postgres 고유 제약 조건과 인덱스 비교

문서를 이해할 수 있듯이 다음 정의는 동일합니다.

create table foo (
    id serial primary key,
    code integer,
    label text,
    constraint foo_uq unique (code, label));

create table foo (
    id serial primary key,
    code integer,
    label text);
create unique index foo_idx on foo using btree (code, label);    

단, Postgres 9.4 매뉴얼에는 다음과 같이 기재되어 있습니다.

테이블에 고유 구속조건을 추가하는 권장 방법은 다음과 같습니다.ALTER TABLE ... ADD CONSTRAINT인덱스를 사용하여 고유한 제약을 적용하는 것은 구현 세부사항으로 간주할 수 있으며 직접 접근해서는 안 됩니다.

(편집: 이 노트는 Postgres 9.5에서 매뉴얼에서 삭제되었습니다.)

그것은 단지 좋은 스타일의 문제인가?이 변형들 중 하나를 선택할 경우(예: 성능)의 실질적인 결과는 무엇입니까?

나는 이 기본적이지만 중요한 문제에 대해 의구심이 들어 모범을 보이기로 결심했다.

2개의 컬럼으로 구성된 테스트테이블 마스터를 만듭니다.con_id with unique contraint와 ind_id indexed by unique index입니다.

create table master (
    con_id integer unique,
    ind_id integer
);
create unique index master_unique_idx on master (ind_id);

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_unique_idx" UNIQUE, btree (ind_id)

표 설명(psql의 \d)에서는 고유 제약조건과 고유 인덱스를 구분할 수 있습니다.

고유성

만약을 위해 독특함을 확인해 봅시다.

test=# insert into master values (0, 0);
INSERT 0 1
test=# insert into master values (0, 1);
ERROR:  duplicate key value violates unique constraint "master_con_id_key"
DETAIL:  Key (con_id)=(0) already exists.
test=# insert into master values (1, 0);
ERROR:  duplicate key value violates unique constraint "master_unique_idx"
DETAIL:  Key (ind_id)=(0) already exists.
test=#

예상대로 된다!

외부 키

이제 마스터에 있는 두 개의 열을 참조하는 두 개의 외부 키를 사용하여 세부 테이블을 정의합니다.

create table detail (
    con_id integer,
    ind_id integer,
    constraint detail_fk1 foreign key (con_id) references master(con_id),
    constraint detail_fk2 foreign key (ind_id) references master(ind_id)
);

    Table "public.detail"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Foreign-key constraints:
    "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

흠, 오류는 없습니다.확실히 작동하도록 합시다.

test=# insert into detail values (0, 0);
INSERT 0 1
test=# insert into detail values (1, 0);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk1"
DETAIL:  Key (con_id)=(1) is not present in table "master".
test=# insert into detail values (0, 1);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk2"
DETAIL:  Key (ind_id)=(1) is not present in table "master".
test=#

두 열 모두 외부 키로 참조할 수 있습니다.

인덱스를 사용한 제약 조건

기존의 고유 인덱스를 사용하여 테이블 구속조건을 추가할 수 있습니다.

alter table master add constraint master_ind_id_key unique using index master_unique_idx;

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_ind_id_key" UNIQUE CONSTRAINT, btree (ind_id)
Referenced by:
    TABLE "detail" CONSTRAINT "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    TABLE "detail" CONSTRAINT "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

이제 열 제약 조건 설명에는 차이가 없습니다.

부분 인덱스

테이블 제약 조건 선언에서는 부분 인덱스를 만들 수 없습니다.이는 의 정의에서 직접 유래한다.create table .... 에서는 .를 설정할 수 WHERE clause부분 인덱스를 만듭니다.표현식(열뿐만 아니라)에 인덱스를 작성하고 다른 매개변수(조합, 정렬 순서, NULL 배치)를 정의할 수도 있습니다.

부분 인덱스를 사용하여 테이블 제약 조건을 추가할 수 없습니다.

alter table master add column part_id integer;
create unique index master_partial_idx on master (part_id) where part_id is not null;

alter table master add constraint master_part_id_key unique using index master_partial_idx;
ERROR:  "master_partial_idx" is a partial index
LINE 1: alter table master add constraint master_part_id_key unique ...
                               ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

「」를 하는 또UNIQUE INDEX ★★UNIQUE CONSTRAINT 할 수 죠.DROP/CREATE그러나 제약 조건에서는 할 수 없습니다.

고유성은 제약사항입니다.인덱스는 기존 모든 값을 빠르게 검색하여 지정된 값이 이미 존재하는지 확인할 수 있기 때문에 고유한 인덱스를 생성하여 구현됩니다.

개념적으로 인덱스는 구현 세부사항이며 고유성은 제약조건에만 관련되어야 한다.

전문

따라서 속도 성능은 동일해야 합니다.

다양한 사람들이 고유한 제약조건에 비해 고유한 인덱스의 이점을 제공하므로 단점이 있습니다. 고유한 제약조건은 지연될 수 있으며(트랜잭션이 끝날 때만 확인), 고유한 인덱스는 지연될 수 없습니다.

할 수 있는 은 을 하는 입니다.ON CONFLICT ON CONSTRAINTclause(이 질문도 참조).

이것은 동작하지 않습니다.

CREATE TABLE T (a INT PRIMARY KEY, b INT, c INT);
CREATE UNIQUE INDEX u ON t(b);

INSERT INTO T (a, b, c)
VALUES (1, 2, 3)
ON CONFLICT ON CONSTRAINT u
DO UPDATE SET c = 4
RETURNING *;

작성 방법:

[42704]: ERROR: constraint "u" for table "t" does not exist

인덱스를 구속조건으로 변환합니다.

DROP INDEX u;
ALTER TABLE t ADD CONSTRAINT u UNIQUE (b);

★★★★★★★★★★★★★★★★.INSERT이치노

또한 SQL 식을 고유한 인덱스에서 사용할 수 있지만 제약 조건에서는 사용할 수 없습니다.

이것은 동작하지 않습니다.

CREATE TABLE users (
    name text,
    UNIQUE (lower(name))
);

계속 작업하고 있어요.

CREATE TABLE users (
    name text
);
CREATE UNIQUE INDEX uq_name on users (lower(name));

잠금에 차이가 있습니다.
인덱스를 추가해도 테이블에 대한 읽기 액세스는 차단되지 않습니다.
ALTER TABLE을 통해 추가되므로 구속조건을 추가하면 테이블 잠금이 설정됩니다(따라서 모든 선택 항목이 차단됩니다).

이 문서는 다음과 같습니다.

ADD table_constraint [비활성]

은 테이블과 하여 새로운 합니다.CREATE TABLE 「」을 .NOT VALID이는 현재 외부 키 제약에 대해서만 허용됩니다.이 " " "로 표시되어 .NOT VALID테이블 내의 모든 행이 제약조건을 충족하는지 확인하기 위해 잠재적인 초기 체크를 건너뜁니다.제약조건은 후속 삽입 또는 업데이트에 대해 계속 적용됩니다(즉, 참조 테이블에 일치하는 행이 없는 한 실패합니다).단, 데이터베이스는 VALIDATE CONSTRARECT 옵션을 사용하여 검증될 때까지 테이블 내의 모든 행에 제약 조건이 유지된다고 가정하지 않습니다.

그래서 저는 제약조건을 더해서 '부분적 고유성'이라고 부릅니다.

또, 일의성을 확보하는 방법에 대해서:

고유한 제약 조건을 추가하면 제약 조건에 나열된 열 또는 열 그룹에 고유한 B-트리 인덱스가 자동으로 생성됩니다.일부 행에만 적용되는 고유성 제한은 고유 제약으로 기술할 수 없지만 고유한 부분 인덱스를 생성하여 이러한 제한을 적용할 수 있습니다.

주의: 테이블에 고유 구속조건을 추가할 때 권장되는 방법은 ALTER TABLE ... ADD RESTRAINT입니다.인덱스를 사용하여 고유한 제약을 적용하는 것은 구현 세부사항으로 간주될 수 있으며 직접 액세스해서는 안 됩니다.그러나 고유한 열에 수동으로 인덱스를 만들 필요가 없습니다. 그러면 자동으로 생성된 인덱스가 복제될 뿐입니다.

따라서 고유성을 보장하기 위해 인덱스를 생성하는 구속조건을 추가해야 합니다.

이 문제를 어떻게 봐야 할까요?

"제약"은 이 열이 고유해야 한다는 것을 문법적으로 보장하는 것을 목표로 하며, "인덱스"는 의미론적인 의미이며, "실행 방법, 고유성 달성 방법, 구현 시 고유성이 무엇을 의미하는지"에 관한 것입니다.따라서 Postgresql의 구현 방법은 매우 논리적입니다.첫 번째로 열이 고유해야 한다고 선언한 후 Postgresql에 의해 고유 인덱스를 추가하는 구현이 추가됩니다.

다른 답변과 더불어 고유 제약 조건을 사용하여 인덱스와 같이 쿼리 속도를 높일 수 있는지 여부도 있습니다.

EXPLAY(설명)에 표시된 대로 인덱스 스캔에 제약 조건이 실제로 사용됩니다.

ALTER TABLE mytable
    ADD CONSTRAINT mytable_uc UNIQUE (other_id, name);

explain select * from mytable
    where name = 'name' and other_id = 154

결과:

Index Scan using mytable_uc on mytable  (cost=0.28..2.29 rows=1 width=101)
  Index Cond: ((other_id = 154) AND ((name)::text = 'name'::text))

SELECT a.phone_number,count(*) FROM public.users a
Group BY phone_number Having count(*)>1;

SELECT a.phone_number,count(*) FROM public.retailers a
Group BY phone_number Having count(*)>1;

select a.phone_number from users a inner join users b
on a.id <> b.id and a.phone_number = b.phone_number order by a.id;


select a.phone_number from retailers a inner join retailers b
on a.id <> b.id and a.phone_number = b.phone_number order by a.id

DELETE FROM
    users a
        USING users b
WHERE
    a.id > b.id
    AND a.phone_number = b.phone_number;
    
DELETE FROM
    retailers a
        USING retailers b
WHERE
    a.id > b.id
    AND a.phone_number = b.phone_number;
    
CREATE UNIQUE INDEX CONCURRENTLY users_phone_number 
ON users (phone_number);

확인하는 방법:

insert into users(name,phone_number,created_at,updated_at) select name,phone_number,created_at,updated_at from users

언급URL : https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index