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.

Cheers,
Csaba.