Re: 335 times faster (!)

On Mon, 3 Feb 2003, Mikael Carneholm wrote:

> > ------- Ursprungligt meddelande -------
> >
> > Från: Mario Weilguni <mweilguni(at)sime(dot)com>
> > Datum: Mon, 3 Feb 2003 20:05:48 +0100
> >
> >try:
> >explain select * from enheter where enhetsid = '200178146';
> >or
> >explain select * from enheter where enhetsid = 200178146::bigint
> >
>
> explain select * from enheter where enhetsid = '200178146';
> QUERY PLAN ---------------------------------------------------------------------------
> Index Scan using pk_enheter on enheter (cost=0.00..4.05 rows=1 width=91)
> Index Cond: (enhetsid = 200178146::bigint)
> (2 rows)
>
> Strange...using:
> 200178146::bigint
> or
> '200178146'
> ..the query is lightning fast. Since the PK column is of integer type, I don't think it's logical to pass a string-type argument...or am I different than most people on this point? :)

No, I think a lot of us have been caught by this in the past.

> What about third party frameworks (such as Hibernate, eg) - I'm sure they will look at the column datatype and think: 'Oh, it's an integer...I'll pass an integer argument then', which will result in unnecessary poor performance.
>
> What's the cause of this behaviour? Why isn't psql (or the backend) converting integer type arguments into char/string types, if there's this much to gain?

Someone more familiar with the backend can answer this a lot better than I
could so I won't give a half assed comment.

>
> (Sorry for being quite a Pg newbie..have never used Pg for such "heavy" load before)

I feel like that most days.

--
Nigel J. Andrews