CODEWITHSUNDEEP
postgresql
Cornos
Cornos

Reputation: 283

Multiple unique constraint performance

Imagine we have those tables with no primary key:

CREATE TABLE test (
  name1 INT,
  name2 INT,
  name3 INT,
  
  UNIQUE (name1, name2, name3)
);

CREATE TABLE test2 (
  name1 INT,
  name2 INT,
  name3 INT,
  
  UNIQUE (name1, name2)
);

I feel like those two tables are exactly the same somehow, I am not sure if the combinations are the same. If you have a trick to know about combinations, I ll be more than happy to know about it.

In case of performance is it the same to add an unique constraint on 2 columns and an unique constraint on let say 5 or 6 columns? I imagine that we are just adding one pointer per constraint?

Upvotes: 1

Views: 225

Answers (1)

SQLpro
SQLpro

Reputation: 5131

Each unique SQL constraint translates into a definition stored in a system table (the set of these system tables forming what is called the CATALOG) but also into an index which speeds up the verification of this uniqueness. The number of columns in the index as the length of the information has little influence on the duration of this check because the search algorithm is logarithmic...

In your case, the difference of performance will be negligible

Upvotes: 2

Related Questions