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