Re: [PERFORM] Writting a search engine for a pgsql DB
Steinar H. Gunderson wrote: On Tue, Feb 27, 2007 at 01:33:47PM +, 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
Re: [PERFORM] Writting a search engine for a pgsql DB
On Wed, 28 Feb 2007, Dave Page wrote: Steinar H. Gunderson wrote: On Tue, Feb 27, 2007 at 01:33:47PM +, 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
Re: [PERFORM] Writting a search engine for a pgsql DB
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Writting a search engine for a pgsql DB
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
Re: [PERFORM] Writting a search engine for a pgsql DB
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
Re: [PERFORM] Writting a search engine for a pgsql DB
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
Re: [PERFORM] Writting a search engine for a pgsql DB
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
Re: [PERFORM] Writting a search engine for a pgsql DB
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/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Writting a search engine for a pgsql DB
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
Re: [PERFORM] Writting a search engine for a pgsql DB
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/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Writting a search engine for a pgsql DB
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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Writting a search engine for a pgsql DB
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Writting a search engine for a pgsql DB
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=999556617big_mirror=0 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Writting a search engine for a pgsql DB
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Writting a search engine for a pgsql DB
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
