My most humble apologies to the pg development team (pg_lets?).
I took Greg Stark's advice and set:
shared_buffers = 10000 # was 50000
work_mem = 1048576 # 1Gb - was 16384
Also, I noticed that the EXPLAIN ANALYZE consistently thought reads would
take longer than they actually did, so I decreased random_page_cost down to
1 (the server has a SATA Raid at level 10).
Queries that previously seemed to stall out are still a little slow but
nothing like before. And I'm seeing a more normal balance of CPU and disk
i/o while a query is running instead of the high-cpu-low-disk-read situation
I was seeing before. Concurrency is way up.
I tried a couple of interim sizes for work_mem and so far, the larger the
better (the server has 16Gb). I'll test a little larger size this evening
and see what it does. Yes, I've read the warning that this is per process.
Kudos to you Greg, thanks Luke for your comment (though it seems to disagree
with my experience). Also to Dennis, there were not drastic changes in the
plan between 8.0 and 8.1, it was just the actual execution times.
Martin
"PostgreSQL" <martin@portant.com> wrote in message
news:dkko49$1v06$1@news.hub.org...
> SELECT v_barcode, count(v_barcode) FROM lead GROUP BY v_barcode HAVING
> count(*) > 1;
>
> This is a pretty good example of the place where 8.1 seems to be quite
> broken.
...