X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A6751D1B281 for ; Mon, 14 Jun 2004 19:31:10 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 70228-10 for ; Mon, 14 Jun 2004 22:31:07 +0000 (GMT) Received: from hotmail.com (bay17-dav15.bay17.hotmail.com [64.4.43.195]) by svr1.postgresql.org (Postfix) with ESMTP id 9B310D1B19A for ; Mon, 14 Jun 2004 19:31:03 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Mon, 14 Jun 2004 15:22:19 -0700 Received: from 142.104.250.115 by bay17-dav15.bay17.hotmail.com with DAV; Mon, 07 Jun 2004 21:46:54 +0000 X-Originating-IP: [142.104.250.115] X-Originating-Email: [borajetta@hotmail.com] X-Sender: borajetta@hotmail.com From: "borajetta" To: Subject: 50 000 000 Table entries and I have to do a keyword search HELP NEEDED Date: Mon, 7 Jun 2004 14:47:17 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0018_01C44C9E.5410FE00" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: X-OriginalArrivalTime: 14 Jun 2004 22:22:19.0625 (UTC) FILETIME=[0E763D90:01C4525E] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_MESSAGE, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200406/147 X-Sequence-Number: 7205 This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C44C9E.5410FE00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable So I have a table with about 50 million entries in it, I have to do a keywo= rd search. =20 The keyword search is done on the title of the entry. For example a entry = could be "This is a title string which could be searched" =20 I have tried a few ways to search but I get horrible search times. Some ke= ywords will come up with matches as big as .25 million but most are around = 1000-5000. =20 I use an index which narrows the table down to about 1.5-2million entries. =20 I used 2 tables which had a 1:1 correspondence. One held a gist index which was on a int field which searched the for the k= eyword. Then I would join the table to another to retrieve the rest of the= information about the items it matched. =20 This was slow even for returning 100 entries. About 10 seconds, sometimes = 5. But when I start getting 1xxx entries its about 30-50 seconds. The res= t is just horrible. =20 How should I set up my indexes and or tables. We were thinking of putting the index inside one table then the join would = not have to be done but this still returns rather slow results. =20 I have not fully tested this method but it looks like when run for just the= keyword search on the title and no joining it can return in about 10 secon= ds or less. This is a great improvement but I am currently going to make the table all = in one and see how long it will take. I believe it will not be much more a= s there will be no join needed only the returning of some attribute fields.= =20=20 =20 This is still not the kind of time I would like to see, I wanted something = around 2 seconds or less. I know there is a lot of information especially = if .25 million rows are to be returned but if there is only 1xxx-9xxx rows = to be returned I believe 2 seconds seems about right. =20 How do search engines do it? Any suggestions are welcome, =20 Thanks ------=_NextPart_000_0018_01C44C9E.5410FE00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

So I have a table with about 50 million entries in it, I have to d= o a=20 keyword search.

 

The keyword search is done on the title of the entry.  For example a entry could be "Thi= s is a=20 title string which could be searched"

 

I have tried a few ways to search but I get horrible search times.=   Some keywords will come up with m= atches=20 as big as .25 million but most are around 1000-5000.

 

I use an index which narrows the table down to about 1.5-2million= =20 entries.

 

I used 2 tables which had a 1:1 correspondence.

One held a gist index which was on a int field which searched the = for the=20 keyword.  Then I would join t= he=20 table to another to retrieve the rest of the information about the items it= =20 matched.

 

This was slow even for returning 100 entries.  About 10 seconds, sometimes 5.  But when I start getting 1xxx ent= ries=20 its about 30-50 seconds.  The= rest=20 is just horrible.

 

How should I set up my indexes and or tables.

We were thinking of putting the index inside one table then the jo= in=20 would not have to be done but this still returns rather slow results.

 

I have not fully tested this method but it looks like when run for= just=20 the keyword search on the title and no joining it can return in about 10 se= conds=20 or less.

This is a great improvement but I am currently goi= ng to=20 make the table all in one and see how long it will take.  I believe it will not be much mor= e as=20 there will be no join needed only the returning of some attribute fields. 

 

This is still not the kind of time I would like to see, I wanted= =20 something around 2 seconds or less. = =20 I know there is a lot of information especially if .25 million rows = are=20 to be returned but if there is only 1xxx-9xxx rows to be returned I believe= 2=20 seconds seems about right.

 

How do search engines do it?

Any suggestions are welcome,

 

Thanks

------=_NextPart_000_0018_01C44C9E.5410FE00--