Forum OpenACS Q&A: Response to Searching a PostgeSQL database

Posted by Albert Langer on
Not sure if this helps, but can't do much harm.

Postgresql has a contributed function for doing substrings searches more efficiently than LIKE. I know someone who wrote an alternative to it in PGPLSQL or whatever the procedural language for "triggers" is called, using a faster algorithm and accepting substring queries in the same format as LDAP. Works by indexing each of the 10 rotated versions of a 10 letter word separately so that a search for '*SUB' or '*SUB*' will be just as fast as for 'SUB*'. Doesn't need to search the whole table, just looks up the (much larger) index. So searches are dramatically faster (but inserts slower and much more disk space, though the latter is pretty irrelevant these days).

Last I saw it was working but hadn't been packaged up for release or integrated as an internal Postgresql function. I'm sure he'll be happy to pass it on "as is". If anyone is interested, let me know.

Doesn't deal directly with full text searching, only relatively small text fields within database. But could be relevant if people are looking at different ways to do search related things.