Re: indexing just a part of a string

Christoph Pingel wrote:
> So I would like to say 'index only the first 200 chars of the column',
> which will result in a full index of 99.9 % of my entries. I did this in
> MySQL, but I didn't find it in the pg manual.
>
> How do I proceed?

You could do:

CREATE INDEX <index name>
ON <table name> (SUBSTRING(<column name>, 1, 200))

But that may cause the index to be used only if you query for results
using SUBSTRING(). I don't know; You can test if it uses an index scan
using EXPLAIN.

You could also use separate indices for the short and the long string
variants, or maybe you could use a column that's better suited to the
task (for example, a column with an MD5 hash of the text or an integer
based on a sequence).
You could also try a different type of index, an ltree (contrib) for
example.

It all pretty much depends on what you're trying to do. In any case, you
should take a look at the documentation for CREATE INDEX, there are
possibilities.

Out of general curiosity: I mentioned using a hashed column as a
possible solution. Would that be equivalent to using a hash index? Or is
searching a hash value in a btree index actually faster than in a hash
index?

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban(at)magproductions(dot)nl
W: http://www.magproductions.nl