X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AFC81D1B297 for ; Fri, 18 Jun 2004 09:19:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26821-02 for ; Fri, 18 Jun 2004 12:19:51 +0000 (GMT) Received: from frodo.hserus.net (frodo.hserus.net [204.74.68.40]) by svr1.postgresql.org (Postfix) with ESMTP id E4A42D1B273 for ; Fri, 18 Jun 2004 09:19:47 -0300 (ADT) Received: from concord.pspl.co.in ([202.54.11.72]:64651 helo=frodo.hserus.net) by frodo.hserus.net with asmtp (Cipher TLSv1:DHE-RSA-AES256-SHA:256) (Exim 4.34 #0) id 1BbIJl-000GRG-H4 by authid with plain; Fri, 18 Jun 2004 17:48:01 +0530 Message-ID: <40D2DD73.7070600@frodo.hserus.net> Date: Fri, 18 Jun 2004 17:47:55 +0530 From: Shridhar Daithankar User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Gary Cowell Cc: pgsql-performance@postgresql.org Subject: Re: Major differences between oracle and postgres performance References: <20040618113157.84084.qmail@web25104.mail.ukl.yahoo.com> In-Reply-To: <20040618113157.84084.qmail@web25104.mail.ukl.yahoo.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.7 tagged_above=0.0 required=5.0 tests=RCVD_IN_NJABL, RCVD_IN_NJABL_SPAM X-Spam-Level: X-Archive-Number: 200406/183 X-Sequence-Number: 7241 Gary Cowell wrote: > The explain output on postgres shows the same > execution with a scan on vers and a sort but the query > time is 78.6 seconds. > > The explain output from PostgreSQL is: > QUERY PLAN > --------------------------------------------------------------------------------- > Unique (cost=117865.77..120574.48 rows=142 > width=132) > -> Sort (cost=117865.77..119220.13 rows=541741 > width=132) > Sort Key: "version" > -> Seq Scan on vers (cost=0.00..21367.41 > rows=541741 width=132) > Filter: ("version" IS NOT NULL) > > I do have an index on the column in question but > neither oracle nor postgresql choose to use it (which > given that we're visiting all rows is perhaps not > surprising). Can you post explain analyze for the same query? It contains actual numbers alond side the chosen plan. > > I'm not as familiar with postgresql as I am with > Oracle but I think I've configured comparible > buffering and sort area sizes, certainly there isn't > much physical IO going on in either case. Well, for postgresql you should check out http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html HTH Shridhar