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
>

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org