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 A3EA4D1B1A9 for ; Tue, 6 Jul 2004 14:57:32 -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 86115-01 for ; Tue, 6 Jul 2004 17:57:28 +0000 (GMT) Received: from hotmail.com (bay17-dav8.bay17.hotmail.com [64.4.43.188]) by svr1.postgresql.org (Postfix) with ESMTP id 0ABAAD1B1B0 for ; Tue, 6 Jul 2004 14:57:26 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Tue, 6 Jul 2004 10:56:01 -0700 Received: from 142.104.250.115 by bay17-dav8.bay17.hotmail.com with DAV; Tue, 06 Jul 2004 17:56:01 +0000 X-Originating-IP: [142.104.250.115] X-Originating-Email: [borajetta@hotmail.com] X-Sender: borajetta@hotmail.com From: "borajetta" To: Subject: Re: Keyword searching question Date: Tue, 6 Jul 2004 10:56:29 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0028_01C46347.E43B5CE0" 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:56:01.0326 (UTC) FILETIME=[7FBDCCE0:01C46382] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.9 tagged_above=0.0 required=5.0 tests=HTML_30_40, HTML_MESSAGE, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200407/18 X-Sequence-Number: 7409 This is a multi-part message in MIME format. ------=_NextPart_000_0028_01C46347.E43B5CE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Forgot to mention that were running post gres 7.4.2 using a dual optiron sy= stem and 4gig ram. The results are used by the PHP web server. Thanks again ----- Original Message -----=20 From: borajetta=20 To: pgsql-performance@postgresql.org=20 Sent: Tuesday, July 06, 2004 10:52 AM Subject: Keyword searching question 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 colu= mn of the rows. I have indexes using a gist index on the TSearch2 using 'd= efault'. I wanted to know if it would be quicker to change my words in the title i= nto crc32 bit integers and then look for them using an index which would be= on the crc numbers. I could then cluster on the crc numbers and this shou= ld result in a fast search but I just want to know if you know of better wa= ys of doing keyword searching, I need it to be about 1-4 seconds and return= usually around 1-5 thousand rows. I know places like google and ebay do seaches and only return 200 or 100 = max rows, this can be done on our current system very fast as you just limi= t the number returned but for our purposes I need to return all the rows, Thanks smokedvw ------=_NextPart_000_0028_01C46347.E43B5CE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Forgot to mention that were running post g= res 7.4.2=20 using a dual optiron system and 4gig ram. The results are used by the PHP w= eb=20 server.
Thanks again
----- Original Message -----
Fro= m:=20 borajetta
To: pgsql-performance@postgr= esql.org=20
Sent: Tuesday, July 06, 2004 10:52= =20 AM
Subject: Keyword searching questio= n

I have been trying to get keyword search= ing=20 quicker and have now decided to make smaller tables.
 
I was working with tables of about 50 00= 0 000=20 rows but now will use about 1.5million rows, I am using a TSearch2 to fin= d=20 keywords in the titles column of the rows.  I have indexes using a g= ist=20 index on the 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 usin= g an=20 index which would be on the crc numbers.  I could then cluster on th= e crc=20 numbers and this should result in a fast search but I just want to know i= f 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 se= aches and=20 only return 200 or 100 max rows, this can be done on our current system v= ery=20 fast as you just limit the number returned but for our purposes I need to= =20 return all the rows,
 
Thanks
smokedvw
------=_NextPart_000_0028_01C46347.E43B5CE0--