Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 2F8641337990 for ; Fri, 25 Mar 2011 04:04:48 -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 24731-02 for ; Fri, 25 Mar 2011 07:04:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp180.dfw.emailsrvr.com (smtp180.dfw.emailsrvr.com [67.192.241.180]) by mail.postgresql.org (Postfix) with ESMTP id 0A3541337B43 for ; Fri, 25 Mar 2011 04:04:30 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp8.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id 04BF58263; Fri, 25 Mar 2011 03:04:29 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp8.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 6CF188234; Fri, 25 Mar 2011 03:04:27 -0400 (EDT) Message-ID: <4D8C3FAB.7070001@orkash.com> Date: Fri, 25 Mar 2011 12:39:31 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: Chetan Suttraway CC: pgsql-performance@postgresql.org Subject: Re: Why Index is not used References: <4D8C37CA.3090601@orkash.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------010207040209000204000608" 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/322 X-Sequence-Number: 43001 This is a multi-part message in MIME format. --------------010207040209000204000608 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Chetan Suttraway wrote: > > > On Fri, Mar 25, 2011 at 12:05 PM, Adarsh Sharma > > 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 > > --------------010207040209000204000608 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Chetan Suttraway wrote:


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


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



--------------010207040209000204000608--