Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 666061337B2F for ; Fri, 4 Feb 2011 07:21:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 61286-06 for ; Fri, 4 Feb 2011 11:21:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from rs73.luxsci.com (rs73.luxsci.com [64.49.254.135]) by mail.postgresql.org (Postfix) with ESMTP id BD5EA1337B54 for ; Fri, 4 Feb 2011 07:21:50 -0400 (AST) Received: from GNANAM ([180.151.61.10]) (authenticated bits=0) by rs73.luxsci.com (8.13.8/8.13.8) with ESMTP id p14BLj5I023987; Fri, 4 Feb 2011 05:21:47 -0600 Reply-To: From: "Gnanakumar" To: "'pasman pasmanski'" Cc: References: <005b01cbc399$e8d99fa0$ba8cdee0$@com> In-Reply-To: Subject: Re: Is there a batch/bulk UPDATE syntax available? Date: Fri, 4 Feb 2011 16:48:34 +0530 Organization: Zoniac Software Private Limited Message-ID: <006601cbc45d$453847b0$cfa8d710$@com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcvD4TaF+9DIbTWgRtCXSW9Xq683ygAezvKw Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201102/18 X-Sequence-Number: 36657 Here is my UPDATE query and EXPLAIN ANALYZE output: UPDATE query: UPDATE MYUPDATETABLE SET ABOOLEANCOLUMN = TRUE WHERE COMPANYID = 999 AND EMAIL1 = 'someemail@somedomain.com' EXPLAIN ANALYZE output: Bitmap Heap Scan on myupdatetable (cost=190.89..16107.70 rows=45 width=1826) (actual time=31.150..31.155 rows=2 loops=1) Recheck Cond: (companyid = 999::numeric) Filter: ((email1)::text = 'someemail@somedomain.com'::text) -> Bitmap Index Scan on mut_cmpid_indx (cost=0.00..190.88 rows=9051 width=0) (actual time=13.868..13.868 rows=60206 loops=1) Index Cond: (companyid = 999::numeric) Total runtime: 31.319 ms -----Original Message----- From: pasman pasmanski [mailto:pasman.p@gmail.com] Sent: Friday, February 04, 2011 2:01 AM To: gnanam@zoniac.com Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Is there a batch/bulk UPDATE syntax available? Show us explain analyze for this update. 2011/2/3, Gnanakumar : > Hi, > > Is there a batch/bulk UPDATE query syntax available in PostgreSQL, similar > to multirow VALUES syntax available for INSERT? > > INSERT Multirow VALUES syntax example: > INSERT INTO films (code, title, did, date_prod, kind) VALUES > ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), > ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'); > > There is a situation in my application, where I am performing lots and lots > of updates on individual rows. I am trying to figure out how to make the > updates faster. > > Any other ideas/ways to make updates faster are highly appreciated. > > Regards, > Gnanam > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > -- ------------ pasman