Re: Real vs Int performance

Right you are. Kettle is turning the number(11) field from Oracle into a BigNumber, which is a decimal. If I cast the field into an Integer in Kettle and keep the field an integer in Postgres, I get good performance. Suspect the correct course of action would simply be to make number(11) fields in Oracle numeric(11,0) fields in Postgres.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, January 26, 2011 5:12 PM
To: David Greco
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Real vs Int performance

David Greco <David_Greco(at)harte-hanks(dot)com> 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