Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 7E72A1337B96 for ; Fri, 25 Mar 2011 06:48:38 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 68332-02 for ; Fri, 25 Mar 2011 09:48:27 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp190.dfw.emailsrvr.com (smtp190.dfw.emailsrvr.com [67.192.241.190]) by mail.postgresql.org (Postfix) with ESMTP id D3F601337B49 for ; Fri, 25 Mar 2011 06:48:25 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp9.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id 5BEFB3C0365; Fri, 25 Mar 2011 05:48:25 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp9.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id E2FFE3C033B; Fri, 25 Mar 2011 05:48:22 -0400 (EDT) Message-ID: <4D8C6614.1050007@orkash.com> Date: Fri, 25 Mar 2011 15:23:24 +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, tv@fuzzy.cz Subject: Re: Why Index is not used References: <4D8C37CA.3090601@orkash.com> <4D8C3FAB.7070001@orkash.com> <4D8C5881.4090806@orkash.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------060109070604050104030803" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201103/330 X-Sequence-Number: 43009 This is a multi-part message in MIME format. --------------060109070604050104030803 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Chetan Suttraway wrote: > > > On Fri, Mar 25, 2011 at 2:25 PM, Adarsh Sharma > > wrote: > >> >> 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 >> >> > > The ideas is to have maximum filtering occuring on leading column of > index. > the first plan with only the predicates on clause_id is returning > 379772050555842 rows whereas > in the second plan with doc_id predicates is returning only 20954686217. > > So maybe you should consider re-ordering of the index on clause2. > > I am thinking that you created the indexes by looking at the columns > used in the where clause. > But its not always helpful to create indexes based on exact order of > predicates specified in query. > Instead the idea should be consider the predicate which is going to do > filter out the results. > Likewise we should consider all possible uses of index columns across > all queries and then decide on the > order of columns for the composite index to be created. > > Whats your take on this? I am sorry but I am not able to got your points completely. My table definitions are as : *Clause2 Table : *CREATE TABLE clause2 ( id bigint NOT NULL DEFAULT nextval('clause_id_seq'::regclass), source_id integer, sentence_id integer, clause_id integer, tense character varying(30), clause text, CONSTRAINT pk_clause_demo_id PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); CREATE INDEX idx_clause ON clause2 USING btree (clause_id, source_id, sentence_id); *svo2 table :*-- CREATE TABLE svo2 ( svo_id bigint NOT NULL DEFAULT nextval('svo_svo_id_seq'::regclass), doc_id integer, sentence_id integer, clause_id integer, negation integer, subject character varying(3000), verb character varying(3000), "object" character varying(3000), preposition character varying(3000), subject_type character varying(3000), object_type character varying(3000), subject_attribute character varying(3000), object_attribute character varying(3000), verb_attribute character varying(3000), subject_concept character varying(100), object_concept character varying(100), subject_sense character varying(100), object_sense character varying(100), subject_chain character varying(5000), object_chain character varying(5000), sub_type_id integer, obj_type_id integer, CONSTRAINT pk_svo_demo_id PRIMARY KEY (svo_id) ) WITH ( OIDS=FALSE ); CREATE INDEX idx_svo2 ON svo2 USING btree (clause_id, doc_id, sentence_id); Please correct me if I m wrong. I need to change the order of columns in indexes according to the filter conditions but in this query . After making set enable_mergejoin = false and random_page_cost =2.0 The problem remains the same. What is your recommendations for the new index so that the query runs even faster. I can change my original query to : explain analyze select c.clause,s.doc_id,s.subject,s.verb,s.object,s.subject_type,s.object_type from clause2 c, svo2 s where c.clause_id=s.clause_id and s.doc_id=c.source_id and c.sentence_id=s.sentence_id ; And the output is : QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=0.00..128419720.68 rows=167324179 width=105) (actual time=11.179..285708.966 rows=30473117 loops=1) -> Seq Scan on svo2 s (cost=0.00..697537.60 rows=27471560 width=53) (actual time=0.013..19554.222 rows=27471560 loops=1) -> Index Scan using idx_clause on clause2 c (cost=0.00..4.63 rows=1 width=72) (actual time=0.006..0.007 rows=1 loops=27471560) Index Cond: ((c.clause_id = s.clause_id) AND (c.source_id = s.doc_id) AND (c.sentence_id = s.sentence_id)) Total runtime: 301599.274 ms Thanks & best Regards, Adarsh Sharma > Regards, > Chetan Suttraway > EnterpriseDB , The Enterprise PostgreSQL > company. > > > --------------060109070604050104030803 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Chetan Suttraway wrote:


On Fri, Mar 25, 2011 at 2:25 PM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:

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



The ideas is to have maximum filtering occuring on leading column of index.
the first plan with only the predicates on clause_id is returning 379772050555842 rows whereas
in the second plan with doc_id predicates is returning only 20954686217.

So maybe you should consider re-ordering of the index on clause2.

I am thinking that you created the indexes by looking at the columns used in the where clause.
But its not always helpful to create  indexes based on exact order of predicates specified in query.
Instead the idea should be consider the predicate which is going to do filter out the results.
Likewise we should consider all possible uses of index columns across all queries and then decide on the
order of columns for the composite index to be created.

Whats your take on this?

I am sorry but I am not able to got your points completely.

My table definitions are as :

Clause2 Table :

CREATE TABLE clause2
(
  id bigint NOT NULL DEFAULT nextval('clause_id_seq'::regclass),
  source_id integer,
  sentence_id integer,
  clause_id integer,
  tense character varying(30),
  clause text,
  CONSTRAINT pk_clause_demo_id PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
CREATE INDEX idx_clause  ON clause2  USING btree  (clause_id, source_id, sentence_id);

svo2 table :--

CREATE TABLE svo2
(
  svo_id bigint NOT NULL DEFAULT nextval('svo_svo_id_seq'::regclass),
  doc_id integer,
  sentence_id integer,
  clause_id integer,
  negation integer,
  subject character varying(3000),
  verb character varying(3000),
  "object" character varying(3000),
  preposition character varying(3000),
  subject_type character varying(3000),
  object_type character varying(3000),
  subject_attribute character varying(3000),
  object_attribute character varying(3000),
  verb_attribute character varying(3000),
  subject_concept character varying(100),
  object_concept character varying(100),
  subject_sense character varying(100),
  object_sense character varying(100),
  subject_chain character varying(5000),
  object_chain character varying(5000),
  sub_type_id integer,
  obj_type_id integer,
  CONSTRAINT pk_svo_demo_id PRIMARY KEY (svo_id)
)
WITH (
  OIDS=FALSE
);
CREATE INDEX idx_svo2  ON svo2  USING btree  (clause_id, doc_id, sentence_id);

Please correct me if I m wrong.

I need to change the order of columns in indexes according to the filter conditions but in this query .

After making 
set enable_mergejoin = false
and random_page_cost =2.0

The problem remains the same.
What is your recommendations for the new index so that the query runs even faster.


I can change my original query to :

explain analyze select c.clause,s.doc_id,s.subject,s.verb,s.object,s.subject_type,s.object_type from clause2 c, svo2 s where c.clause_id=s.clause_id and s.doc_id=c.source_id and c.sentence_id=s.sentence_id ;

And the output is :

                                                             QUERY PLAN                                                            
------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..128419720.68 rows=167324179 width=105) (actual time=11.179..285708.966 rows=30473117 loops=1)
   ->  Seq Scan on svo2 s  (cost=0.00..697537.60 rows=27471560 width=53) (actual time=0.013..19554.222 rows=27471560 loops=1)
   ->  Index Scan using idx_clause on clause2 c  (cost=0.00..4.63 rows=1 width=72) (actual time=0.006..0.007 rows=1 loops=27471560)
         Index Cond: ((c.clause_id = s.clause_id) AND (c.source_id = s.doc_id) AND (c.sentence_id = s.sentence_id))
 Total runtime: 301599.274 ms


Thanks & best Regards,
Adarsh Sharma


Regards,
Chetan Suttraway
EnterpriseDB, The Enterprise PostgreSQL company.




--------------060109070604050104030803--