From: Mark Stosberg <mark@summersault.com>
To: Madison Kelly <linux@alteeve.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Writting a "search engine" for a pgsql DB
Date: Mon, 26 Feb 2007 13:15:07 -0500
Message-ID: <45E323AB.1040009@summersault.com> (raw)
In-Reply-To: <45E31D28.2020402@alteeve.com>
References: <45E30ADA.2050700@alteeve.com>
<45E31300.4070701@commandprompt.com>
<erv5jn$2l1b$1@news.hub.org>
<45E31D28.2020402@alteeve.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark@summersault.com, linux@alteeve.com
Subject: Re: Writting a "search engine" for a pgsql DB
In-Reply-To: <45E323AB.1040009@summersault.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox