Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 9F3541337BD0 for ; Wed, 26 Jan 2011 18:12:26 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 86149-08 for ; Wed, 26 Jan 2011 22:12:19 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 17EFA1337B69 for ; Wed, 26 Jan 2011 18:12:13 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p0QMCCVW026918; Wed, 26 Jan 2011 17:12:12 -0500 (EST) To: David Greco cc: "pgsql-performance@postgresql.org" Subject: Re: Real vs Int performance In-reply-to: References: Comments: In-reply-to David Greco message dated "Wed, 26 Jan 2011 11:31:58 -0800" Date: Wed, 26 Jan 2011 17:12:12 -0500 Message-ID: <26917.1296079932@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201101/245 X-Sequence-Number: 42032 David Greco writes: > Came across a problem I find perplexing. I recreated the dimensional tables in Oracle and the fields that are integers in Oracle became integers > in Postgres. Was experiencing terrible performance during the load and narrowed down to a particular dimensional lookup problem. The table > dim_carrier holds about 80k rows. You can see the actual query issued by Kettle below, but basically I am looking up using the business key from > our OLTP system. This field is carrier_source_id and is indexed as you can see below. If I change this field from an integer to a real, I get > about a 70x increase in performance of the query. That's really, really hard to believe, given that all else is equal --- so I'm betting it isn't. I suspect that what is really happening is that you're passing non-integral comparison constants in your queries. For example, if carrier_id is an integer, then SELECT ... WHERE carrier_id = 42 is indexable, but this isn't: SELECT ... WHERE carrier_id = 42.0 The latter case however *would* be indexable if carrier_id were float. The examples you show fail to show any performance difference at all, but that's probably because you used quoted literals ('42' not 42), which prevents the parser from deciding that a cross-type comparison is demanded. I believe Oracle handles such things differently, so running into this type of issue during an Oracle port isn't too surprising. > In real life, this query is actually bound and parameterized, In that case, an EXPLAIN using literal constants is next door to useless in terms of telling you what will happen in real life. You need to pay attention to exactly how the parameterization is done. Again, I'm suspecting a wrong datatype indication. regards, tom lane