Message-ID: <4D8C37CA.3090601@orkash.com>
Date: Fri, 25 Mar 2011 12:05:54 +0530
From: Adarsh Sharma <adarsh.sharma@orkash.com>
User-Agent: Thunderbird 2.0.0.22 (X11/20090625)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Why Index is not used
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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