Dear all,
Today I got to run a query internally from my application by more than
10 connections.
But The query performed very badly. A the data size of tables are as :
pdc_uima=# select pg_size_pretty(pg_total_relation_size('clause2'));
pg_size_pretty
----------------
5858 MB
(1 row)
pdc_uima=# select pg_size_pretty(pg_total_relation_size('svo2'));
pg_size_pretty
----------------
4719 MB
(1 row)
I explain the query as after making the indexes as :
pdc_uima=# explain select c.clause, s.* from clause2 c, svo2 s where
c.clause_id=s.clause_id and s.doc_id=c.source_id and c.
pdc_uima-# sentence_id=s.sentence_id ;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------
Merge Join (cost=5673831.05..34033959.87 rows=167324179 width=2053)
Merge Cond: ((s.clause_id = c.clause_id) AND (s.doc_id = c.source_id)
AND (s.sentence_id = c.sentence_id))
-> Index Scan using idx_svo2 on svo2 s (cost=0.00..24489343.65
rows=27471560 width=1993)
-> Materialize (cost=5673828.74..6071992.29 rows=31853084 width=72)
-> Sort (cost=5673828.74..5753461.45 rows=31853084 width=72)
Sort Key: c.clause_id, c.source_id, c.sentence_id
-> Seq Scan on clause2 c (cost=0.00..770951.84
rows=31853084 width=72)
Indexes are :
CREATE INDEX idx_clause ON clause2 USING btree (clause_id, source_id,
sentence_id);
CREATE INDEX idx_svo2 ON svo2 USING btree (clause_id, doc_id,
sentence_id);
I don't know why it not uses the index scan for clause2 table.
Any suggestions to tune the query.
Thanks & best Regards,
Adarsh Sharma
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
