From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | "PostgreSQL" <martin(at)portant(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: 8.1 iss |
Date: | 2005-11-06 19:24:00 |
Message-ID: | 87pspdfulr.fsf@stark.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
"PostgreSQL" <martin(at)portant(dot)com> writes:
...
> 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
...
It sounds to me like it's doing a large on-disk sort. Increasing work_mem
should improve the efficiency. If you increase it enough it might even be able
to do it in memory, but probably not.
The shared_buffers is excessive but if you're using the default 8kB block
sizes then it 400MB of shared pages on a 16GB machine ought not cause
problems. It might still be worth trying lowering this to 10,000 or so.
Is this a custom build from postgresql.org sources? RPM build? Or is it a BSD
ports or Gentoo build with unusual options?
Perhaps posting actual vmstat and iostat output might help if someone catches
something you didn't see?
--
greg
From | Date | Subject | |
---|---|---|---|
Next Message | Joost Kraaijeveld | 2005-11-06 19:33:53 | Re: Performance PG 8.0 on dual opteron / 4GB / 3ware |
Previous Message | Jeroen van Iddekinge | 2005-11-06 18:02:39 | Performance problem with pg8.0 |