On Tue, 2007-07-03 at 21:52 +0000, Christopher Browne wrote:
> 1.  Delete with the 5M individual DELETE statements.  (Which you found 
> took 552.49s)
> 2.  Delete with 50K DELETE statements, each having a WHERE clause with 
> 100 items in it.
> 3.  Delete with 5K DELETE statements, each having a WHERE clause with 1K 
> items in it.
> 
> If 2. or 3. come *way* closer to 9.41s, then it may be worth exploring 
> the complexity of folding together adjacent deletes on the same table.  
> There could also be a case made for trying sequential versus random 
> orderings (e.g. - in the former case, each DELETE statement takes on a 
> specific range of items whereas in the latter, each selects items more 
> or less at random).
> 
> I'll see about constructing a series of tests like this; won't be 
> running before I send this :-).  If you have time to generate 2. and/or 
> 3., on your system and get timings there, I'd be much obliged.
> 

I apologize for the slow response. Here are my results:

DELETE 5000000 at a time: 19.571929
DELETE    1000 at a time: 15.999146
DELETE     100 at a time: 18.946113
DELETE       1 at a time: 770.507714
UPDATE 5000000 at a time: 94.686762
UPDATE    1000 at a time: 86.327752
UPDATE     100 at a time: 103.473719
UPDATE       1 at a time: 963.358307

My test scripts are attached. I am seeing the same kind of slowness when
it's deleting all versus in chunks of 1000, which is confusing me also.

Notice in my scripts that the way the table is created is perfectly
clustered.

Regards,
	Jeff Davis
-------------- next part --------------
require 'postgres'

db = PGconn.connect(nil,nil,nil,nil,'db02','jdavis',nil)

db.exec %q{
        drop table if exists delete_test;
        create table delete_test( a1 int8, a2 int8, a3 int8, a4 int8 );
        insert into delete_test select
                generate_series,
                generate_series+1,
                generate_series+2,
                generate_series+3
                from generate_series(1,5000000);
        create unique index delete_test_idx on delete_test(a1);
        analyze delete_test;
}

sleep 100

t1 = Time.now
db.exec "BEGIN"
i = 0
x = ARGV[0].to_i
while i < 5000000 do
        db.exec "DELETE FROM delete_test WHERE a1 > #{i} and a1 <= #{i+x}"
        i += x
end
db.exec "COMMIT"
t2 = Time.now

puts t2-t1

-------------- next part --------------
require 'postgres'

db = PGconn.connect(nil,nil,nil,nil,'db02','jdavis',nil)

db.exec %q{
        drop table if exists update_test;
        create table update_test( a1 int8, a2 int8, a3 int8, a4 int8 );
        insert into update_test select
                generate_series,
                generate_series+1,
                generate_series+2,
                generate_series+3
                from generate_series(1,5000000);
        create unique index update_test_idx on update_test(a1);
        analyze update_test;
}

sleep 100

t1 = Time.now
db.exec "BEGIN"
i = 0
x = ARGV[0].to_i
while i < 5000000 do
        db.exec "UPDATE update_test SET a2=-a2 WHERE a1 > #{i} and a1 <= #{i+x}"
        i += x
end
db.exec "COMMIT"
t2 = Time.now

puts t2-t1