Re: Why Index is not used

Adarsh Sharma, 25.03.2011 07:51:
>
> Thanks Andreas, I was about print the output but it takes too much time.
>
> Below is the output of explain analyze command :
> pdc_uima=# explain analyze select c.clause, s.* from clause2 c, svo2 s where c.clause_id=s.clause_id and
s.doc_id=c.source_idand c. 
> pdc_uima-# sentence_id=s.sentence_id ;
> QUERY PLAN
>
----------------------------------------------------------------------------------------------------------------------------------------------------
> Merge Join (cost=5673831.05..34033959.87 rows=167324179 width=2053) (actual time=216281.162..630721.636 rows=30473117
loops=1)
> 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) (actual
time=0.130..177599.310rows=27471560 loops=1) 
> -> Materialize (cost=5673828.74..6071992.29 rows=31853084 width=72) (actual time=216280.596..370507.452 rows=52037763
loops=1)
> -> Sort (cost=5673828.74..5753461.45 rows=31853084 width=72) (actual time=216280.591..324707.956 rows=31853083
loops=1)
> Sort Key: c.clause_id, c.source_id, c.sentence_id
> Sort Method: external merge Disk: 2616520kB
> -> Seq Scan on clause2 c (cost=0.00..770951.84 rows=31853084 width=72) (actual time=0.025..25018.665 rows=31853083
loops=1)
> Total runtime: 647804.037 ms
> (9 rows)
>
>
How many rows are there in clause2 in total?

31853084 rows are returned from that table which sounds like the whole table qualifies for the join condition.

Regards
Thomas