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
adefault 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
timesfaster!
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
integervalues 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/