Re: indexes greatly slowing data entry

"DE Sesa, Michael C." <michael(dot)desesa(at)exeloncorp(dot)com> writes:
> With both varchar indexes in place, data for the table with the varchar
> index created second would take 10 times as long to store data into compared
> to the other table. From vmstat: blocks in (bi) is 10 times higher with
> both varchar indexes when compared to with only one, and it looks like the
> raid array is maxed out.

How much RAM do you have, and what is your shared_buffers setting?
I'm speculating that you're simply thrashing because you don't have
enough buffers to retain the top layers of all four indexes.

Also, is the varchar data reasonably unique, or are there many duplicate
values? btree is known to have some performance problems with large
numbers of equal keys, particularly in pre-7.1 releases.

> pg 7.0.3 on Red Hat Linux 7.1.

Running a more modern version might help.

regards, tom lane