X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 0ABE09DC860 for ; Tue, 7 Mar 2006 13:08:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68875-08 for ; Tue, 7 Mar 2006 13:08:56 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 42DF09DC851 for ; Tue, 7 Mar 2006 13:08:54 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Tue, 7 Mar 2006 17:08:53 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 07 Mar 2006 11:08:53 -0600 Subject: Re: firebird X postgresql 8.1.2 windows, performance From: Scott Marlowe To: andremachado Cc: pgsql-performance@postgresql.org In-Reply-To: <20060307162842.M37264@techforce.com.br> References: <20060307162842.M37264@techforce.com.br> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1141751333.18820.172.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 07 Mar 2006 11:08:53 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.15 required=5 tests=[AWL=0.149, UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.15 X-Spam-Level: X-Archive-Number: 200603/62 X-Sequence-Number: 17499 On Tue, 2006-03-07 at 10:29, andremachado wrote: > Hello, > A friend asked for help to accelerate some postgresql queries on postgresql > 8.1.2 for windows. > He is comparing with firebird. > Firebird was being up to 90 times faster at some queries. > Attached is a gziped text file containing some steps I tried on a simple > example query. > Could get improvements from 270 seconds to 74 seconds. > But Firebird effortlessly still can perform the same query at 20 seconds. > Please, do you have some suggestion? First off, PostgreSQL on Windows is still kinda new, so it's quite possible that on some flavor of unix the disparity we're seeing wouldn't be so great. You may be seeing some issue with PostgreSQL's fairly new windows port instead of some basic postgresql problem. Is this running on the same basic hardware for both databases? I would imagine so, but just wanted to check. As someone else mentioned, try cranking up work mem, and to a lesser extent, shared_buffers. Also, as mentioned, why are bitmap scans and seq scans turned off? Bitmap scans are quite a nice improvement, and sometimes, a sequential scan is faster than an index. Forcing PostgreSQL to always use an index it not really a good idea. Lastly, I noticed that after you clusters on all your indexes, the query planner switched from a merge join to a hash join, and it was slower. You might wanna try turning off hash joins for a quick test to see if merge joins are any faster. Lastly, you might want to compare the two databases running on linux or BSD to see how they compare there.