From: Madison Kelly <linux@alteeve.com>
To: Mark Stosberg <mark@summersault.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Writting a "search engine" for a pgsql DB
Date: Mon, 26 Feb 2007 13:59:45 -0500
Message-ID: <45E32E21.9090901@alteeve.com> (raw)
In-Reply-To: <45E323AB.1040009@summersault.com>
References: <45E30ADA.2050700@alteeve.com>
<45E31300.4070701@commandprompt.com>
<erv5jn$2l1b$1@news.hub.org>
<45E31D28.2020402@alteeve.com>
<45E323AB.1040009@summersault.com>
Mark Stosberg wrote:
> 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
Now see, this is exactly the kind of sagely advice I was hoping for! :)
I'll look into tsearch2, and failing that for some reason, I love the
keyword table idea.
Thanks kindly!!
Madi
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: linux@alteeve.com, mark@summersault.com
Subject: Re: Writting a "search engine" for a pgsql DB
In-Reply-To: <45E32E21.9090901@alteeve.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