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: Chetan Suttraway <chetan(dot)suttraway(at)enterprisedb(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why Index is not used
Date: 2011-03-25 08:55:29
Message-ID: 4D8C5881.4090806@orkash.com (view raw or flat)
Thread:
Lists: pgsql-performance
>
>
>
> 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
>
>

In response to

Responses

pgsql-performance by date

Next:From: Chetan SuttrawayDate: 2011-03-25 09:07:36
Subject: Re: Why Index is not used
Previous:From: Marti RaudseppDate: 2011-03-25 08:30:00
Subject: Re: pg9.0.3 explain analyze running very slow compared to a different box with much less configuration

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