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 6BCE4D1B45D for ; Thu, 8 Jan 2004 05:53:12 +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 78638-04 for ; Thu, 8 Jan 2004 01:52:24 -0400 (AST) Received: from mail01.objectmastery.net (mail01.objectmastery.net [203.34.143.230]) by svr1.postgresql.org (Postfix) with ESMTP id 2D838D1B44C for ; Thu, 8 Jan 2004 01:52:22 -0400 (AST) Received: from objectmastery.com (rev.objectmastery.net [203.34.143.18]) by mail01.objectmastery.net (Postfix) with ESMTP id 0951F3FA5 for ; Thu, 8 Jan 2004 16:52:06 +1100 (EST) Message-ID: <3FFCF005.70108@objectmastery.com> Date: Thu, 08 Jan 2004 16:52:05 +1100 From: Bradley Tate User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Slow query problem X-Enigmail-Version: 0.76.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/89 X-Sequence-Number: 5264 Hi, We've set up a little test box (1GHz Athlon, 40G IDE drive, 256M RAM, Redhat 9) to do some basic comparisons between postgresql and firebird 1.0.3 and 1.5rc8. Mostly the results are comparable, with one significant exception. QUERY select invheadref, invprodref, sum(units) from invtran group by invheadref, invprodref RESULTS pg 7.3.4 - 5.5 min pg 7.4.0 - 10 min fb 1.0.3 - 64 sec fb 1.5 - 44 sec * The invtran table has about 2.5 million records, invheadref and invprodref are both char(10) and indexed. * shared_buffers = 12000 and sort_mem = 8192 are the only changes I've made to postgresql.conf, with relevant changes to shmall and shmmax. This is an explain analyse plan from postgresql 7.4: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ GroupAggregate (cost=572484.23..601701.15 rows=1614140 width=39) (actual time=500091.171..554203.189 rows=147621 loops=1) -> Sort (cost=572484.23..578779.62 rows=2518157 width=39) (actual time=500090.939..527500.940 rows=2521530 loops=1) Sort Key: invheadref, invprodref -> Seq Scan on invtran (cost=0.00..112014.57 rows=2518157 width=39) (actual time=16.002..25516.917 rows=2521530 loops=1) Total runtime: 554826.827 ms (5 rows) Am I correct in interpreting that most time was spent doing the sorting? Explain confuses the heck out of me and any help on how I could make this run faster would be gratefully received. Cheers, Bradley.