Jeff Davis <pgsql at j-davis.com> writes:
> On Wed, 2007-07-04 at 10:20 +0200, Csaba Nagy wrote:
>> On Wed, 2007-07-04 at 00:53, Christopher Browne wrote:
>> > One at a time took about 15 minutes
>> > 100 at a time took 3:15
>> > 1000 at a time took longer than 100 at a time (curious, that!)
>> > all in one shot took 43 seconds.
>> 
>> Check the plans for the 100 vs. 1000 cases: I'm pretty sure 100 goes for
>> bitmap index scan and 1000 goes for sequential scan... and 10 * 100
>> bitmap index scans are probably somewhat faster than 1 sequential scan
>> on your table/box. I guess 1000 is close to the limit between the
>> performance turnover between the index scan and sequential scan on your
>> table/box/setup, but the sequential scan is slightly underestimated by
>> the planner.
>> 
>> BTW, the bitmap index scan case should theoretically be the fastest, so
>> aiming for the highest chunk size where the planner still chooses bitmap
>> index scan (or downright forcing it to do so if possible) would give the
>> best performance.
>> 
>
> Why would a bitmap index scan be faster than a sequential scan when
> deleting the entire table?

It wouldn't be...

The thing is, on the subscriber side, the deletes take place one tuple
at a time.  In THAT case, putting several into one DELETE request does
validly favour a bitmap index scan.

This may remain controversial for a while; it seems evident that there
is a gain to be had by doing several DELETEs together.  Unfortunately,
it is also evident that this will complicate the code.

>From your numbers, it looks like "maxxing out" at 100 in a statement
is likely optimal, *way* faster than doing a delete at a time, and not
materially worse than the "where-less" DELETE FROM FOO;

This is not the next thing to change, but I think it should be changed
someday...
-- 
output = reverse("ofni.sailifa.ac" "@" "enworbbc")
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)