Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id BD82D1337C26 for ; Wed, 16 Mar 2011 06:09:07 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 69303-02 for ; Wed, 16 Mar 2011 09:09:00 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp200.dfw.emailsrvr.com (smtp200.dfw.emailsrvr.com [67.192.241.200]) by mail.postgresql.org (Postfix) with ESMTP id 17BA21337C1C for ; Wed, 16 Mar 2011 06:08:43 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp10.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id C276E1B8164; Wed, 16 Mar 2011 05:08:43 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp10.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id CF2B21B813E for ; Wed, 16 Mar 2011 05:08:42 -0400 (EDT) Message-ID: <4D807F42.7000906@orkash.com> Date: Wed, 16 Mar 2011 14:43:38 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Help with Query Tuning Content-Type: multipart/alternative; boundary="------------010102050708090601060105" 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/162 X-Sequence-Number: 42841 This is a multi-part message in MIME format. --------------010102050708090601060105 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------010102050708090601060105 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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

--------------010102050708090601060105--