Re: Real vs Int performance - Mailing list pgsql-performance
From | David Greco |
---|---|
Subject | Re: Real vs Int performance |
Date | |
Msg-id | CDFA9340E95A764E9366B4EDF3A43125BCACF7E9EC@VA3DIAXVS091.RED001.local Whole thread Raw |
In response to | Re: Real vs Int performance (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Real vs Int performance
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-performance |
Right you are. Kettle is turning the number(11) field from Oracle into a BigNumber, which is a decimal. If I cast the fieldinto an Integer in Kettle and keep the field an integer in Postgres, I get good performance. Suspect the correct courseof action would simply be to make number(11) fields in Oracle numeric(11,0) fields in Postgres. -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, January 26, 2011 5:12 PM To: David Greco Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Real vs Int performance 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
pgsql-performance by date: