On 05/16/2011 01:39 PM, Adarsh Sharma wrote:
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
Using limit and offset can be horrifyingly slow for non-trivial
queries. Are you trying to paginate results? If not, what are you
trying to achieve?
In most (all?) cases, Pg will have to execute the query up to the
point where it's found limit+offset rows, producing and discarding
offset rows as it goes. Needless to say, that's horrifyingly
inefficient.
Reformatting your query for readability (to me) as:
EXPLAIN ANALYZE
SELECT c.clause, s.subject ,s.object , s.verb, s.subject_type,
s.object_type ,s.doc_id ,s.svo_id
FROM clause2 c INNER JOIN svo2 s ON (c.clause_id=s.clause_id AND
c.source_id=s.doc_id AND c.sentence_id=s.sentence_id)
INNER JOIN page_content p ON
(s.doc_id=p.crawled_page_id)
ORDER BY s.svo_id limit 1000 offset 17929000
... I can see that you're joining on
(c.clause_id,c.source_id,c.sentence_id)=(s.clause_id,s.doc_id,s.sentence_id).
You have matching indexes idx_clause2_id and idx_svo2_id_dummy with
matching column ordering. Pg is using idx_clause2_id in the join of
svo2 and clause2, but instead of doing a bitmap index scan using it
and idx_svo2_id_dummy it's doing a nested loop using idx_clause2_id
and pk_svo_id.
First: make sure your stats are up to date by ANALYZE-ing your
tables and probably increasing the stats collected on the join
columns and/or increasing default_statistics_target. If that doesn't
help, personally I'd play with the random_page_cost and
seq_page_cost to see if they reflect your machine's actual
performance, and to see if you get a more favourable plan. If I were
experimenting with this I'd also see if giving the query lots of
work_mem allowed it to try a different approach to the join.
"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
|
