Re: Update command too slow

Oops! venkatbabukr(at)yahoo(dot)com (Venkatesh Babu) was seen spray-painting on a wall:
> There aren't any triggers but there are 75262 update
> statements. The problem is that we have a datatype
> called as "Collection" and we are fetching the data
> rows into it, modifying the data and call
> Collection.save(). This save method generates one
> update satement per record present in it.

Is that "Collection" in your application the entire table?

If it is, then you can get a nice win thus:

--> Delete from stbl; --- One fast statement
--> COPY stbl from stdin; --- Another fast statement
row 1 data
row 2 data
row 3 data
...
row 75262 data
\.

That update would be REALLY fast!

Even if it isn't, consider trying the following transaction:

BEGIN;
select * into temp table stbl_12341 from stbl limit 0;
-- Note that 12341 is the PID of your process, so that should be
-- pretty unique

copy stbl_12341 from stdin; -- Load your 75262 rows in one fell swoop
row 1 data
row 2 data
...
row 75262 data
\.

-- Now, delete from stbl all the rows that are in the replacement table...
delete from stbl where pkey in (select pkey from stbl_12341);
insert into stbl (select * from stbl_12341);
COMMIT;

Both approaches will be WAY faster than doing the processing row by
row.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/postgresql.html
"Waving away a cloud of smoke, I look up, and am blinded by a bright,
white light. It's God. No, not Richard Stallman, or Linus Torvalds,
but God. In a booming voice, He says: "THIS IS A SIGN. USE LINUX, THE
FREE Unix SYSTEM FOR THE 386." -- Matt Welsh