Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 65AF9133655C for ; Fri, 18 Mar 2011 12:30:28 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 29000-03 for ; Fri, 18 Mar 2011 15:30:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from elizabeth.gransy.com (elizabeth.gransy.com [89.187.132.199]) by mail.postgresql.org (Postfix) with ESMTP id 3094C133616B for ; Fri, 18 Mar 2011 12:30:20 -0300 (ADT) Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 5BB3D15E432C; Fri, 18 Mar 2011 16:30:19 +0100 (CET) Received: from 85.161.56.102 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Fri, 18 Mar 2011 16:30:19 +0100 Message-ID: <4527c38f24af40c40594d001e09a2724.squirrel@sq.gransy.com> In-Reply-To: <4D82DCE2.5020902@orkash.com> References: <4D807F42.7000906@orkash.com> <20110316163653.GF11541@aart.is.rice.edu> <4D81A951.7030609@orkash.com> <4874ad87076c4b2c743083543cae2334.squirrel@sq.gransy.com> <4D81D9BF.8030402@orkash.com> <443b5cf42d93187ae72efaa54159d401.squirrel@sq.gransy.com> <4D81F2AA.9010706@orkash.com> <6caac0fce0f9956128e08d99ad839c0e.squirrel@sq.gransy.com> <4D81FDCA.9020308@orkash.com> <9033de88e9c4f443c9e88aa027530b73.squirrel@sq.gransy.com> <4D82DCE2.5020902@orkash.com> Date: Fri, 18 Mar 2011 16:30:19 +0100 Subject: Re: Help with Query Tuning From: tv@fuzzy.cz To: "Adarsh Sharma" Cc: tv@fuzzy.cz, pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.001 tagged_above=-10 required=5 tests=BAYES_20=-0.001 X-Spam-Level: X-Archive-Number: 201103/220 X-Sequence-Number: 42899 > Thanks , it works now .. :-) > > Here is the output : > > pdc_uima=# SELECT count(*) from page_content WHERE publishing_date like > '%2010%' and > pdc_uima-# content_language='en' and content is not null and > isprocessable = 1 and > pdc_uima-# to_tsvector('english',content) @@ > to_tsquery('english','Mujahid' || ' | ' > pdc_uima(# || 'jihad' || ' | ' || 'Militant' || ' | ' || 'fedayeen' || ' | > ' > pdc_uima(# || 'insurgent' || ' | ' || 'terrORist' || ' | ' || 'cadre' || > ' | ' > pdc_uima(# || 'civilians' || ' | ' || 'police' || ' | ' || 'cops' || > 'crpf' || ' | ' > pdc_uima(# || 'defence' || ' | ' || 'dsf' || ' | ' || 'ssb' ); > > count > -------- > 137193 > (1 row) > > Time: 195441.894 ms > > > But my original query is to use AND also i.e Hi, just replace "AND" and "OR" (used with LIKE operator) for "&" and "|" (used with to_tsquery). So this (content like '%Militant%' OR content like '%jihad%') AND (content like '%kill%' OR content like '%injure%') becomes to_tsvector('english',content) @@ to_tsquery('english', '(Militant | jihad) & (kill | injure)') BTW it seems you somehow believe you'll get exactly the same result from those two queries (LIKE vs. tsearch) - that's false expectation. I believe the fulltext query is much better and more appropriate in this case, just don't expect the same results. regards Tomas