|
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 |
