Re: Why Index is not used

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