Postgres FTI and SQLObject

Thursday, October 13th, 2005

Postgres is a truly awesome database. When we started working on Launchpad I wasn’t sure if it would be up to the job. I was so wrong. It’s been robust, fast, and *professional* in every regard.

Stub has got full text search pretty smoothly integrated into Launchpad, using tsearch2. Today I setup the source package searching engine in the Soyuz part of Launchpad (see the page at http://launchpad.net/distros/ubuntu/+search) and used tsearch2. What I could not figure out, however, was how to rank the results by the quality of match.

We are using SQLObject. So the code ends up looking like this:

result = DistributionSourcePackageCache.select("fti @@ ftq(%s)" % sqlvalues(searchtext))

Now I have the resultset, but it does not seem to be in the optimally ranked order, and I can’t figure out how to get it that way. Does anyone know? Ah well, Stub will wake up shortly, will bother him then.

2 comments:

  1. Josh Berkus says: (permalink)
    June 7th, 2006 at 1:22 am

    Mark,

    What are you getting as a result set? Tsearch2 is highly configurable, but that means you need to use some of the ranking function parameters to get the result you want.

    E-mail me, or use the OpenFTS mailing list, which is pretty responsive.

    BTW, I’m quoting your blog post for the PostgreSQL home page. I hope that’s OK with you … if not, ping me and I’ll take it down right away.

  2. February 26, 2006 « Everything is Data says: (permalink)
    May 2nd, 2009 at 12:00 am

    [...] 26, 2006 By neilconway Mark Shuttleworth seems fairly pleased with Postgres. Money [...]