Обсуждение: 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.
--
Tatsuo Ishii

> -----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  

>> 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

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

> 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,
Pennsylvania19026
 

> 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,
Pennsylvania19026
 

> -----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

"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