sqlobject-discuss

[SQLObject] Using fulltext indexes on Postgres

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

Hallo,

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

I considered two alternatives. One is the fti module. fti requires to
use queries like the following which access a second table carrying
the fulltext index(es): 

 select a.id from normal_fti f1, normal a where f1.string ~ '^clone'
 and a.oid = f1.id ;

"normal a" here is the table managed by SQLObject, "normal_fti" has
the fulltext index and looks like this: 
 
 CREATE TABLE normal_fti (
  string varchar(200),
  id oid
 );

Another approach is the GiST-based tsearch module (I cannot use
tsearch2 because I'm stuck with PSQL 7.1 for now) from:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/

tsearch has a nicer query layout, but needs a new column inside the
SQLObject table of type "txtidx". 

Given an index column called "titleidx" this then gives queries like
this: 

 select * from normal where titleidx @@ 'something';

This looks easier to incorporate into SQLObjects .select syntax to me,
but will it disturb my SQLObject classes? I am aware, that I might not
be able to use automatic table generation.

Now my question is: Say, that both are fast enough for me, what kind
of index would you use in a SQLObject application? And I'd love to
hear, if someone has any experiences with one of these or even some
example code, as I'm not sure yet, how to implement the queries? 

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

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__