Thread: Column Index vs Record Insert Trade-off?

Is there a performance trade-off between column indexes and record inserts?<br /><br /> I know that in MS Access there
issuch a trade-off. This being indexes make SQL queries perform faster at the cost of record insert speed. Put another
way,the more column indexes in a table, the slower a record insert in that table performs.<br /><br /> Is there a
similartrade-off in PostgreSQL?  

Ken Hill wrote:
> Is there a performance trade-off between column indexes and record
> inserts?
> 
> I know that in MS Access there is such a trade-off. This being indexes
> make SQL queries perform faster at the cost of record insert speed. Put
> another way, the more column indexes in a table, the slower a record
> insert in that table performs.
> 
> Is there a similar trade-off in PostgreSQL?

The tradeoff is made in every system. The more indexes you have on a 
table, the more indexes need to be updated when you update/insert/delete 
rows.

So - a unique constraint automatically implies an index in PostgreSQL 
(it's how it implements its unique checks). Other than that, you should 
only index those columns where you will want a small number of rows or 
quick ordering.

--   Richard Huxton  Archonet Ltd