Re: Why Index is not used

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Fri, Mar 25, 2011 at 12:39 PM, Adarsh Sharma <adarsh.sharma@xxxxxxxxxx> wrote:
Chetan Suttraway wrote:


On Fri, Mar 25, 2011 at 12:05 PM, Adarsh Sharma <adarsh.sharma@xxxxxxxxxx> wrote:
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.


In this case, there are no predicates or filters on individual table. (maybe something like c.source_id=10)
so either of the 2 tables will have to go for simple scan.

Are you expecting seq. scan on svo2 and index scan on clause2?


As per the size consideration and the number of rows, I think index scan on clause2 is better.

Your constraint is valid  but  I need to perform  this query faster. 
What is the reason behind the seq scan of clause2.



Regards,
Adarsh




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 ;

--
Regards,
Chetan Suttraway
EnterpriseDB, The Enterprise PostgreSQL company.




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux