The following bug has been logged online:

Bug reference: 2167
Logged by: Sunil Basu
Email address: sunil.basu (AT) esspl (DOT) com
PostgreSQL version: 8.0
Operating system: Windows XP
Description: Performance degradation
Details:

I am connecting to Postgresql from my Delphi application.
My Delphi application connects to 2 databases- 1. MS Access which is my
business database and 2. Postgresql where I store data matching some
specific criteria from the business database.

I explain my problem issue:

Previously I used to insert records into the postgresql database
unconditionally. That is everytime a data comes it is stored in the
postgresql. So I land up with some redundant data always.
But the operation was smooth and near about 600 records could be inserted
per second.

Now I have made a check in the postgresql database that whether a record
exists depending on criteria which is set as per the index order defined for
my postgre table. I used a sql "Select 1 from ... where ..." statement for
checking in the postgresql.
Depending on the record count from the select query, I decide whether to
insert or to update.
Now I have noticed a considerable degradation in performance. Now near about
60-75 records can be updated/inserted per second.

I need help on this.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

""Sunil Basu"" <sunil.basu (AT) esspl (DOT) com> wrote
A performance degradation is expected because your new program do two more
things: query the index and maintain the index. But I am not sure how much.
Your method will not work if you do the insertition concurrently. There are
alternatives to prevent duplicates: (1) build a unique index on the
attributes and let PostgreSQL to prevent duplicates; (2) do it later in a
batch since your PostgreSQL is a backup - after you insert a lot of data
with some duplates, do a "INSERT INTO another_table SELECT DISTINCT FROM
this_table ...".

Regards,
Qingqing



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster