Re: PostgreSQL Performance issue

A(dot)Bhattacharya(at)sungard(dot)com wrote:
> I have *622,000 number of records *but it is taking almost *4 and half
> hours* to load these data into the tables.
>
> I have a simple function in db which is being called from Java batch
> program to populate the records into tables from flat files.
>

Four likely possibilities here, in order of how easy they are to test
for and therefore resolve:

1) Your hardware doesn't handle commits very well. You can turn off the
synchronous_commit command while doing the data loading to see if that
helps. See http://www.postgresql.org/docs/8.3/static/wal-async-commit.html

2) You're doing a commit after every single transaction. See
http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html
for an intro to disabling this. Common practice is to commit every 100
to 1000 transactions instead.

3) The simple function in your database is executing very slowly.

4) The design of the database involves a lot of overhead, such as
difficult to process foreign keys or an excess of indexes on some tables.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us