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. I understand that this query will want to do a full table scan
(even through v_barcode is indexed). And the table is largish, at 34
million rows. In the 8.0 world, this took around 4 minutes. With 8.1beta3,
this has run for 30 minutes (as I began to write this) and is still going
strong.
And it behaves differently than I'd expect. Top shows the postmaster
process running the query as using up 99.9 percent of one CPU, while the i/o
wait time never gets above 3%. vmstat shows the "block out" (bo) number
quite high, 15 to 20 thousand, which also surprises me. "block in" is from
0 to about 2500. iostat shows 15,000 to 20,000 blocks written every 5
seconds, while it shows 0 blocks read. There is no other significant
process running on the box. (Apache is running but is not being used here a
3:00a.m. on Sunday). This is a dual Opteron box with 16 Gb memory and a
3ware SATA raid runing 64bit SUSE. Something seems badly wrong.
As I post this, the query is approaching an hour of run time. I've listed
an explain of the query and my non-default conf parameters below. Please
advise on anything I should change or try, or on any information I can
provide that could help diagnose this.
GroupAggregate (cost=9899282.83..10285434.26 rows=223858 width=15)
Filter: (count(*) > 1)
-> Sort (cost=9899282.83..9994841.31 rows=38223392 width=15)
Sort Key: v_barcode
-> Seq Scan on lead (cost=0.00..1950947.92 rows=38223392 width=15)
shared_buffers = 50000
work_mem = 16384
maintenance_work_mem = 16384
max_fsm_pages = 100000
max_fsm_relations = 5000
wal_buffers = 32
checkpoint_segments = 32
effective_cache_size = 50000
default_statistics_target = 50