To: "PostgreSQL" <martin@portant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 8.1 iss
References: <dkko49$1v06$1@news.hub.org>
In-Reply-To: <dkko49$1v06$1@news.hub.org>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 06 Nov 2005 14:24:00 -0500
Message-ID: <87pspdfulr.fsf@stark.xeocode.com>
Lines: 36
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


"PostgreSQL" <martin@portant.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