From: PostgreSQL <martin@portant.com>
To: pgsql-performance@postgresql.org
Subject: 8.1 iss
Date: Sun, 6 Nov 2005 03:55:18 -0600
Message-ID: <dkko49$1v06$1@news.hub.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: martin@portant.com
Subject: Re: 8.1 iss
In-Reply-To: <dkko49$1v06$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox