On Mon, Jan 30, 2012 at 1:27 AM, Saurabh <saurabh.b85@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
