Juho Saarikko wrote:
I suggest implementing unique hash indexes and automatically creating one (and turning the b-tree index into a non-unique one) when a large value is inserted to fix this. Alternatively, fix b-trees so they can handle large values; however, a hash index should be far more efficient for this specific case, since the size of a hash is independent of pre-hash data size.
The current implementation of hash indexes actually store the whole key, in addition to the hash, so the size of the hash index is not independent of the key size. There has been some discussion on revamping the hash index implementation, and changing that among other things, but don't hold your breath.
As others have pointed out, CREATE UNIQUE INDEX i ON ((md5(column)) is a pretty good work-around.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
