Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 63F551337BF7 for ; Fri, 18 Mar 2011 01:12:51 -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 35975-01 for ; Fri, 18 Mar 2011 04:12:43 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp120.dfw.emailsrvr.com (smtp120.dfw.emailsrvr.com [67.192.241.120]) by mail.postgresql.org (Postfix) with ESMTP id 4719C133655C for ; Fri, 18 Mar 2011 01:12:43 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp2.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id 583692A813A; Fri, 18 Mar 2011 00:12:42 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp2.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id CE2222A8118; Fri, 18 Mar 2011 00:12:40 -0400 (EDT) Message-ID: <4D82DCE2.5020902@orkash.com> Date: Fri, 18 Mar 2011 09:47:38 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: tv@fuzzy.cz CC: pgsql-performance@postgresql.org Subject: Re: Help with Query Tuning 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> In-Reply-To: <9033de88e9c4f443c9e88aa027530b73.squirrel@sq.gransy.com> Content-Type: multipart/alternative; boundary="------------050407010400000001020103" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201103/201 X-Sequence-Number: 42880 This is a multi-part message in MIME format. --------------050407010400000001020103 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 select count(*) from page_content where publishing_date like '%2010%' and content_language='en' and content is not null and isprocessable = 1 and (content like '%Militant%' OR content like '%jihad%' OR content like '%Mujahid%' OR content like '%fedayeen%' OR content like '%insurgent%' OR content like '%terrORist%' OR content like '%cadre%' OR content like '%civilians%' OR content like '%police%' OR content like '%defence%' OR content like '%cops%' OR content like '%crpf%' OR content like '%dsf%' OR content like '%ssb%') AND (content like '%kill%' OR content like '%injure%'); count ------- 57061 (1 row) Time: 19423.087 ms Now I have to add AND condition ( AND (content like '%kill%' OR content like '%injure%') ) also. Thanks & Regards, Adarsh Sharma tv@fuzzy.cz wrote: >> tv@fuzzy.cz wrote: >> >>>> Yes , I think we caught the problem but it results in the below error : >>>> >>>> SELECT count(*) from page_content >>>> WHERE publishing_date like '%2010%' and content_language='en' and >>>> content is not null and isprocessable = 1 and >>>> to_tsvector('english',content) @@ to_tsquery('english','Mujahid ' || >>>> 'jihad ' || 'Militant ' || 'fedayeen ' || 'insurgent ' || 'terrORist ' >>>> || 'cadre ' || 'civilians ' || 'police ' || 'defence ' || 'cops ' || >>>> 'crpf ' || 'dsf ' || 'ssb'); >>>> >>>> ERROR: syntax error in tsquery: "Mujahid jihad Militant fedayeen >>>> insurgent terrORist cadre civilians police defence cops crpf dsf ssb" >>>> >>>> >>> The text passed to to_tsquery has to be a proper query, i.e. single >>> tokens >>> separated by boolean operators. In your case, you should put there '|' >>> (which means OR) to get something like this >>> >>> 'Mujahid | jihad | Militant | ...' >>> >>> or you can use plainto_tsquery() as that accepts simple text, but it >>> puts >>> '&' (AND) between the tokens and I guess that's not what you want. >>> >>> Tomas >>> >>> >>> >> What to do to make it satisfies the OR condition to match any of the >> to_tsquery values as we got it right through like '%Mujahid' or ..... >> or .... >> > > You can't force the plainto_tsquery to somehow use the OR instead of AND. > You need to modify the piece of code that produces the search text to put > there '|' characters. So do something like this > > SELECT count(*) from page_content WHERE publishing_date like '%2010%' and > content_language='en' and content is not null and isprocessable = 1 and > to_tsvector('english',content) @@ to_tsquery('english','Mujahid' || ' | ' > || 'jihad' || ' | ' || 'Militant' || ' | ' || 'fedayeen); > > Not sure where does this text come from, but you can do this in a higher > level language, e.g. in PHP. Something like this > > $words = implode(' | ', explode(' ',$text)); > > and then pass the $words into the query. Or something like that. > > Tomas > > --------------050407010400000001020103 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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

select  count(*)  from page_content where publishing_date like '%2010%' and content_language='en'  and content is not null and isprocessable = 1 and (content like '%Militant%'
OR content like '%jihad%' OR  content like '%Mujahid%'  OR
 content like '%fedayeen%' OR content like '%insurgent%'  OR content like '%terrORist%' OR
  content like '%cadre%'  OR content like '%civilians%' OR content like '%police%' OR content like '%defence%' OR content like '%cops%' OR content like '%crpf%' OR content like '%dsf%' OR content like '%ssb%') AND (content like '%kill%' OR content like '%injure%');

 count
-------
 57061
(1 row)

Time: 19423.087 ms


Now I have to add AND condition (  AND (content like '%kill%' OR content like '%injure%')  )  also.


Thanks & Regards,
Adarsh Sharma



tv@fuzzy.cz wrote: 
tv@fuzzy.cz wrote:
    
Yes , I think we caught the problem but it results in the below error :

SELECT count(*)  from page_content
WHERE publishing_date like '%2010%' and content_language='en' and
content is not null and isprocessable = 1 and
to_tsvector('english',content) @@ to_tsquery('english','Mujahid ' ||
'jihad ' || 'Militant ' || 'fedayeen ' || 'insurgent ' || 'terrORist '
|| 'cadre ' || 'civilians ' || 'police ' || 'defence ' || 'cops ' ||
'crpf ' || 'dsf ' || 'ssb');

ERROR:  syntax error in tsquery: "Mujahid jihad Militant fedayeen
insurgent terrORist cadre civilians police defence cops crpf dsf ssb"

        
The text passed to to_tsquery has to be a proper query, i.e. single
tokens
separated by boolean operators. In your case, you should put there '|'
(which means OR) to get something like this

  'Mujahid | jihad | Militant | ...'

or you can use plainto_tsquery() as that accepts simple text, but it
puts
'&' (AND) between the tokens and I guess that's not what you want.

Tomas


      
What to do to make it satisfies the OR condition to match any of the
to_tsquery values as we got it right through like '%Mujahid' or .....
or ....
    

You can't force the plainto_tsquery to somehow use the OR instead of AND.
You need to modify the piece of code that produces the search text to put
there '|' characters. So do something like this

SELECT count(*)  from page_content WHERE publishing_date like '%2010%' and
content_language='en' and content is not null and isprocessable = 1 and
to_tsvector('english',content) @@ to_tsquery('english','Mujahid' || ' | '
|| 'jihad' || ' | ' || 'Militant' || ' | ' || 'fedayeen);

Not sure where does this text come from, but you can do this in a higher
level language, e.g. in PHP. Something like this

$words = implode(' | ', explode(' ',$text));

and then pass the $words into the query. Or something like that.

Tomas

--------------050407010400000001020103--