Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1727E1337B43 for ; Fri, 25 Mar 2011 03:47:07 -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 14969-04 for ; Fri, 25 Mar 2011 06:46:48 +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 6B8AB1337990 for ; Fri, 25 Mar 2011 03:46:48 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp8.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id 063CC8158; Fri, 25 Mar 2011 02:46:48 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp8.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 6860680F8; Fri, 25 Mar 2011 02:46:46 -0400 (EDT) Message-ID: <4D8C3B86.3040208@orkash.com> Date: Fri, 25 Mar 2011 12:21:50 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: Andreas Kretschmer CC: pgsql-performance@postgresql.org Subject: Re: Why Index is not used References: <4D8C37CA.3090601@orkash.com> <20110325064427.GA18517@tux> In-Reply-To: <20110325064427.GA18517@tux> Content-Type: multipart/alternative; boundary="------------040101010602060507080207" 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/320 X-Sequence-Number: 42999 This is a multi-part message in MIME format. --------------040101010602060507080207 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 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 > --------------040101010602060507080207 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
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.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.
    

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

--------------040101010602060507080207--