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 22AB5D1B20D for ; Tue, 6 Jul 2004 14:54:15 -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 80926-10 for ; Tue, 6 Jul 2004 17:54:10 +0000 (GMT) Received: from hotmail.com (bay17-dav9.bay17.hotmail.com [64.4.43.189]) by svr1.postgresql.org (Postfix) with ESMTP id 13A29D1B1D5 for ; Tue, 6 Jul 2004 14:54:08 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Tue, 6 Jul 2004 10:52:31 -0700 Received: from 142.104.250.115 by bay17-dav9.bay17.hotmail.com with DAV; Tue, 06 Jul 2004 17:52:31 +0000 X-Originating-IP: [142.104.250.115] X-Originating-Email: [borajetta@hotmail.com] X-Sender: borajetta@hotmail.com From: "borajetta" To: Subject: Keyword searching question Date: Tue, 6 Jul 2004 10:52:59 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0013_01C46347.66E63FD0" 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: 06 Jul 2004 17:52:31.0540 (UTC) FILETIME=[02B2FF40:01C46382] 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_20_30, HTML_MESSAGE, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200407/17 X-Sequence-Number: 7408 This is a multi-part message in MIME format. ------=_NextPart_000_0013_01C46347.66E63FD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have been trying to get keyword searching quicker and have now decided to= make smaller tables. I was working with tables of about 50 000 000 rows but now will use about 1= .5million rows, I am using a TSearch2 to find keywords in the titles column= of the rows. I have indexes using a gist index on the TSearch2 using 'def= ault'. I wanted to know if it would be quicker to change my words in the title int= o crc32 bit integers and then look for them using an index which would be o= n the crc numbers. I could then cluster on the crc numbers and this should= result in a fast search but I just want to know if you know of better ways= of doing keyword searching, I need it to be about 1-4 seconds and return u= sually around 1-5 thousand rows. I know places like google and ebay do seaches and only return 200 or 100 ma= x rows, this can be done on our current system very fast as you just limit = the number returned but for our purposes I need to return all the rows, Thanks smokedvw ------=_NextPart_000_0013_01C46347.66E63FD0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I have been trying to get keyword searchin= g quicker=20 and have now decided to make smaller tables.
 
I was working with tables of about 50 000 = 000 rows=20 but now will use about 1.5million rows, I am using a TSearch2 to find keywo= rds=20 in the titles column of the rows.  I have indexes using a gist index o= n the=20 TSearch2 using 'default'.
I wanted to know if it would be quicker to= change=20 my words in the title into crc32 bit integers and then look for them using = an=20 index which would be on the crc numbers.  I could then cluster on the = crc=20 numbers and this should result in a fast search but I just want to know if = you=20 know of better ways of doing keyword searching, I need it to be about 1-4= =20 seconds and return usually around 1-5 thousand rows.
 
I know places like google and ebay do seac= hes and=20 only return 200 or 100 max rows, this can be done on our current system ver= y=20 fast as you just limit the number returned but for our purposes I need to r= eturn=20 all the rows,
 
Thanks
smokedvw
------=_NextPart_000_0013_01C46347.66E63FD0--