Re: 335 times faster (!) - Mailing list pgsql-general

From Nigel J. Andrews
Subject Re: 335 times faster (!)
Date
Msg-id Pine.LNX.4.21.0302031958050.20150-100000@ponder.fairway2k.co.uk
Whole thread Raw
In response to Re: 335 times faster (!)  (Mikael Carneholm <carniz@spray.se>)
List pgsql-general
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


pgsql-general by date:

Previous
From: "Nigel J. Andrews"
Date:
Subject: Re: DBI driver and transactions
Next
From: Mikael Carneholm
Date:
Subject: Re: What is the benefit of schemas?