Received: from cx379956-a.nwptn1.va.home.com (207-172-212-36.s36.tnt4.nrf.va.dialup.rcn.com [207.172.212.36]) by hub.org (8.10.1/8.10.1) with ESMTP id e5NHnl169550 for ; Fri, 23 Jun 2000 13:49:47 -0400 (EDT) Received: from erols.com (vasia@localhost [127.0.0.1]) by cx379956-a.nwptn1.va.home.com (8.9.1a/8.8.7) with ESMTP id NAA04777; Fri, 23 Jun 2000 13:43:52 -0400 Message-ID: <3953A1D8.DF3392CD@erols.com> Date: Fri, 23 Jun 2000 13:43:52 -0400 From: Vassili A Akimov Organization: Touch Technology X-Mailer: Mozilla 4.61 [en] (X11; U; Linux 2.2.14 i586) X-Accept-Language: ru, en MIME-Version: 1.0 To: Andrew Snow CC: "Pgsql-General@Postgresql. Org" , mitch@venux.net Subject: Re: Need to improve performance References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200006/583 Andrew Snow wrote: > > 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 Thank you gentelmen! It seems that regexp was the reason! I enabled likeplanning and it i=didn't improve the performance until I replaced '~*' with 'LIKE'. Thank you again! V.