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: (view raw, whole thread or download thread mbox)
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! :)


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.


In response to


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-2017 The PostgreSQL Global Development Group