postgresql when it's not your job

20:53

Indexes Are Not Cheap

11 February 2016

I’ve noticed an increasing tendency in PostgreSQL users to over-index tables, often constructing very complex partial indexes to try to speed up very particular queries.

Be careful about doing this. Not only do additional indexes increase the plan time, they greatly increase insert time.

By way of example, I created a table with a single bigint column, and populated it with:

time psql -c "insert into t select generate_series(1, 100000000)"

That run without any indexes took 1 minute, 55 seconds; that run with eight indexes on the same table took 26 minutes, 39 seconds, or almost 14 times slower.

Regularly consult pg_stat_user_indexes and drop indexes that aren’t being used. Your disk space and insert times will thank you.

Thom Brown at 13:39, 12 February 2016:

I had requested that we see how much time it takes to update an index in the EXPLAIN ANALYSE output, but I didn’t really get an enthusiastic response. I wanted to see how much time was spent maintaining indexes vs updating the table. I still think that would be useful.