Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1B73C1337B2F for ; Thu, 17 Mar 2011 01:51:40 -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 89050-07 for ; Thu, 17 Mar 2011 04:51:32 +0000 (UTC) X-Greylist: delayed 00:07:50.860236 by SQLgrey-1.7.6 Received: from smtp140.iad.emailsrvr.com (smtp140.iad.emailsrvr.com [207.97.245.140]) by mail.postgresql.org (Postfix) with ESMTP id 71EFE133616B for ; Thu, 17 Mar 2011 01:51:32 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp44.relay.iad1a.emailsrvr.com (SMTP Server) with ESMTP id 05AFA12862B; Thu, 17 Mar 2011 00:43:41 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp44.relay.iad1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 7F858128435; Thu, 17 Mar 2011 00:43:39 -0400 (EDT) Message-ID: <4D8192A4.6080101@orkash.com> Date: Thu, 17 Mar 2011 10:18:36 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: Kenneth Marshall CC: pgsql-performance@postgresql.org Subject: Re: Help with Query Tuning References: <4D807F42.7000906@orkash.com> <20110316163653.GF11541@aart.is.rice.edu> In-Reply-To: <20110316163653.GF11541@aart.is.rice.edu> Content-Type: multipart/alternative; boundary="------------040704050803030404030603" 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/178 X-Sequence-Number: 42857 This is a multi-part message in MIME format. --------------040704050803030404030603 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Thanks Marshall, would I need to change the data type of *content *column to tsvector and create a Gist Index on it. Best Regards, Adarsh Kenneth Marshall wrote: > On Wed, Mar 16, 2011 at 02:43:38PM +0530, Adarsh Sharma wrote: > >> Dear all, >> >> I am facing a problem while creating the index to make the below query run >> faster. My table size is near about 1065 MB and 428467 rows. >> >> explain analyze 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%'); >> >> *Output: >> >> * Aggregate (cost=107557.78..107557.79 rows=1 width=0) (actual >> time=18564.631..18564.631 rows=1 loops=1) >> -> Seq Scan on page_content (cost=0.00..107466.82 rows=36381 width=0) >> (actual time=0.146..18529.371 rows=59918 loops=1) >> Filter: ((content IS NOT NULL) AND (publishing_date ~~ >> '%2010%'::text) AND (content_language = 'en'::bpchar) AND (isprocessable = >> 1) AND (((content) >> ::text ~~ '%kill%'::text) OR ((content)::text ~~ '%injure%'::text)) AND >> (((content)::text ~~ '%Militant%'::text) OR ((content)::text ~~ >> '%jihad%'::text) OR ( >> (content)::text ~~ '%Mujahid%'::text) OR ((content)::text ~~ >> '%fedayeen%'::text) OR ((content)::text ~~ '%insurgent%'::text) OR >> ((content)::text ~~ '%terrori >> st%'::text) OR ((content)::text ~~ '%cadre%'::text) OR ((content)::text ~~ >> '%civilians%'::text) OR ((content)::text ~~ '%police%'::text) OR >> ((content)::text >> ~~ '%defence%'::text) OR ((content)::text ~~ '%cops%'::text) OR >> ((content)::text ~~ '%crpf%'::text) OR ((content)::text ~~ '%dsf%'::text) >> OR ((content)::text >> ~~ '%ssb%'::text))) >> Total runtime: 18564.673 ms >> >> >> *Index on that Table : >> >> *CREATE INDEX idx_page_id >> ON page_content >> USING btree >> (crawled_page_id); >> >> *Index I create :* >> CREATE INDEX idx_page_id_content >> ON page_content >> USING btree >> (crawled_page_id,content_language,publishing_date,isprocessable); >> >> *Index that fail to create: >> >> *CREATE INDEX idx_page_id_content1 >> ON page_content >> USING btree >> (crawled_page_id,content); >> >> Error :-ERROR: index row requires 13240 bytes, maximum size is 8191 >> ********** Error ********** >> >> ERROR: index row requires 13240 bytes, maximum size is 8191 >> SQL state: 54000 >> >> How to resolve this error >> Please give any suggestion to tune the query. >> >> Thanks & best Regards, >> >> Adarsh Sharma >> >> > > You should probably be looking at using full-text indexing: > > http://www.postgresql.org/docs/9.0/static/textsearch.html > > or limit the size of content for the index. > > Cheers, > Ken > --------------040704050803030404030603 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Thanks Marshall, would I need to change the data type  of content column to tsvector and create a Gist Index on it.

Best Regards,
Adarsh


Kenneth Marshall wrote: 
On Wed, Mar 16, 2011 at 02:43:38PM +0530, Adarsh Sharma wrote:
  
Dear all,

I am facing a problem while  creating the index to make the below query run 
faster. My table  size is near about 1065 MB and 428467 rows.

explain analyze 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%');

*Output:

* Aggregate  (cost=107557.78..107557.79 rows=1 width=0) (actual 
time=18564.631..18564.631 rows=1 loops=1)
  ->  Seq Scan on page_content  (cost=0.00..107466.82 rows=36381 width=0) 
(actual time=0.146..18529.371 rows=59918 loops=1)
        Filter: ((content IS NOT NULL) AND (publishing_date ~~ 
'%2010%'::text) AND (content_language = 'en'::bpchar) AND (isprocessable = 
1) AND (((content)
::text ~~ '%kill%'::text) OR ((content)::text ~~ '%injure%'::text)) AND 
(((content)::text ~~ '%Militant%'::text) OR ((content)::text ~~ 
'%jihad%'::text) OR (
(content)::text ~~ '%Mujahid%'::text) OR ((content)::text ~~ 
'%fedayeen%'::text) OR ((content)::text ~~ '%insurgent%'::text) OR 
((content)::text ~~ '%terrori
st%'::text) OR ((content)::text ~~ '%cadre%'::text) OR ((content)::text ~~ 
'%civilians%'::text) OR ((content)::text ~~ '%police%'::text) OR 
((content)::text
~~ '%defence%'::text) OR ((content)::text ~~ '%cops%'::text) OR 
((content)::text ~~ '%crpf%'::text) OR ((content)::text ~~ '%dsf%'::text) 
OR ((content)::text
~~ '%ssb%'::text)))
Total runtime: 18564.673 ms


*Index on that Table :

*CREATE INDEX idx_page_id
 ON page_content
 USING btree
 (crawled_page_id);

*Index I create :*
CREATE INDEX idx_page_id_content
 ON page_content
 USING btree
 (crawled_page_id,content_language,publishing_date,isprocessable);

*Index that fail to create:

*CREATE INDEX idx_page_id_content1
 ON page_content
 USING btree
 (crawled_page_id,content);

Error :-ERROR:  index row requires 13240 bytes, maximum size is 8191
********** Error **********

ERROR: index row requires 13240 bytes, maximum size is 8191
SQL state: 54000

How to resolve this error
Please give any suggestion to tune the query.

Thanks & best Regards,

Adarsh Sharma

    

You should probably be looking at using full-text indexing:

http://www.postgresql.org/docs/9.0/static/textsearch.html

or limit the size of content for the index.

Cheers,
Ken

--------------040704050803030404030603--