Could you please post output of below queries:
explain select c.clause, s.* from clause2 c, svo2 s where c.clause_id=s.clause_id;
explain select c.clause, s.* from clause2 c, svo2 s where s.doc_id=c.source_id;
explain select c.clause, s.* from clause2 c, svo2 s where c.sentence_id=s.sentence_id ;
As per your instructions, Please check the below output :-
pdc_uima=# explain select c.clause, s.* from clause2 c, svo2 s where c.clause_id=s.clause_id;
QUERY PLAN
---------------------------------------------------------------------------------
Hash Join (cost=7828339.10..4349603998133.96 rows=379772050555842 width=2053)
Hash Cond: (c.clause_id = s.clause_id)
-> Seq Scan on clause2 c (cost=0.00..770951.84 rows=31853084 width=64)
-> Hash (cost=697537.60..697537.60 rows=27471560 width=1993)
-> Seq Scan on svo2 s (cost=0.00..697537.60 rows=27471560 width=1993)
(5 rows)
pdc_uima=# explain select c.clause, s.* from clause2 c, svo2 s where s.doc_id=c.source_id;
QUERY PLAN
---------------------------------------------------------------------------------------
Merge Join (cost=43635232.12..358368926.66 rows=20954686217 width=2053)
Merge Cond: (c.source_id = s.doc_id)
-> Sort (cost=5596061.24..5675693.95 rows=31853084 width=64)
Sort Key: c.source_id
-> Seq Scan on clause2 c (cost=0.00..770951.84 rows=31853084 width=64)
-> Materialize (cost=38028881.02..38372275.52 rows=27471560 width=1993)
-> Sort (cost=38028881.02..38097559.92 rows=27471560 width=1993)
Sort Key: s.doc_id
-> Seq Scan on svo2 s (cost=0.00..697537.60 rows=27471560 width=1993)
(9 rows)
pdc_uima=# explain select c.clause, s.* from clause2 c, svo2 s where c.sentence_id=s.sentence_id ;
QUERY PLAN
---------------------------------------------------------------------------------------
Merge Join (cost=43711844.03..241541026048.10 rows=PLeaswidth=2053)
Merge Cond: (c.sentence_id = s.sentence_id)
-> Sort (cost=5596061.24..5675693.95 rows=31853084 width=64)
Sort Key: c.sentence_id
-> Seq Scan on clause2 c (cost=0.00..770951.84 rows=31853084 width=64)
-> Materialize (cost=38028881.02..38372275.52 rows=27471560 width=1993)
-> Sort (cost=38028881.02..38097559.92 rows=27471560 width=1993)
Sort Key: s.sentence_id
-> Seq Scan on svo2 s (cost=0.00..697537.60 rows=27471560 width=1993)
(9 rows)
Please let me know if any other information is required.
--
Best Regards,
Adarsh Sharma