Skip site navigation (1) Skip section navigation (2)

Re: Writting a "search engine" for a pgsql DB

From: Mark Stosberg <mark(at)summersault(dot)com>
To: Madison Kelly <linux(at)alteeve(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Writting a "search engine" for a pgsql DB
Date: 2007-02-26 18:15:07
Message-ID: 45E323AB.1040009@summersault.com (view raw or flat)
Thread:
Lists: pgsql-performance
Madison Kelly wrote:
>
>   I think the more direct question I was trying to get at is "How do you
> build a 'relavence' search engine? One where results are returned/sorted
> by relevance of some sort?". At this point, the best I can think of,
> would be to perform multiple queries; first matching the whole search
> term, then the search term starting a row, then ending a row, then
> anywhere in a row and "scoring" the results based on which query they
> came out on. This seems terribly cumbersome (and probably slow, indexes
> be damned) though. I'm hoping there is a better way! :)

Madison,

I think your basic thinking is correct. However, the first "select" can
done "offline" -- sometime beforehand.

For example, you might create a table called "keywords" that includes
the list of words mined in the other tables, along with references to
where the words are found, and how many times they are mentioned.

Then, when someone actually searches, the search is primarily on the
"keywords" table, which is now way to sort by "rank", since the table
contains how many times each keyword matches. The final result can be
constructed by using the details in the keywords table to pull up the
actual records needed.

My expectation however is that there are enough details in the system,
that I would first look at trying a package like tsearch2 to help solve
the problem, before trying to write another system like this from scratch.

  Mark


In response to

Responses

pgsql-performance by date

Next:From: Madison KellyDate: 2007-02-26 18:59:45
Subject: Re: Writting a "search engine" for a pgsql DB
Previous:From: Magnus HaganderDate: 2007-02-26 17:55:18
Subject: Re: Writting a "search engine" for a pgsql DB

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group