Re: [SQLObject] Using fulltext indexes on Postgres

[Frank B. <fb...@fo...>]

Hallo,
Frank Barknecht hat gesagt: // Frank Barknecht wrote:

> I'd like to use a full text index on some of my SQLObject tables in
> PostgreSQL to speed things up a bit.

Okay, I decided to use tsearch now and implemented it. Maybe my
approach is interesting for others as well, so here's how I did it: 

I added the indexes and stuff as described in the tsearch docs. Then
it was a bit tricky to get the select right, because I use a complex
autogeneration of queries using SQLBuilder.

So I created a new SQLOp like this: 

from SQLObject import *
from SQLObject.SQLBuilder import SQLOp
def TSEARCH(expr, string):
	return SQLOp("##", expr, string)

Now this can be used with the "const" or "func" keyword: 

# colName is the name of an SQLObject Col(), op is the search word,
# and artist_tsearch, title_tsearch are the indexed tsearch columns.
# They get used instead of the SQLObject Col()s in queries:

if colName == "artist":
	subquery = TSEARCH(const.artist_tsearch, op)
	# this creates a query like: where artist_tsearch ## 'beatles' 
if colName == "title":
	subquery = TSEARCH(const.title_tsearch, op)
else: 
	# search in field without full text index:
	subquery = LIKE(someOtherColum, op)

This actually works rather well, but made my head spin last night.
Also the indexes make the db really huge. The data, which is imported
from a 16 MB csv file results in 561 MB data in the db directory of
PostgreSQL. I stripped all stop-words from the tsearch dictionary,
though, because I need to find artists like "the who", too. 

Searching is fast now.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__