Index in postgresql

Question

Firstly, I have a table in database USERS with almost 30 Million records in it. I have different indices for each column. But some of the column have only 2 to 3 non null values while others are Null but still their index size is 847 MB a little less than the one index that contain unique value for each row.

Can anyone know why is it like this?

Secondly, in PostgreSQL we have a index for primary key index for each column by default what if we delete that index what will be the consequences? What that index is really use for? As i'm searching based on values in other columns only will it be safe to delete index for primary key?

If you'd add a few commas to your sentences, we'd understand you better. — Laurenz Albe
– Laurenz Albe, Commented May 28, 2020 at 15:21

Laurenz Albe · Accepted Answer · 2020-05-28 15:26:08Z

1

NULL values are stored in indexes just like all other values, so the first part is not surprising.

You cannot delete the primary key index, what you could do is drop the primary key constraint. But then you cannot be certain that no duplicate rows get added to the table. If you think that is no problem, look at the many questions asking for help with exactly that problem.

Every table should have a primary key.

But it might be a good idea to get rid of some other indexes if you don't need them.

answered May 28, 2020 at 15:26

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jim Macaulay · Accepted Answer · 2020-05-28 15:02:03Z

0

There is nothing called primary key index, seems to be you are talking about unique index. First of all you need to understand the difference between primary key and index. You can have only one primary key in a table. Primary key would be your unique identifier of each column and does not allow nulls. Index is used to speed up your fetching process on particular column and you can have one null if it is unique index. Deleting unique index in your table will not impact any thing apart from performance. Its your way of design to have index or not

answered May 28, 2020 at 15:02

Jim Macaulay

5,2515 gold badges32 silver badges60 bronze badges

3 Comments

user330315 Over a year ago

"There is nothing called primary key index" I guess Elizabeth is referring to the unique index that is automatically created when you declare a primary key. So in that sense, yes there is a "primary key index"

elizabeth lail Over a year ago

exactly @a_horse_with_no_name thanks for your explanation

Jim Macaulay Over a year ago

" There is nothing called primary key index, seems to be you are talking about unique index". Yes even i mentioned as unique index. Technically we cannot call it as primary key index. Index is different from primary key which can be unique or non unique

Collectives™ on Stack Overflow

Index in postgresql

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related