Re: 335 times faster (!)

On Mon, 3 Feb 2003, Mikael Carneholm wrote:

> > ------- Ursprungligt meddelande -------
> >
> > Från:    Mario Weilguni <mweilguni@sime.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-typeargument...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/stringtypes, 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