X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 01E3ED1BA35 for ; Wed, 10 Mar 2004 03:35:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 41546-07 for ; Tue, 9 Mar 2004 23:35:28 -0400 (AST) Received: from ideafix.webnow.com.br (ideafix.webnow.com.br [200.155.4.67]) by svr1.postgresql.org (Postfix) with ESMTP id 29E6ED1B54E for ; Tue, 9 Mar 2004 23:35:20 -0400 (AST) Received: by ideafix.webnow.com.br (Postfix, from userid 48) id DB956198F2B; Wed, 10 Mar 2004 00:35:15 -0300 (BRT) Received: from 148100.cps.virtua.com.br ([200.174.148.100]) (SquirrelMail authenticated user marcus.magalhaes@vlinfo.com.br) by webmail.webnow.com.br with HTTP; Wed, 10 Mar 2004 00:35:15 -0300 (BRT) Message-ID: <62371.200.174.148.100.1078889715.squirrel@webmail.webnow.com.br> Date: Wed, 10 Mar 2004 00:35:15 -0300 (BRT) Subject: optimizing large query with IN (...) From: "Marcus Andree S. Magalhaes" To: X-Priority: 3 Importance: Normal X-Mailer: SquirrelMail (version 1.2.11) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/125 X-Sequence-Number: 5981 Guys, I got a Java program to tune. It connects to a 7.4.1 postgresql server running Linux using JDBC. The program needs to update a counter on a somewhat large number of rows, about 1200 on a ~130k rows table. The query is something like the following: UPDATE table SET table.par = table.par + 1 WHERE table.key IN ('value1', 'value2', ... , 'value1200' ) This query runs on a transaction (by issuing a call to setAutoCommit(false)) and a commit() right after the query is sent to the backend. The process of committing and updating the values is painfully slow (no surprises here). Any ideas? Thanks.