We have a table with about 20 columns in a Postgres 9.2.10 DB. For better performance on some SELECT queries, we plan to add an index on one column with data type timestamp. As the index also can slow down the performance on inserts, we did the following performance test:
We inserted 5 million records into the table. That's the max. number of records we expect in production. Then we measured the time to insert 10000 records with and without index on the timestamp column. This is the maximum number of inserts we expect per day, In peaks not more than 5 inserts per second.
Here the results:
- With index – 84 441 ms
- Without index – 78 000 ms
At least for this test, the index slowed down the performance only slightly. For our requirements, I do not see an issue to add the index.
But this was only one test in a lab environment, are there any other pitfalls when running this on a production database? Can we get into a situation, where an INSERT suddenly takes more than 5 seconds under specific circumstances?
Best Answer
No, a basic btree index on a small column is typically cheap to maintain. Of course it gets slightly more expensive when the table has accumulated some bloat from dead rows, but the difference should be small. And you have to consider additional storage on disk for the index.
One thing seems worth mentioning: Updates on columns involved in an index in any way cannot use H.O.T. updates ("heap-only tuples"), so this might add some cost for updates changing the indexed column. But still, this would just prevent one internal optimization, no big deal. And only relevant if you involve the column in updates.