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

On Mon, Jan 30, 2012 at 1:27 AM, Saurabh <saurabh(dot)b85(at)gmail(dot)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.

Inserting into a indexed table causes a lot of random access to the
underlying index (unless the data is inserted in an order which
corresponds to the index order of all indexes, which is not likely to
happen with multiple indexes). As soon as your indexes don't fit in
cache, your performance will collapse.

What if you don't have the integer index but just the text? What is
the average length of the data in the text field? Is your system CPU
limited or IO limited during the load?

> My question is how I
> can improve performance when inserting data using index on text
> column?

The only "magic" answer is to drop the index and rebuild after the
insert. If that doesn't work for you, then you have to identify your
bottleneck and fix it. That can't be done with just the information
you provide.

Cheers,

Jeff