From: Gnanakumar <gnanam@zoniac.com>
To: 'pasman pasmanski' <pasman.p@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Is there a batch/bulk UPDATE syntax available?
Date: Fri, 4 Feb 2011 16:48:34 +0530
Message-ID: <006601cbc45d$453847b0$cfa8d710$@com> (raw)
In-Reply-To: <AANLkTimp3h7u0FVWB4ub2Y0p5s84ApPgX2KW-EK1FJBf@mail.gmail.com>
References: <005b01cbc399$e8d99fa0$ba8cdee0$@com>
<AANLkTimp3h7u0FVWB4ub2Y0p5s84ApPgX2KW-EK1FJBf@mail.gmail.com>
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 <gnanam@zoniac.com>:
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gnanam@zoniac.com, pasman.p@gmail.com
Subject: Re: Is there a batch/bulk UPDATE syntax available?
In-Reply-To: <006601cbc45d$453847b0$cfa8d710$@com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox