pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWritting a "search engine" for a pgsql DB
21+ messages / 10 participants
[nested] [flat]
* Writting a "search engine" for a pgsql DB
@ 2007-02-26 16:29 Madison Kelly <linux@alteeve.com>
0 siblings, 4 replies; 21+ messages in thread
From: Madison Kelly @ 2007-02-26 16:29 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I am asking in this list because, at the end of the day, this is a
performance question.
I am looking at writing a search engine of sorts for my database. I
have only ever written very simple search engines before which amounted
to not much more that the query string being used with ILIKE on a pile
of columns. This was pretty rudimentary and didn't offer anything like
relevance sorting and such (I'd sort by result name, age or whatnot).
So I am hoping some of you guys and gals might be able to point me
towards some resources or offer some tips or gotcha's before I get
started on this. I'd really like to come up with a more intelligent
search engine that doesn't take two minutes to return results. :) I
know, in the end good indexes and underlying hardware will be important,
but a sane as possible query structure helps to start with.
Thanks all!!
Madison
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-26 17:04 Joshua D. Drake <jd@commandprompt.com>
parent: Madison Kelly <linux@alteeve.com>
3 siblings, 1 reply; 21+ messages in thread
From: Joshua D. Drake @ 2007-02-26 17:04 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
Madison Kelly wrote:
> Hi all,
>
> I am asking in this list because, at the end of the day, this is a
> performance question.
>
> I am looking at writing a search engine of sorts for my database. I
> have only ever written very simple search engines before which amounted
> to not much more that the query string being used with ILIKE on a pile
> of columns. This was pretty rudimentary and didn't offer anything like
> relevance sorting and such (I'd sort by result name, age or whatnot).
>
> So I am hoping some of you guys and gals might be able to point me
> towards some resources or offer some tips or gotcha's before I get
> started on this. I'd really like to come up with a more intelligent
> search engine that doesn't take two minutes to return results. :) I
> know, in the end good indexes and underlying hardware will be important,
> but a sane as possible query structure helps to start with.
See search.postgresql.org, you can download all source from
gborg.postgresql.org.
Joshua D. Drake
>
> Thanks all!!
>
> Madison
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-26 17:32 Mark Stosberg <mark@summersault.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 2 replies; 21+ messages in thread
From: Mark Stosberg @ 2007-02-26 17:32 UTC (permalink / raw)
To: pgsql-performance
Joshua D. Drake wrote:
> Madison Kelly wrote:
>> Hi all,
>>
>> I am asking in this list because, at the end of the day, this is a
>> performance question.
>>
>> I am looking at writing a search engine of sorts for my database. I
>> have only ever written very simple search engines before which amounted
>> to not much more that the query string being used with ILIKE on a pile
>> of columns. This was pretty rudimentary and didn't offer anything like
>> relevance sorting and such (I'd sort by result name, age or whatnot).
>>
>> So I am hoping some of you guys and gals might be able to point me
>> towards some resources or offer some tips or gotcha's before I get
>> started on this. I'd really like to come up with a more intelligent
>> search engine that doesn't take two minutes to return results. :) I
>> know, in the end good indexes and underlying hardware will be important,
>> but a sane as possible query structure helps to start with.
>
> See search.postgresql.org, you can download all source from
> gborg.postgresql.org.
Joshua,
What's the name of the project referred to? There's nothing named
"search" hosted on Gborg according to this project list:
http://gborg.postgresql.org/project/projdisplaylist.php
Madison,
For small data sets and simpler searches, the approach you have been
using can be appropriate. You may just want to use a small tool in a
regular programming language to help build the query. I wrote such a
tool for Perl:
http://search.cpan.org/~markstos/SQL-KeywordSearch-1.11/lib/SQL/KeywordSearch.pm
For large or complex searches, a more specialized search system may be
appropriate. I suspect that's kind of tool that Joshua is referencing.
Mark
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-26 17:41 Joshua D. Drake <jd@commandprompt.com>
parent: Mark Stosberg <mark@summersault.com>
1 sibling, 0 replies; 21+ messages in thread
From: Joshua D. Drake @ 2007-02-26 17:41 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
>>> So I am hoping some of you guys and gals might be able to point me
>>> towards some resources or offer some tips or gotcha's before I get
>>> started on this. I'd really like to come up with a more intelligent
>>> search engine that doesn't take two minutes to return results. :) I
>>> know, in the end good indexes and underlying hardware will be important,
>>> but a sane as possible query structure helps to start with.
>> See search.postgresql.org, you can download all source from
>> gborg.postgresql.org.
>
> Joshua,
>
> What's the name of the project referred to? There's nothing named
> "search" hosted on Gborg according to this project list:
>
> http://gborg.postgresql.org/project/projdisplaylist.php
http://gborg.postgresql.org/project/pgweb/projdisplay.php
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-26 17:47 Madison Kelly <linux@alteeve.com>
parent: Mark Stosberg <mark@summersault.com>
1 sibling, 2 replies; 21+ messages in thread
From: Madison Kelly @ 2007-02-26 17:47 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
Mark Stosberg wrote:
> Joshua D. Drake wrote:
>> Madison Kelly wrote:
>>> Hi all,
>>>
>>> I am asking in this list because, at the end of the day, this is a
>>> performance question.
>>>
>>> I am looking at writing a search engine of sorts for my database. I
>>> have only ever written very simple search engines before which amounted
>>> to not much more that the query string being used with ILIKE on a pile
>>> of columns. This was pretty rudimentary and didn't offer anything like
>>> relevance sorting and such (I'd sort by result name, age or whatnot).
>>>
>>> So I am hoping some of you guys and gals might be able to point me
>>> towards some resources or offer some tips or gotcha's before I get
>>> started on this. I'd really like to come up with a more intelligent
>>> search engine that doesn't take two minutes to return results. :) I
>>> know, in the end good indexes and underlying hardware will be important,
>>> but a sane as possible query structure helps to start with.
>> See search.postgresql.org, you can download all source from
>> gborg.postgresql.org.
>
> Joshua,
>
> What's the name of the project referred to? There's nothing named
> "search" hosted on Gborg according to this project list:
>
> http://gborg.postgresql.org/project/projdisplaylist.php
>
> Madison,
>
> For small data sets and simpler searches, the approach you have been
> using can be appropriate. You may just want to use a small tool in a
> regular programming language to help build the query. I wrote such a
> tool for Perl:
>
> http://search.cpan.org/~markstos/SQL-KeywordSearch-1.11/lib/SQL/KeywordSearch.pm
>
> For large or complex searches, a more specialized search system may be
> appropriate. I suspect that's kind of tool that Joshua is referencing.
>
> Mark
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
Thanks Joshua and Mark!
Joshua, I've been digging around the CVS (web) looking for the search
engine code but so far have only found the reference (www.search) in
'general.php' but can't locate the file. You wouldn't happen to have a
direct link would you?
Mark, Thanks for a link to your module. I'll take a look at it's
source and see how you work your magic. :)
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! :)
Madi
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-26 17:55 Magnus Hagander <magnus@hagander.net>
parent: Madison Kelly <linux@alteeve.com>
1 sibling, 0 replies; 21+ messages in thread
From: Magnus Hagander @ 2007-02-26 17:55 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: Mark Stosberg <mark@summersault.com>; pgsql-performance
> Joshua, I've been digging around the CVS (web) looking for the search
> engine code but so far have only found the reference (www.search) in
> 'general.php' but can't locate the file. You wouldn't happen to have a
> direct link would you?
It's all in module "portal". You will find the indexing stuff in
tools/search, and the search interface in system/page/search.php.
> 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! :)
The tsearch2 ranking features are pretty good.
//Magnus
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-26 18:15 Mark Stosberg <mark@summersault.com>
parent: Madison Kelly <linux@alteeve.com>
1 sibling, 1 reply; 21+ messages in thread
From: Mark Stosberg @ 2007-02-26 18:15 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-26 18:59 Madison Kelly <linux@alteeve.com>
parent: Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 21+ messages in thread
From: Madison Kelly @ 2007-02-26 18:59 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-26 19:46 Mark Stosberg <mark@summersault.com>
parent: Madison Kelly <linux@alteeve.com>
0 siblings, 0 replies; 21+ messages in thread
From: Mark Stosberg @ 2007-02-26 19:46 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
>
> 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.
For example keyword search code, you can try this package:
http://downloads.sourceforge.net/cascade/cascade-devel-pieces-1.1.tgz?modtime=999556617&big_mirr...
There is a "keywords" subdirectory with the Perl and SQL. I'm sure this
code is not ideal in a number of ways:
1. It's from 2001.
2. It doesn't actually function on it's own anymore. However, you can
read the code and get ideas.
3. I'm sure someone has a better looking/functioning example!
Anyway, it's there if you want to take a look.
Mark
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-26 20:14 Jeff Davis <pgsql@j-davis.com>
parent: Madison Kelly <linux@alteeve.com>
3 siblings, 0 replies; 21+ messages in thread
From: Jeff Davis @ 2007-02-26 20:14 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
On Mon, 2007-02-26 at 11:29 -0500, Madison Kelly wrote:
> I am looking at writing a search engine of sorts for my database. I
> have only ever written very simple search engines before which amounted
> to not much more that the query string being used with ILIKE on a pile
> of columns. This was pretty rudimentary and didn't offer anything like
> relevance sorting and such (I'd sort by result name, age or whatnot).
Look at Tsearch2:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
It has a lot of features for searching, and can make use of powerful
indexes to return search results very quickly. As someone already
mentioned, it also has ranking features.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-26 21:24 Charles Sprickman <spork@bway.net>
parent: Madison Kelly <linux@alteeve.com>
3 siblings, 1 reply; 21+ messages in thread
From: Charles Sprickman @ 2007-02-26 21:24 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
On Mon, 26 Feb 2007, Madison Kelly wrote:
> Hi all,
>
> I'd really like to come up with a more intelligent search engine that doesn't
> take two minutes to return results. :) I know, in the end good indexes and
> underlying hardware will be important, but a sane as possible query structure
> helps to start with.
I'm not a programmer, so I can't comment on how good of an example this
is, but I've been pretty happy with mnogosearch:
http://www.mnogosearch.com/
The *nix versions are free. Looking at the db structure gave me a bit of
an idea of what I'm guessing is the "right way" to search a huge amount of
documents.
Charles
> Thanks all!!
>
> Madison
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-27 13:15 Magnus Hagander <magnus@hagander.net>
parent: Charles Sprickman <spork@bway.net>
0 siblings, 1 reply; 21+ messages in thread
From: Magnus Hagander @ 2007-02-27 13:15 UTC (permalink / raw)
To: Charles Sprickman <spork@bway.net>; +Cc: Madison Kelly <linux@alteeve.com>; pgsql-performance
On Mon, Feb 26, 2007 at 04:24:12PM -0500, Charles Sprickman wrote:
> On Mon, 26 Feb 2007, Madison Kelly wrote:
>
> >Hi all,
> >
> >I'd really like to come up with a more intelligent search engine that
> >doesn't take two minutes to return results. :) I know, in the end good
> >indexes and underlying hardware will be important, but a sane as possible
> >query structure helps to start with.
>
> I'm not a programmer, so I can't comment on how good of an example this
> is, but I've been pretty happy with mnogosearch:
>
> http://www.mnogosearch.com/
>
> The *nix versions are free. Looking at the db structure gave me a bit of
> an idea of what I'm guessing is the "right way" to search a huge amount of
> documents.
Just as a datapoint, we did try to use mnogosearch for the
postgresql.org website+archives search, and it fell over completely.
Indexing took way too long, and we had search times several thousand
times longer than with tsearch2.
That said, I'm sure there are cases when it works fine :-)
//Magnus
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-27 13:33 Dave Page <dpage@postgresql.org>
parent: Magnus Hagander <magnus@hagander.net>
0 siblings, 2 replies; 21+ messages in thread
From: Dave Page @ 2007-02-27 13:33 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Charles Sprickman <spork@bway.net>; Madison Kelly <linux@alteeve.com>; pgsql-performance
Magnus Hagander wrote:
>
> Just as a datapoint, we did try to use mnogosearch for the
> postgresql.org website+archives search, and it fell over completely.
> Indexing took way too long, and we had search times several thousand
> times longer than with tsearch2.
>
> That said, I'm sure there are cases when it works fine :-)
There are - in fact before your time the site did use Mnogosearch. We
moved to our own port of ASPSeek when we outgrew Mnogo's capabilities,
and then to your TSearch code when we outgrew ASPSeek.
When we outgrow PostgreSQL & Tsearch2, then, well, we'll need to stop
pretending to be Google...
/D
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-27 23:05 Chris <dmagick@gmail.com>
parent: Madison Kelly <linux@alteeve.com>
3 siblings, 0 replies; 21+ messages in thread
From: Chris @ 2007-02-27 23:05 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
Madison Kelly wrote:
> Hi all,
>
> I am asking in this list because, at the end of the day, this is a
> performance question.
>
> I am looking at writing a search engine of sorts for my database. I
> have only ever written very simple search engines before which amounted
> to not much more that the query string being used with ILIKE on a pile
> of columns. This was pretty rudimentary and didn't offer anything like
> relevance sorting and such (I'd sort by result name, age or whatnot).
>
> So I am hoping some of you guys and gals might be able to point me
> towards some resources or offer some tips or gotcha's before I get
> started on this. I'd really like to come up with a more intelligent
> search engine that doesn't take two minutes to return results. :) I
> know, in the end good indexes and underlying hardware will be important,
> but a sane as possible query structure helps to start with.
As someone mentioned, tsearch2 is a good option.
<plug> I wrote a small article about how to get it set up relatively
easily: http://www.designmagick.com/article/27/ </plug>
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-27 23:36 Charles Sprickman <spork@bway.net>
parent: Dave Page <dpage@postgresql.org>
1 sibling, 2 replies; 21+ messages in thread
From: Charles Sprickman @ 2007-02-27 23:36 UTC (permalink / raw)
To: Dave Page <dpage@postgresql.org>; +Cc: Magnus Hagander <magnus@hagander.net>; Madison Kelly <linux@alteeve.com>; pgsql-performance
On Tue, 27 Feb 2007, Dave Page wrote:
> Magnus Hagander wrote:
>>
>> Just as a datapoint, we did try to use mnogosearch for the
>> postgresql.org website+archives search, and it fell over completely.
>> Indexing took way too long, and we had search times several thousand
>> times longer than with tsearch2.
>>
>> That said, I'm sure there are cases when it works fine :-)
>
> There are - in fact before your time the site did use Mnogosearch. We
> moved to our own port of ASPSeek when we outgrew Mnogo's capabilities,
> and then to your TSearch code when we outgrew ASPSeek.
At risk of pulling this way too far off topic, may I ask how many
documents (mail messages) you were dealing with when things started to
fall apart with mnogo? We're looking at it for a new project that will
hopefully get bigger and bigger. We will be throwing groups of mailing
lists into their own mnogo config/tables... If we should save ourselves
the pain and look at something more homebrew, then we'll start
investigating "Tsearch".
Thanks,
Charles
> When we outgrow PostgreSQL & Tsearch2, then, well, we'll need to stop
> pretending to be Google...
>
> /D
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-28 07:44 Magnus Hagander <magnus@hagander.net>
parent: Charles Sprickman <spork@bway.net>
1 sibling, 0 replies; 21+ messages in thread
From: Magnus Hagander @ 2007-02-28 07:44 UTC (permalink / raw)
To: Charles Sprickman <spork@bway.net>; +Cc: Dave Page <dpage@postgresql.org>; Madison Kelly <linux@alteeve.com>; pgsql-performance
On Tue, Feb 27, 2007 at 06:36:11PM -0500, Charles Sprickman wrote:
> On Tue, 27 Feb 2007, Dave Page wrote:
>
> >Magnus Hagander wrote:
> >>
> >>Just as a datapoint, we did try to use mnogosearch for the
> >>postgresql.org website+archives search, and it fell over completely.
> >>Indexing took way too long, and we had search times several thousand
> >>times longer than with tsearch2.
> >>
> >>That said, I'm sure there are cases when it works fine :-)
> >
> >There are - in fact before your time the site did use Mnogosearch. We
> >moved to our own port of ASPSeek when we outgrew Mnogo's capabilities,
> >and then to your TSearch code when we outgrew ASPSeek.
>
> At risk of pulling this way too far off topic, may I ask how many
> documents (mail messages) you were dealing with when things started to
> fall apart with mnogo? We're looking at it for a new project that will
> hopefully get bigger and bigger. We will be throwing groups of mailing
> lists into their own mnogo config/tables... If we should save ourselves
> the pain and look at something more homebrew, then we'll start
> investigating "Tsearch".
I don't know when it broke exactly, but I know we're currently doing
about 600,000 documents. AFAIK it started to fall apart pretty long
before that.
//Magnus
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-28 08:33 Dave Page <dpage@postgresql.org>
parent: Charles Sprickman <spork@bway.net>
1 sibling, 0 replies; 21+ messages in thread
From: Dave Page @ 2007-02-28 08:33 UTC (permalink / raw)
To: Charles Sprickman <spork@bway.net>; +Cc: Magnus Hagander <magnus@hagander.net>; Madison Kelly <linux@alteeve.com>; pgsql-performance
Charles Sprickman wrote:
> On Tue, 27 Feb 2007, Dave Page wrote:
>
>> Magnus Hagander wrote:
>>>
>>> Just as a datapoint, we did try to use mnogosearch for the
>>> postgresql.org website+archives search, and it fell over completely.
>>> Indexing took way too long, and we had search times several thousand
>>> times longer than with tsearch2.
>>>
>>> That said, I'm sure there are cases when it works fine :-)
>>
>> There are - in fact before your time the site did use Mnogosearch. We
>> moved to our own port of ASPSeek when we outgrew Mnogo's capabilities,
>> and then to your TSearch code when we outgrew ASPSeek.
>
> At risk of pulling this way too far off topic, may I ask how many
> documents (mail messages) you were dealing with when things started to
> fall apart with mnogo?
I honestly don't remember now, but it would have been in the tens or
maybe low hundreds of thousands. Don't get me wrong, I've built sites
where Mnogo is still running fine and does a great job - it just doesn't
scale well.
> We're looking at it for a new project that will
> hopefully get bigger and bigger. We will be throwing groups of mailing
> lists into their own mnogo config/tables... If we should save ourselves
> the pain and look at something more homebrew, then we'll start
> investigating "Tsearch".
Well put it this way, the PostgreSQL mailing list archives outgrew Mnogo
years ago and even ASPSeek was beginning to struggle when it got removed
a few months back.
Regards, Dave
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-28 11:16 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Dave Page <dpage@postgresql.org>
1 sibling, 1 reply; 21+ messages in thread
From: Steinar H. Gunderson @ 2007-02-28 11:16 UTC (permalink / raw)
To: pgsql-performance
On Tue, Feb 27, 2007 at 01:33:47PM +0000, Dave Page wrote:
> When we outgrow PostgreSQL & Tsearch2, then, well, we'll need to stop
> pretending to be Google...
Just for the record: Google has been known to sponsor sites in need with
Google Minis and such earlier -- I don't know what their[1] policy is on the
matter, but if tsearch2 should at some point stop being usable for indexing
postgresql.org, asking them might be worth a shot.
[1] Technically "our", as I start working there in July. I do not speak for
Google, etc., blah blah. :-)
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-28 11:39 Dave Page <dpage@postgresql.org>
parent: Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 1 reply; 21+ messages in thread
From: Dave Page @ 2007-02-28 11:39 UTC (permalink / raw)
To: pgsql-performance
Steinar H. Gunderson wrote:
> On Tue, Feb 27, 2007 at 01:33:47PM +0000, Dave Page wrote:
>> When we outgrow PostgreSQL & Tsearch2, then, well, we'll need to stop
>> pretending to be Google...
>
> Just for the record: Google has been known to sponsor sites in need with
> Google Minis and such earlier -- I don't know what their[1] policy is on the
> matter, but if tsearch2 should at some point stop being usable for indexing
> postgresql.org, asking them might be worth a shot.
I think if postgresql.org outgrows tsearch2 then the preferred solution
would be to improve tsearch2/postgresql, but thanks for the tip :-)
> [1] Technically "our", as I start working there in July.
Congratulations :-)
Regards, Dave
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-28 12:35 Oleg Bartunov <oleg@sai.msu.su>
parent: Dave Page <dpage@postgresql.org>
0 siblings, 1 reply; 21+ messages in thread
From: Oleg Bartunov @ 2007-02-28 12:35 UTC (permalink / raw)
To: Dave Page <dpage@postgresql.org>; +Cc: pgsql-performance
On Wed, 28 Feb 2007, Dave Page wrote:
> Steinar H. Gunderson wrote:
>> On Tue, Feb 27, 2007 at 01:33:47PM +0000, Dave Page wrote:
>>> When we outgrow PostgreSQL & Tsearch2, then, well, we'll need to stop
>>> pretending to be Google...
>>
>> Just for the record: Google has been known to sponsor sites in need with
>> Google Minis and such earlier -- I don't know what their[1] policy is on the
>> matter, but if tsearch2 should at some point stop being usable for indexing
>> postgresql.org, asking them might be worth a shot.
>
> I think if postgresql.org outgrows tsearch2 then the preferred solution
> would be to improve tsearch2/postgresql, but thanks for the tip :-)
Guys, current tsearch2 should works with millions of documents. Actually,
the performance killer is the necessity to consult heap to calculate rank
which is unavoidably slow, since one need to read all records.
Search itself is incredibly fast ! If we find a way to store an additional
information in index and workout visibility issue, full text search will
be damn fast.
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Writting a "search engine" for a pgsql DB
@ 2007-02-28 12:40 Dave Page <dpage@postgresql.org>
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 21+ messages in thread
From: Dave Page @ 2007-02-28 12:40 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: pgsql-performance
Oleg Bartunov wrote:
> Guys, current tsearch2 should works with millions of documents.
...
> Search itself is incredibly fast !
Oh, I know - you and Teodor have done a wonderful job.
Regards, Dave.
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2007-02-28 12:40 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-26 16:29 Writting a "search engine" for a pgsql DB Madison Kelly <linux@alteeve.com>
2007-02-26 17:04 ` Joshua D. Drake <jd@commandprompt.com>
2007-02-26 17:32 ` Mark Stosberg <mark@summersault.com>
2007-02-26 17:41 ` Joshua D. Drake <jd@commandprompt.com>
2007-02-26 17:47 ` Madison Kelly <linux@alteeve.com>
2007-02-26 17:55 ` Magnus Hagander <magnus@hagander.net>
2007-02-26 18:15 ` Mark Stosberg <mark@summersault.com>
2007-02-26 18:59 ` Madison Kelly <linux@alteeve.com>
2007-02-26 19:46 ` Mark Stosberg <mark@summersault.com>
2007-02-26 20:14 ` Jeff Davis <pgsql@j-davis.com>
2007-02-26 21:24 ` Charles Sprickman <spork@bway.net>
2007-02-27 13:15 ` Magnus Hagander <magnus@hagander.net>
2007-02-27 13:33 ` Dave Page <dpage@postgresql.org>
2007-02-27 23:36 ` Charles Sprickman <spork@bway.net>
2007-02-28 07:44 ` Magnus Hagander <magnus@hagander.net>
2007-02-28 08:33 ` Dave Page <dpage@postgresql.org>
2007-02-28 11:16 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-02-28 11:39 ` Dave Page <dpage@postgresql.org>
2007-02-28 12:35 ` Oleg Bartunov <oleg@sai.msu.su>
2007-02-28 12:40 ` Dave Page <dpage@postgresql.org>
2007-02-27 23:05 ` Chris <dmagick@gmail.com>
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