Re: How to improve insert speed with index on text column

On Mon, Jan 30, 2012 at 6:27 AM, Saurabh <saurabh.b85@gmail.com> wrote:
> Hi all,
>
> I am using Postgresql database for our project and doing some
> performance testing. We need to insert millions of record with indexed
> columns. We have 5 columns in table. I created index on integer only
> then performance is good but when I created index on text column as
> well then the performance reduced to 1/8th times. My question is how I
> can improve performance when inserting data using index on text
> column?

Post all the necessary details. Schema, table and index sizes, some config...

Assuming your text column is a long one (long text), this results in
really big indices.
Assuming you only search by equality, you can make it a lot faster by hashing.
Last time I checked, hash indices were quite limited and performed
badly, but I've heard they improved quite a bit. If hash indices don't
work for you, you can always build them on top of btree indices by
indexing on the expression hash(column) and comparing as hash(value) =
hash(column) and value = column.
On a table indexed by URL I have, this improved things immensely. Both
lookup and insertion times improved.