From: "Andrew J. Kopciuch" <akopciuch@bddf.ca>
Reply-To: akopciuch@bddf.ca
Organization: BDDF Consulting
To: postgres@ied.com, pgsql-sql@postgresql.org
Subject: Re: switching default integer datatype to int8 and "IN (...)" clause
Date: Thu, 9 Jan 2003 03:30:41 -0700
User-Agent: KMail/1.5
References: <no.Yo.N.nN.0301090355170.2533-100000@business.com>
In-Reply-To: <no.Yo.N.nN.0301090355170.2533-100000@business.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200301090330.41238.akopciuch@bddf.ca>

>  but a select like this takes ages (looooong time):
> # select * from file where id = 1921773;
>    id   | name
>    -----+----------------
> 1921777 |  icons
>

I believe the reason is this : the numeric literal is first considered an int4 
becuase it falls within the range of int4 (-2147483648 to +2147483647).

try quoting the literal like this:

 # select * from file where id = '1921773';

This forces the literal to be evaluated.  If you do an explain on that query 
... you should see that the query planner uses the index as expected and that 
the condition used on the index is using the literal value cast to a big int.


That's just my understanding anyway.


Andy