Do we have problems with int8 indexes? Seems select on an int8 does
not use an index.

This is PostgreSQL 6.5.2 on RedHat 6.0.
--
Tatsuo Ishii

Fri, 24 Sep 1999 15:12:17 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
int8 and index
> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tatsuo Ishii
> Sent: Friday, September 24, 1999 3:12 PM
> To: pgsql-hackers@postgreSQL.org
> Subject: [HACKERS] int8 and index
> 
> 
> Do we have problems with int8 indexes? Seems select on an int8 does
> not use an index.
>

How about   select .. from .. where .. = ..::int8;  ?

Without ::int8 PostgreSQL doesn't use int8 indexes.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp  

Fri, 24 Sep 1999 15:32:02 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] int8 and index
>> Do we have problems with int8 indexes? Seems select on an int8 does
>> not use an index.
>>
>
>How about   select .. from .. where .. = ..::int8;  ?
>
>Without ::int8 PostgreSQL doesn't use int8 indexes.

Oops. I forgot about that! Thanks.
--
Tatsuo Ishii

Fri, 24 Sep 1999 15:36:37 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [HACKERS] int8 and index 
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> How about   select .. from .. where .. = ..::int8;  ?
>> 
>> Without ::int8 PostgreSQL doesn't use int8 indexes.

> Oops. I forgot about that! Thanks.

Yes, this is on the TODO list (although I think TODO just mentions
the equivalent problem for int2).

			regards, tom lane

Fri, 24 Sep 1999 11:08:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] int8 and index 
> Do we have problems with int8 indexes? Seems select on an int8 does
> not use an index.
> 
> This is PostgreSQL 6.5.2 on RedHat 6.0.

That is strange.  We have code to make indexes on int8 now.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 26 Sep 1999 20:05:31 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] int8 and index
> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> >> How about   select .. from .. where .. = ..::int8;  ?
> >> 
> >> Without ::int8 PostgreSQL doesn't use int8 indexes.
> 
> > Oops. I forgot about that! Thanks.
> 
> Yes, this is on the TODO list (although I think TODO just mentions
> the equivalent problem for int2).
> 

int8 mention added.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 26 Sep 1999 20:47:04 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] int8 and index
> -----Original Message-----
> From: Bruce Momjian [mailto:maillist@candle.pha.pa.us]
> Sent: Monday, September 27, 1999 9:47 AM
> To: Tom Lane
> Cc: t-ishii@sra.co.jp; Hiroshi Inoue; pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] int8 and index
> 
> 
> > Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > >> How about   select .. from .. where .. = ..::int8;  ?
> > >> 
> > >> Without ::int8 PostgreSQL doesn't use int8 indexes.
> > 
> > > Oops. I forgot about that! Thanks.
> > 
> > Yes, this is on the TODO list (although I think TODO just mentions
> > the equivalent problem for int2).
> > 
> 
> int8 mention added.
>

There may be a little difference.

int4 -> int8 never fails.
But int4 -> int2 fails if abs(int4) > 32768.

select .. from .. where int2_column = 32769;

	should return 0 rows or cause an elog(ERROR) ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Mon, 27 Sep 1999 10:08:55 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] int8 and index
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> int4 -> int8 never fails.
> But int4 -> int2 fails if abs(int4) > 32768.

> select .. from .. where int2_column = 32769;

> 	should return 0 rows or cause an elog(ERROR) ?

Should return 0 rows, clearly.  (That's what happens now, and I can
see no justification for doing otherwise.)  When we add code to try to
coerce the constant to match the type of the column, we will have to
watch out for overflow and not do the coercion if so.

What would be really way cool would be if the constant simplifier could
recognize that this condition is a constant FALSE, but that would
probably mean building in more knowledge about the semantics of
specific operators than is justified...

			regards, tom lane

Mon, 27 Sep 1999 09:29:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] int8 and index 