[BUGS] BUG #2167: Performance degradation
The following bug has been logged online: Bug reference: 2167 Logged by: Sunil Basu Email address: [EMAIL PROTECTED] 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
Re: [BUGS] BUG #2167: Performance degradation
""Sunil Basu"" <[EMAIL PROTECTED]> wrote > > 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. > 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
[BUGS] BUG #2168: 45.000.000 records too much?
The following bug has been logged online: Bug reference: 2168 Logged by: Steven Mooij Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: Linux Description:45.000.000 records too much? Details: Tried to copy the result of a join into another table, about 45.000.000 (small) records. This is the result: testsearch=> insert into t_documentword2 (SELECT document_id, t_word2.id, frequency from t_documentword, t_word2 where t_documentword.word = t_word2.word); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. I got the same result in version 7.5.15. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #2169: excelente
The following bug has been logged online: Bug reference: 2169 Logged by: Luis Felipe Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.3 Operating system: windows Xp Description:excelente Details: Good trades I go to you since I am fiek usuary of the best motor of free BD Postgres.En this occasion I am working version 8.0.3 for Windows and need to execute flat archives in estructura.Como I can realizar.Gracias pos its collaboration or if..un has information agradeceria hug thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2169: excelente
On 1/13/06, Luis Felipe <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 2169 > Logged by: Luis Felipe > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.0.3 > Operating system: windows Xp > Description:excelente > Details: > > Good trades I go to you since I am fiek usuary of the best motor of free BD > Postgres.En this occasion I am working version 8.0.3 for Windows and need to > execute flat archives in estructura.Como I can realizar.Gracias pos its > collaboration or if..un has information agradeceria hug thanks > Sorry there is no spanglish list... please, post in english or in spanish in [EMAIL PROTECTED] (i recommend you do that) -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
