Re: Why Index is not used

On Fri, Mar 25, 2011 at 2:25 PM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>wrote:

>
> 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
>
>
>
> The ideas is to have maximum filtering occuring on leading column of
index.
the first plan with only the predicates on clause_id is returning
379772050555842 rows whereas
in the second plan with doc_id predicates is returning only 20954686217.

So maybe you should consider re-ordering of the index on clause2.

I am thinking that you created the indexes by looking at the columns used in
the where clause.
But its not always helpful to create indexes based on exact order of
predicates specified in query.
Instead the idea should be consider the predicate which is going to do
filter out the results.
Likewise we should consider all possible uses of index columns across all
queries and then decide on the
order of columns for the composite index to be created.

Whats your take on this?

--
Regards,
Chetan Suttraway
EnterpriseDB <http://www.enterprisedb.com/>, The Enterprise
PostgreSQL<http://www.enterprisedb.com/>
company.