From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Greco <David_Greco@harte-hanks.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Real vs Int performance
Date: Wed, 26 Jan 2011 17:12:12 -0500
Message-ID: <26917.1296079932@sss.pgh.pa.us> (raw)
In-Reply-To: <CDFA9340E95A764E9366B4EDF3A43125BCACF7E68D@VA3DIAXVS091.RED001.local>
References: <CDFA9340E95A764E9366B4EDF3A43125BCACF7E68D@VA3DIAXVS091.RED001.local>
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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, David_Greco@harte-hanks.com
Subject: Re: Real vs Int performance
In-Reply-To: <26917.1296079932@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox