Skip site navigation (1) Skip section navigation (2)

Re: Why Index is not used

From: Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>
To: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why Index is not used
Date: 2011-03-25 06:51:50
Message-ID: 4D8C3B86.3040208@orkash.com (view raw or flat)
Thread:
Lists: pgsql-performance
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_id and 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.310 rows=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)


Thanks , Adarsh

Andreas Kretschmer wrote:
> Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> 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.
>>     
>
> How many rows contains clause2? The planner expected 167324179 returning
> rows, can you run the same explain with ANALYSE to see the real amount
> of returning rows?
>
>
> Andreas
>   

In response to

Responses

pgsql-performance by date

Next:From: Chetan SuttrawayDate: 2011-03-25 06:56:09
Subject: Re: Why Index is not used
Previous:From: Andreas KretschmerDate: 2011-03-25 06:44:27
Subject: Re: Why Index is not used

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group