Re: 335 times faster (!)

On Mon, 3 Feb 2003, Mikael Carneholm wrote:

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

Could it be that you've got a reasonably powerful machine and that your table
isn't very wide?

Are you sure your search using the primary key was actually using the primary
key index, i.e. did you just do:

SELECT * FROM mytable WHERE pkcol = 45

or did you quote the number or cast to bigint? Perhaps this has changed in 7.3
I don't know.

Also, did you perhaps do your search on the text type column just after doing
the first SELECT? You might find there's some caching issue.

Not sure about anyone else but I think we'd want to see the plans used for your
queries, in addition to the queries, before accepting this.

--
Nigel J. Andrews