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@xxxxxxxxxxxxxxxxx W: http://www.magproductions.nl
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
