Postgres' pattern matching functions look very powerful, but I am concerned about performance issues here, and it turns out I'm not the only one facing this problem, of course.
Duck suggests using a second index table (id, keyword) with a row for each useful keyword that links back to the main table, and momjian came across an implementation using triggers, although that was 7 years ago now.
Another solution is called TSearch2. This implementation seems to be heavily based on Duck's suggestions and it's bundled into Postgres' official distribution as of version 7.4. It has to be manually installed from sources (from contrib/tsearch2 make && make install, then dump tsearch2.sql into your database). There's a good review on tsearch2 here.
On Debian:
- apt-get install postgresql-contrib-7.4
- psql mydb < /usr/share/postgres/7.4/contrib/tsearch2.sql (as postgres)
- 4 new tables should be created. Set proper permissions on them
- try: SELECT set_curcfg('default') ;
- then, try: SELECT to_tsvector('I live in Sydney, Australia.')
Read this if you intend on using TSearch2 on Postgres up to 7.4.8, as the original version comes with a highly dangerous security flaw that needs to be manually patched.
No comments:
Post a Comment