Unique index on PostgreSQL text column - can it cause high CPU load?

Question

I have created a unique index on a text column for a PostgreSQL table. The database right now has over 10,000,000 rows and counting. I noticed my database has a HUGE CPU load (over 40.00%) when INSERT/UPDATE statements are executed on this table. However, when these inserts/updates are finished, the CPU load drops back down to normal levels.

Is the unique index defined upon the aformentioned text column hurting the performance of my database? Or should I check something else?

Answer 1

For my personal views: yes; the unique index on a text a column is hurting the performance，especially when many INSERTS/UPDATES happen on the table. If your query does not need the unique index, I suggest you drop the unique index.