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 8945DD1B7CF for ; Tue, 15 Jun 2004 12:15:46 -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 23037-01 for ; Tue, 15 Jun 2004 15:15:44 +0000 (GMT) Received: from hotmail.com (sea1-dav25.sea1.hotmail.com [207.68.162.82]) by svr1.postgresql.org (Postfix) with ESMTP id 5BF6BD1B7BC for ; Tue, 15 Jun 2004 12:15:42 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Tue, 15 Jun 2004 08:15:41 -0700 Received: from 142.104.250.115 by sea1-dav25.sea1.hotmail.com with DAV; Tue, 15 Jun 2004 15:15:41 +0000 X-Originating-IP: [142.104.250.115] X-Originating-Email: [smokedvw@hotmail.com] X-Sender: smokedvw@hotmail.com From: "Aaron" To: Cc: "Postgresql Performance" References: <000601c452d6$68263570$4f01a8c0@jeremydunn> Subject: Re: 50 000 000 Table entries and I have to do a keyword search HELP NEEDED Date: Tue, 15 Jun 2004 08:16:06 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001F_01C452B1.0193F7E0" 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: 15 Jun 2004 15:15:41.0348 (UTC) FILETIME=[9F1CE640:01C452EB] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.7 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_FONTCOLOR_BLUE, HTML_MESSAGE, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200406/209 X-Sequence-Number: 7267 This is a multi-part message in MIME format. ------=_NextPart_000_001F_01C452B1.0193F7E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MessageThe words for the keyword can be made up of a sentace, ie 10 or more= keywords to one entry. Also incase I didnt answer before, we are using TSearch2 and all tables hav= e been fully analyzed and indexed. Any other suggestions? How long do searches take when 10 000 rows are returned? We can not use a limit of 100 because we need to analyze the entire data se= t returned. Thanks, ----- Original Message -----=20 From: Jeremy Dunn=20 To: 'borajetta'=20 Cc: Postgresql Performance=20 Sent: Tuesday, June 15, 2004 5:43 AM Subject: RE: [PERFORM] 50 000 000 Table entries and I have to do a keywor= d search HELP NEEDED One option that does not take advantage of any fancy indexing methods is = to create a trigger on the table, on insert/update/delete, which extracts e= ach individual word from the field you care about, and creates an entry in = another 'keyword' table, id =3D 'word', value =3D pk of your original table= . then index the keyword table on the 'keyword' field, and do your searche= s from there. this should improve performance substantially, even on very = large return sets, because the keyword table rows are very small and thus a= lot of them fit in a disk block. - Jeremy -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-= owner@postgresql.org] On Behalf Of borajetta Sent: Monday, June 07, 2004 5:47 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] 50 000 000 Table entries and I have to do a keyword = search HELP NEEDED So I have a table with about 50 million entries in it, I have to do a k= eyword search. =20=20=20=20=20 The keyword search is done on the title of the entry. For example a en= try could be "This is a title string which could be searched" =20=20=20=20=20 I have tried a few ways to search but I get horrible search times. Som= e keywords will come up with matches as big as .25 million but most are aro= und 1000-5000. =20=20=20=20=20 I use an index which narrows the table down to about 1.5-2million entri= es. =20=20=20=20=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 t= he keyword. Then I would join the table to another to retrieve the rest of= the information about the items it matched. =20=20=20=20=20 This was slow even for returning 100 entries. About 10 seconds, someti= mes 5. But when I start getting 1xxx entries its about 30-50 seconds. The= rest is just horrible. =20=20=20=20=20 How should I set up my indexes and or tables. We were thinking of putting the index inside one table then the join wo= uld not have to be done but this still returns rather slow results. =20=20=20=20=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 s= econds 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 mo= re as there will be no join needed only the returning of some attribute fie= lds.=20=20 =20=20=20=20=20 This is still not the kind of time I would like to see, I wanted someth= ing around 2 seconds or less. I know there is a lot of information especia= lly if .25 million rows are to be returned but if there is only 1xxx-9xxx r= ows to be returned I believe 2 seconds seems about right. =20=20=20=20=20 How do search engines do it? Any suggestions are welcome, =20=20=20=20=20 Thanks ------=_NextPart_000_001F_01C452B1.0193F7E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message
The words for the keyword can be made up o= f a=20 sentace, ie 10 or more keywords to one entry.
Also incase I didnt answer before, we are = using=20 TSearch2 and all tables have been fully analyzed and indexed.
Any other suggestions?
How long do searches take when 10 000 rows= are=20 returned?
We can not use a limit of 100 because we n= eed to=20 analyze the entire data set returned.
Thanks,
----- Original Message -----
Fro= m:=20 Je= remy=20 Dunn
Cc: Postgresql Performance=20
Sent: Tuesday, June 15, 2004 5:43= =20 AM
Subject: RE: [PERFORM] 50 000 000 = Table=20 entries and I have to do a keyword search HELP NEEDED

One=20 option that does not take advantage of any fancy indexing methods is to c= reate=20 a trigger on the table, on insert/update/delete, which extracts each=20 individual word from the field you care about, and creates an entry in an= other=20 'keyword' table, id =3D 'word', value =3D pk of your original table. = ; then=20 index the keyword table on the 'keyword' field, and do your searches from= =20 there.  this should improve performance substantially, even on very = large=20 return sets, because the keyword table rows are very small and thus a lot= of=20 them fit in a disk block.
 
-=20 Jeremy
<= FONT=20 face=3DTahoma size=3D2>-----Original Message-----
From:=20 pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20 borajetta
Sent: Monday, June 07, 2004 5:47 PM
To:=20 pgsql-performance@postgresql.org
Subject: [PERFORM] 50 000 00= 0=20 Table entries and I have to do a keyword search HELP=20 NEEDED

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

 

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

 

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

 

I use an index which narrows the table down to about 1.5-2mill= ion=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=20 the keyword.  Then I woul= d join=20 the table to another to retrieve the rest of the information about the = items=20 it matched.

 

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

 

How should I set up my indexes and or tables.

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

 

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

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

 

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

 

How do search engines do it?

Any suggestions are welcome,

 

Thanks

------=_NextPart_000_001F_01C452B1.0193F7E0--