Indexing int8 does not work, here is why and how! (must read)

[PriceComparison]

I am a programmer for PriceComparison.com.

And I have just solved something quite remarkable and hard to solve. For anyone who has any index using an int8 / bigint field.
You should take time to read this.

The problem start when we noticed that any query with that int8 field is always slow. Finally we did an 'explain' of the query.
The server never even cared of using the index. It always does a sequential scan. No index scan eventhough the index is clearly there. We tried to reindex, recreate it, vacuum analyze, etc.... nothing works.

Then we accidentaly made it work?!? Strange?
Here is our first query (the one that does not work).

select * from product where item_number=1234

this query will NEVER use the index.

then we tried this:

select * from product where item_number='1234'

Notice the difference is just the single quotes!!! Now it uses the index all the time!

Wow, and this only happens with int8 / bigint.

I hope this will help somebody.

Andrew_@_PriceComparison.com

http://www.PriceComparison.com

 

[ceco]

it is true, in fact it is written in the documentation

http://www.postgresql.org/docs/7.4/interactive/datatype.html#DATATYPE-INT

there is also another workaround to type cast the integer to int8

in version 8 it is corrected and nothing like this is necessary

 

[rycamor]

Yes, it is simply a matter of typecasting, and I'm glad it is solved in version 8.0. Another way to fix the queries for indexes on different int types is to explicitly cast the constant to the right type:

select * from product where item_number=1234::int8