Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id B7FD4B5D942 for ; Mon, 16 May 2011 02:39:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 42190-06 for ; Mon, 16 May 2011 05:39:48 +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 92140B5DBDC for ; Mon, 16 May 2011 02:39:48 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp12.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id 2E6BB3C01FE; Mon, 16 May 2011 01:39:46 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp12.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id D5F9C3C01FD for ; Mon, 16 May 2011 01:39:44 -0400 (EDT) Message-ID: <4DD0B8AD.3020906@orkash.com> Date: Mon, 16 May 2011 11:09:57 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Why query takes soo much time Content-Type: multipart/alternative; boundary="------------080401080703040007080701" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201105/234 X-Sequence-Number: 43743 This is a multi-part message in MIME format. --------------080401080703040007080701 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Dear all, I have a query on 3 tables in a database as :- _*Explain Analyze Output :-*_ explain anayze select c.clause, s.subject ,s.object , s.verb, s.subject_type , s.object_type ,s.doc_id ,s.svo_id from clause2 c, svo2 s ,page_content p where c.clause_id=s.clause_id and s.doc_id=c.source_id and c.sentence_id=s.sentence_id and s.doc_id=p.crawled_page_id order by s.svo_id limit 1000 offset 17929000 "Limit (cost=21685592.91..21686802.44 rows=1000 width=2624) (actual time=414601.802..414622.920 rows=1000 loops=1)" " -> Nested Loop (cost=59.77..320659013645.28 rows=265112018116 width=2624) (actual time=0.422..404902.314 rows=17930000 loops=1)" " -> Nested Loop (cost=0.00..313889654.42 rows=109882338 width=2628) (actual time=0.242..174223.789 rows=17736897 loops=1)" " -> Index Scan using pk_svo_id on svo2 s (cost=0.00..33914955.13 rows=26840752 width=2600) (actual time=0.157..14691.039 rows=14238271 loops=1)" " -> Index Scan using idx_clause2_id on clause2 c (cost=0.00..10.36 rows=4 width=44) (actual time=0.007..0.008 rows=1 loops=14238271)" " Index Cond: ((c.source_id = s.doc_id) AND (c.clause_id = s.clause_id) AND (c.sentence_id = s.sentence_id))" " -> Bitmap Heap Scan on page_content p (cost=59.77..2885.18 rows=2413 width=8) (actual time=0.007..0.008 rows=1 loops=17736897)" " Recheck Cond: (p.crawled_page_id = s.doc_id)" " -> Bitmap Index Scan on idx_crawled_id (cost=0.00..59.17 rows=2413 width=0) (actual time=0.005..0.005 rows=1 loops=17736897)" " Index Cond: (p.crawled_page_id = s.doc_id)" "Total runtime: 414623.634 ms" _*My Table & index definitions are as under :- *_Estimated rows in 3 tables are :- clause2 10341700 svo2 26008000 page_content 479785 CREATE TABLE clause2 ( id bigint NOT NULL DEFAULT nextval('clause_id_seq'::regclass), source_id integer, sentence_id integer, clause_id integer, tense character varying(30), clause text, CONSTRAINT pk_clause_id PRIMARY KEY (id) )WITH ( OIDS=FALSE); CREATE INDEX idx_clause2_id ON clause2 USING btree (source_id, clause_id, sentence_id); CREATE TABLE svo2 ( svo_id bigint NOT NULL DEFAULT nextval('svo_svo_id_seq'::regclass), doc_id integer, sentence_id integer, clause_id integer, negation integer, subject character varying(3000), verb character varying(3000), "object" character varying(3000), preposition character varying(3000), subject_type character varying(3000), object_type character varying(3000), subject_attribute character varying(3000), object_attribute character varying(3000), verb_attribute character varying(3000), subject_concept character varying(100), object_concept character varying(100), subject_sense character varying(100), object_sense character varying(100), subject_chain character varying(5000), object_chain character varying(5000), sub_type_id integer, obj_type_id integer, CONSTRAINT pk_svo_id PRIMARY KEY (svo_id) )WITH ( OIDS=FALSE); CREATE INDEX idx_svo2_id_dummy ON svo2 USING btree (doc_id, clause_id, sentence_id); CREATE TABLE page_content ( content_id integer NOT NULL DEFAULT nextval('page_content_ogc_fid_seq'::regclass), wkb_geometry geometry, link_level integer, isprocessable integer, isvalid integer, isanalyzed integer, islocked integer, content_language character(10), url_id integer, publishing_date character(40), heading character(150), category character(150), crawled_page_url character(500), keywords character(500), dt_stamp timestamp with time zone, "content" character varying, crawled_page_id bigint, CONSTRAINT page_content_pk PRIMARY KEY (content_id), CONSTRAINT enforce_dims_wkb_geometry CHECK (st_ndims(wkb_geometry) = 2), CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = (-1)) )WITH ( OIDS=FALSE); CREATE INDEX idx_crawled_id ON page_content USING btree (crawled_page_id); CREATE INDEX pgweb_idx ON page_content USING gin (to_tsvector('english'::regconfig, content::text)); If possible, Please let me know if I am something wrong or any alternate query to run it faster. Thanks --------------080401080703040007080701 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Dear all,
I have a query on 3 tables in a database as :-

Explain Analyze Output :-

explain anayze select c.clause, s.subject ,s.object , s.verb, s.subject_type , s.object_type ,s.doc_id ,s.svo_id from clause2 c, svo2 s ,page_content p where c.clause_id=s.clause_id and s.doc_id=c.source_id and c.sentence_id=s.sentence_id and s.doc_id=p.crawled_page_id order by s.svo_id limit 1000 offset 17929000

"Limit  (cost=21685592.91..21686802.44 rows=1000 width=2624) (actual time=414601.802..414622.920 rows=1000 loops=1)"
"  ->  Nested Loop  (cost=59.77..320659013645.28 rows=265112018116 width=2624) (actual time=0.422..404902.314 rows=17930000 loops=1)"
"        ->  Nested Loop  (cost=0.00..313889654.42 rows=109882338 width=2628) (actual time=0.242..174223.789 rows=17736897 loops=1)"
"              ->  Index Scan using pk_svo_id on svo2 s  (cost=0.00..33914955.13 rows=26840752 width=2600) (actual time=0.157..14691.039 rows=14238271 loops=1)"
"              ->  Index Scan using idx_clause2_id on clause2 c  (cost=0.00..10.36 rows=4 width=44) (actual time=0.007..0.008 rows=1 loops=14238271)"
"                    Index Cond: ((c.source_id = s.doc_id) AND (c.clause_id = s.clause_id) AND (c.sentence_id = s.sentence_id))"
"        ->  Bitmap Heap Scan on page_content p  (cost=59.77..2885.18 rows=2413 width=8) (actual time=0.007..0.008 rows=1 loops=17736897)"
"              Recheck Cond: (p.crawled_page_id = s.doc_id)"
"              ->  Bitmap Index Scan on idx_crawled_id  (cost=0.00..59.17 rows=2413 width=0) (actual time=0.005..0.005 rows=1 loops=17736897)"
"                    Index Cond: (p.crawled_page_id = s.doc_id)"
"Total runtime: 414623.634 ms"

My Table & index definitions are as under :-

Estimated rows in 3 tables are :-

clause2 10341700
svo2 26008000
page_content 479785

CREATE TABLE clause2
(
  id bigint NOT NULL DEFAULT nextval('clause_id_seq'::regclass),
  source_id integer,
  sentence_id integer,
  clause_id integer,
  tense character varying(30),
  clause text,
  CONSTRAINT pk_clause_id PRIMARY KEY (id)
)WITH ( OIDS=FALSE);
CREATE INDEX idx_clause2_id  ON clause2  USING btree (source_id, clause_id, sentence_id);

CREATE TABLE svo2
(
  svo_id bigint NOT NULL DEFAULT nextval('svo_svo_id_seq'::regclass),
  doc_id integer,
  sentence_id integer,
  clause_id integer,
  negation integer,
  subject character varying(3000),
  verb character varying(3000),
  "object" character varying(3000),
  preposition character varying(3000),
  subject_type character varying(3000),
  object_type character varying(3000),
  subject_attribute character varying(3000),
  object_attribute character varying(3000),
  verb_attribute character varying(3000),
  subject_concept character varying(100),
  object_concept character varying(100),
  subject_sense character varying(100),
  object_sense character varying(100),
  subject_chain character varying(5000),
  object_chain character varying(5000),
  sub_type_id integer,
  obj_type_id integer,
  CONSTRAINT pk_svo_id PRIMARY KEY (svo_id)
)WITH (  OIDS=FALSE);
CREATE INDEX idx_svo2_id_dummy  ON svo2  USING btree  (doc_id, clause_id, sentence_id);

CREATE TABLE page_content
(
  content_id integer NOT NULL DEFAULT nextval('page_content_ogc_fid_seq'::regclass),
  wkb_geometry geometry,
  link_level integer,
  isprocessable integer,
  isvalid integer,
  isanalyzed integer,
  islocked integer,
  content_language character(10),
  url_id integer,
  publishing_date character(40),
  heading character(150),
  category character(150),
  crawled_page_url character(500),
  keywords character(500),
  dt_stamp timestamp with time zone,
  "content" character varying,
  crawled_page_id bigint,
  CONSTRAINT page_content_pk PRIMARY KEY (content_id),
  CONSTRAINT enforce_dims_wkb_geometry CHECK (st_ndims(wkb_geometry) = 2),
  CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = (-1))
)WITH (  OIDS=FALSE);
CREATE INDEX idx_crawled_id  ON page_content  USING btree  (crawled_page_id);
CREATE INDEX pgweb_idx  ON page_content  USING gin  (to_tsvector('english'::regconfig, content::text));

If possible, Please let me know if I am something wrong or any alternate query to run it faster.


Thanks
--------------080401080703040007080701--