Greg, Increasing memory actually slows down the current sort performance. We're working on a fix for this now in bizgres. Luke -------------------------- Sent from my BlackBerry Wireless Device -----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx <pgsql-performance-owner@xxxxxxxxxxxxxx> To: PostgreSQL <martin@xxxxxxxxxxx> CC: pgsql-performance@xxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxx> Sent: Sun Nov 06 14:24:00 2005 Subject: Re: [PERFORM] 8.1 iss "PostgreSQL" <martin@xxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster ---------------------------(end of broadcast)---------------------------TIP 6: explain analyze is your friend
