From: tv@fuzzy.cz
To: Adarsh Sharma <adarsh.sharma@orkash.com>
Cc: tv@fuzzy.cz
Cc: pgsql-performance@postgresql.org
Subject: Re: Help with Query Tuning
Date: Fri, 18 Mar 2011 16:30:19 +0100
Message-ID: <4527c38f24af40c40594d001e09a2724.squirrel@sq.gransy.com> (raw)
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>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, adarsh.sharma@orkash.com
Subject: Re: Help with Query Tuning
In-Reply-To: <4527c38f24af40c40594d001e09a2724.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox