Re: limit over attribute size if index over it exists

[Martijn van Oosterhout <kleptog(at)svana(dot)org>]

On Mon, Jun 26, 2006 at 02:52:56AM -0700, pajai wrote:
> I have thought of a possible workaround. I would like to know if it
> seems reasonable. The idea would be to build a hash, on the client
> side, over the problematic column (let's say column a). I then store in
> the db the attribute a (without index) and the hash(a) (with an index).
> Then when I am doing a select, I use firstly a sub-select to choose all
> tuples with the right hash (quick, with index), and then an outer
> select to choose the tuple with the right attribute a (slow, sequential
> scan, but normally few tuples, because few collisions). Something like
> that:

Perhaps you should look into functional indexes. Indexes over a
function.

CREATE INDEX foo_index ON foo( hash(a) );

This index will automatically be used if you make a query like this:

... WHERE hash(a) = 'blah';

Hope this helps,
-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment: signature.asc
Description: Digital signature