335 times faster (!)

I discovered a strange thing when doing a simple search on a (comparably) large table with ~900K rows today:

When searching for a specific row on the primary key (type: bigint), the search took about 6,5 seconds. The column has a default btree index as created by the primary key constraint. However, when searching for the same row on one of it's columns (type: text) which has a functional index on lower(column name), the same row was retrieved in 19ms! That's ~335 times faster!

My idea is thus that one could create tables with a text type column holding the value of the identifier without using the 'primary key' clause, and then afterwards add a functional index on lower(column name). If performance is the main issue, this must be an interesting solution. The downside is of course that the text data type may result in invalid integer values being inserted as keys.

Anyone tried this before?

- Mikael

_____________________________________________________________
Här börjar internet!
Skaffa gratis e-mail och gratis internet på http://www.spray.se

Träffa folk från hela Sverige på ett och samma ställe - http://chat.spray.se/