Received: from int-mail.syd.fl.net.au (int-mail.syd.fl.net.au [202.181.0.28]) by hub.org (8.10.1/8.10.1) with ESMTP id e5NGHB175318 for ; Fri, 23 Jun 2000 12:17:11 -0400 (EDT) Received: from avon (avon.snowville.fl.net.au [202.181.1.250]) by int-mail.syd.fl.net.au (Postfix) with SMTP id 9DC8A16888; Sat, 24 Jun 2000 02:17:05 +1000 (EST) From: "Andrew Snow" To: "Vassili A Akimov" Cc: "Pgsql-General@Postgresql. Org" Subject: RE: Need to improve performance Date: Sat, 24 Jun 2000 02:17:05 +1000 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-Reply-To: <395373C5.B1C2A566@erols.com> X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 Importance: Normal X-Archive-Number: 200006/580 > So we were advised to use "fulltextindex".- chunk > this fields on single words and make new table with words and oids in > it. After we made this table its size was 2940360 records. And I tried > to measure the time: > > .... > > select f1.id from app_fti f1, app_fti f2 where f1.string~*'visual' and > f2.string~*'basic' and f1.id=f2.id; What is causing it to be so slow is the use of regular expression pattern matching. For reasons I don't know, regular expression evaluation in a query is fairly slow in PostgreSQL. But since you have (correctly, I think) made a full text index, why do you still need to use regexp's ? Replace the ~* operator in the query above with 'LIKE', and you should find it several times faster. Please let us know the results of your testing. - Andrew