X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 08DB2D1B4BD for ; Fri, 9 Jan 2004 16:26:04 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 04103-09 for ; Fri, 9 Jan 2004 12:25:33 -0400 (AST) Received: from mail012.syd.optusnet.com.au (mail012.syd.optusnet.com.au [211.29.132.66]) by svr1.postgresql.org (Postfix) with ESMTP id 4C3ECD1DB76 for ; Fri, 9 Jan 2004 10:53:50 -0400 (AST) Received: from objectmastery.com (c211-28-197-254.eburwd1.vic.optusnet.com.au [211.28.197.254]) by mail012.syd.optusnet.com.au (8.11.6p2/8.11.6) with ESMTP id i09Erfr07771; Sat, 10 Jan 2004 01:53:42 +1100 Message-ID: <3FFEC075.2090001@objectmastery.com> Date: Sat, 10 Jan 2004 01:53:41 +1100 From: Bradley Tate User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030703 X-Accept-Language: en-us, en MIME-Version: 1.0 To: =?ISO-8859-1?Q?Dennis_Bj=F6rklund?= Cc: Richard Huxton , pgsql-performance@postgresql.org Subject: Re: Slow query problem References: In-Reply-To: X-Enigmail-Version: 0.76.7.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/99 X-Sequence-Number: 5274 Dennis Björklund wrote: >On Fri, 9 Jan 2004, Richard Huxton wrote: > > > >>>>select invheadref, invprodref, sum(units) >>>>from invtran >>>>group by invheadref, invprodref >>>> >>>> >>>For the above query, shouldn't you have one index for both columns >>>(invheadref, invprodref). Then it should not need to sort at all to do the >>>grouping and it should all be fast. >>> >>> >>Not sure if that would make a difference here, since the whole table is being >>read. >> >> > >The goal was to avoid the sorting which should not be needed with that >index (I hope). So I still think that it would help in this case. > > > Thanks for the advice. I tried creating a compound index along with clustering the invtran table on it, adding another 512MB RAM, increasing shared_buffers to 60000 and increasing sort_mem to 100MB, playing with effective cache size in postgresql.conf. This cut the execution time down to 4 minutes, which was helpful but still way behind firebird. There was still an awful lot of disk activity while it was happening which seems to imply lots of sorting going on (?) Invtran is a big table but it is clustered and static i.e. no updates, select statements only. Mostly my performance problems are with sorts - group by, order by. I was hoping for better results than I've been getting so far. Thanks. p.s. Can someone confirm whether this should work from pgadmin3? i.e. will the size of the sort_mem be changed for the duration of the query or session? set sort_mem to 100000; select ....etc....;