Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id C8ADB1337B9F for ; Thu, 14 Oct 2010 19:57:11 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 67788-08 for ; Thu, 14 Oct 2010 22:57:04 +0000 (UTC) X-Greylist: delayed 00:06:37.829154 by SQLgrey-1.7.6 Received: from 31.mail-out.ovh.net (31.mail-out.ovh.net [213.186.62.10]) by mail.postgresql.org (Postfix) with SMTP id 1BA001335B36 for ; Thu, 14 Oct 2010 19:57:03 -0300 (ADT) Received: (qmail 21602 invoked by uid 503); 14 Oct 2010 23:09:18 -0000 Received: from b6.ovh.net (HELO mail174.ha.ovh.net) (213.186.33.56) by 31.mail-out.ovh.net with SMTP; 14 Oct 2010 23:09:18 -0000 Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 15 Oct 2010 00:50:22 +0200 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13) (lists%peufeu.com@88.161.102.87) by ns0.ovh.net with SMTP; 15 Oct 2010 00:50:21 +0200 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: pgsql-performance@postgresql.org, "Tony Capobianco" Subject: Re: oracle to psql migration - slow query in postgres References: <1287085384.1684.52.camel@tony1.localdomain> Date: Fri, 15 Oct 2010 00:50:20 +0200 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit From: "Pierre C" Message-ID: In-Reply-To: <1287085384.1684.52.camel@tony1.localdomain> User-Agent: Opera Mail/10.62 (Linux) X-Ovh-Tracer-Id: 9884275284296795697 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|U 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/240 X-Sequence-Number: 40658 > emailok | numeric(2,0) | Note that NUMERIC is meant for - really large numbers with lots of digits - or controlled precision and rounding (ie, order total isn't 99.999999999999 $) Accordingly, NUMERIC is a lot slower in all operations, and uses a lot more space, than all the other numeric types. I see many columns in your table that are declared as NUMERIC but should be BOOLs, or SMALLINTs, or INTs, or BIGINTs. Perhaps Oracle handles these differently, I dunno.