Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 301BE1337BBA
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 25 Mar 2011 05:50:46 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 65891-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 25 Mar 2011 08:50:28 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp160.dfw.emailsrvr.com (smtp160.dfw.emailsrvr.com
	[67.192.241.160])
	by mail.postgresql.org (Postfix) with ESMTP id 085C11337B4E
	for <pgsql-performance@postgresql.org>;
	Fri, 25 Mar 2011 05:50:27 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by smtp26.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id
	897A380051; Fri, 25 Mar 2011 04:50:27 -0400 (EDT)
X-Virus-Scanned: OK
Received: by smtp26.relay.dfw1a.emailsrvr.com (Authenticated sender:
	adarsh.sharma-AT-orkash.com) with ESMTPSA id 0732E800CA;
	Fri, 25 Mar 2011 04:50:25 -0400 (EDT)
Message-ID: <4D8C5881.4090806@orkash.com>
Date: Fri, 25 Mar 2011 14:25:29 +0530
From: Adarsh Sharma <adarsh.sharma@orkash.com>
User-Agent: Thunderbird 2.0.0.22 (X11/20090625)
MIME-Version: 1.0
To: Chetan Suttraway <chetan.suttraway@enterprisedb.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Why Index is not used
References: <4D8C37CA.3090601@orkash.com>
	<AANLkTinqGxRkVHuvcYLRtajrWhFWpDDjpTHuNV9_at9A@mail.gmail.com>
	<4D8C3FAB.7070001@orkash.com>
	<AANLkTin7m2cjph=ijKXWZR8_EwKq9Zg1C5Sh73SqRqWX@mail.gmail.com>
In-Reply-To: <AANLkTin7m2cjph=ijKXWZR8_EwKq9Zg1C5Sh73SqRqWX@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------030803070505090705060609"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201103/326
X-Sequence-Number: 43005

This is a multi-part message in MIME format.
--------------030803070505090705060609
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>
>
>
> 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
>
>


--------------030803070505090705060609
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<blockquote
 cite="mid:AANLkTin7m2cjph=ijKXWZR8_EwKq9Zg1C5Sh73SqRqWX@mail.gmail.com"
 type="cite">
  <div class="gmail_quote">
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"></blockquote>
  </div>
  <br>
Could you please post output of below queries:<br>
explain select c.clause, s.* from clause2 c, svo2 s where
c.clause_id=s.clause_id;<br>
explain select c.clause, s.* from clause2 c, svo2 s where
s.doc_id=c.source_id;<br>
explain select c.clause, s.* from clause2 c, svo2 s where
c.sentence_id=s.sentence_id ;<br>
</blockquote>
<br>
<br>
As per your instructions, Please&nbsp; check the below output :-<br>
<br>
pdc_uima=# explain select c.clause, s.* from clause2 c, svo2 s where
c.clause_id=s.clause_id;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY
PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
---------------------------------------------------------------------------------<br>
&nbsp;Hash Join&nbsp; (cost=7828339.10..4349603998133.96 rows=379772050555842
width=2053)<br>
&nbsp;&nbsp; Hash Cond: (c.clause_id = s.clause_id)<br>
&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on clause2 c&nbsp; (cost=0.00..770951.84 rows=31853084
width=64)<br>
&nbsp;&nbsp; -&gt;&nbsp; Hash&nbsp; (cost=697537.60..697537.60 rows=27471560 width=1993)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on svo2 s&nbsp; (cost=0.00..697537.60 rows=27471560
width=1993)<br>
(5 rows)<br>
<br>
pdc_uima=# explain select c.clause, s.* from clause2 c, svo2 s where
s.doc_id=c.source_id;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY
PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
---------------------------------------------------------------------------------------<br>
&nbsp;Merge Join&nbsp; (cost=43635232.12..358368926.66 rows=20954686217
width=2053)<br>
&nbsp;&nbsp; Merge Cond: (c.source_id = s.doc_id)<br>
&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=5596061.24..5675693.95 rows=31853084 width=64)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: c.source_id<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on clause2 c&nbsp; (cost=0.00..770951.84
rows=31853084 width=64)<br>
&nbsp;&nbsp; -&gt;&nbsp; Materialize&nbsp; (cost=38028881.02..38372275.52 rows=27471560
width=1993)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=38028881.02..38097559.92 rows=27471560
width=1993)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: s.doc_id<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on svo2 s&nbsp; (cost=0.00..697537.60
rows=27471560 width=1993)<br>
(9 rows)<br>
<br>
pdc_uima=# explain select c.clause, s.* from clause2 c, svo2 s where
c.sentence_id=s.sentence_id ;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY
PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
---------------------------------------------------------------------------------------<br>
&nbsp;Merge Join&nbsp; (cost=43711844.03..241541026048.10 rows=PLeaswidth=2053)<br>
&nbsp;&nbsp; Merge Cond: (c.sentence_id = s.sentence_id)<br>
&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=5596061.24..5675693.95 rows=31853084 width=64)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: c.sentence_id<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on clause2 c&nbsp; (cost=0.00..770951.84
rows=31853084 width=64)<br>
&nbsp;&nbsp; -&gt;&nbsp; Materialize&nbsp; (cost=38028881.02..38372275.52 rows=27471560
width=1993)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=38028881.02..38097559.92 rows=27471560
width=1993)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: s.sentence_id<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on svo2 s&nbsp; (cost=0.00..697537.60
rows=27471560 width=1993)<br>
(9 rows)<br>
<br>
Please&nbsp; let me know if any other information is required.<br>
<br>
<br>
<br>
<br>
<br>
<blockquote
 cite="mid:AANLkTin7m2cjph=ijKXWZR8_EwKq9Zg1C5Sh73SqRqWX@mail.gmail.com"
 type="cite"><br>
-- <br>
Best Regards,<br>
Adarsh Sharma<br>
  <br>
  <br>
</blockquote>
<br>
</body>
</html>

--------------030803070505090705060609--