David Greco <David_Greco@harte-hanks.com> writes:
> Came across a problem I find perplexing. I recreated the dimensional tables in Oracle and the fields that are
integersin Oracle became integers
> in Postgres. Was experiencing terrible performance during the load and narrowed down to a particular dimensional
lookupproblem. The table
> dim_carrier holds about 80k rows. You can see the actual query issued by Kettle below, but basically I am looking up
usingthe 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
integerto 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