Thread: Matching indexes on int8 columns?

I'm running 7.3, and I recently observed something rather disconcerting,
using EXPLAIN ANALYZE.

If I have an index on an int8 column, and I run a query such as

    SELECT * FROM [tablename] WHERE [colname] = 12345

the index is NOT used, and a sequential scan of the 200K rows is done,
taking ~ 17500 msec.;  if I change the query to be:

    SELECT * FROM [tablename] WHERE [colname] = '12345'

or

    SELECT * FROM [tablename] WHERE [colname] = 12345::int8

then the index is used and a btree index scan is performed taking ~24 msec.

I found these workarounds in a Sept. 2001 thread, leading me to believe
that it's nothing new;  however, there is nothing in the docs. that I
could find (including the Momjian book), that has any reference to this
problem.

My conclusion (assumption?) is that index-to-WHERE matching relies on
some strict kind of type matching, without any attempt at type
conversion between int4 and int8;  but if a type conversion is forced
(e.g., by quoting the number), then the conversion is done before the
matching and all goes well.

So finally, my question.  Why is this behavior present?  Is it actually
   a feature that I don't know enough to appreciate?

Inquiring minds want to know...

Thanks,

        Andrew Biagioni

On Wed, Oct 29, 2003 at 02:08:03PM -0500, Andrew Biagioni wrote:
>
> So finally, my question.  Why is this behavior present?  Is it actually
>   a feature that I don't know enough to appreciate?

It's the side-effect of some other features in the system.  If you
search on -hackers archives you'll find several attempts to find  a
solution to this little problem; all of them have failed so far,
AFAIK.  There was talk of making the int4-to-int8 case behave more
reasonably in 7.4, but I don't know whether it's been done.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110