Part-12 Index tuning for Complex Query in PostgreSQL

 

Primary key and Unique Constraint Should be very clear now .Primary key and Unique Constraint both of them enforces uniqueness on the column. When either a primary key and unique constraint is created, They automatically create unique index, which is B-tree structure behind the scenes. This means you do not have to create unique index specifically on either of these columns If you create it, that would be just redundant or it is unnecessary indexes can degrade performance for your application .Postgres product team, has clear guidance for us when it is about unique index and there is no need to create unique index on those columns. if you still happen to create unique index on those columns where there is unique constraint or primary key, it will be just duplicate index, and it is absolutely not recommended .

Check primary key and constraint and Catalog :

Note:- It is also unique and it is also a primary key. Both of these properties are property of primary key, so when we created primary key it automatically contains unique value, as well as created index .It is very clear that even though we do not see a dedicated entry in indexes table, Index is definitely created on sampletable

Create table sampletable( Id int primary key,name varchar (10));

Example : For create index 

Ceate the index  SampleTable_id on table Sampletable, and query is executed successfully.

Now To verify that, let's execute this statement, Now we can see two different entries in this table. We can see here is the name of index which we have just created However, we had not specified unique index column is false. Now you can clearly see that when we had created primary key on this table it had automatically behind the scene created index Even though this index entry was not visible in Object Brower,