From: Adarsh Sharma <adarsh.sharma@orkash.com>
To: Chetan Suttraway <chetan.suttraway@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: Why Index is not used
Date: Fri, 25 Mar 2011 15:23:24 +0530
Message-ID: <4D8C6614.1050007@orkash.com> (raw)
In-Reply-To: <AANLkTimoW2Gfe+mNQQ9bmCkJ772AoB+vBd9iWai=SdRh@mail.gmail.com>
References: <4D8C37CA.3090601@orkash.com>
<AANLkTinqGxRkVHuvcYLRtajrWhFWpDDjpTHuNV9_at9A@mail.gmail.com>
<4D8C3FAB.7070001@orkash.com>
<AANLkTin7m2cjph=ijKXWZR8_EwKq9Zg1C5Sh73SqRqWX@mail.gmail.com>
<4D8C5881.4090806@orkash.com>
<AANLkTimoW2Gfe+mNQQ9bmCkJ772AoB+vBd9iWai=SdRh@mail.gmail.com>
Chetan Suttraway wrote:
>
>
> On Fri, Mar 25, 2011 at 2:25 PM, Adarsh Sharma
> <adarsh.sharma@orkash.com <mailto: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 <http://www.enterprisedb.com/;, The Enterprise PostgreSQL
> <http://www.enterprisedb.com/; company.
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adarsh.sharma@orkash.com, chetan.suttraway@enterprisedb.com, tv@fuzzy.cz
Subject: Re: Why Index is not used
In-Reply-To: <4D8C6614.1050007@orkash.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox