pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Adarsh Sharma <adarsh.sharma@orkash.com>
To: Chetan Suttraway <chetan.suttraway@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why Index is not used
Date: Fri, 25 Mar 2011 14:25:29 +0530
Message-ID: <4D8C5881.4090806@orkash.com> (raw )
In-Reply-To: <AANLkTin7m2cjph=ijKXWZR8_EwKq9Zg1C5Sh73SqRqWX@mail.gmail.com >
References: <4D8C37CA.3090601@orkash.com >
<AANLkTinqGxRkVHuvcYLRtajrWhFWpDDjpTHuNV9_at9A@mail.gmail.com >
<4D8C3FAB.7070001@orkash.com >
<AANLkTin7m2cjph=ijKXWZR8_EwKq9Zg1C5Sh73SqRqWX@mail.gmail.com >
>
>
>
> 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
>
>
view thread (14+ messages) latest in thread
Message-ID: <4D8C5881.4090806@orkash.com>
Permalink: ../4D8C5881.4090806@orkash.com/
Also on: postgresql.org/message-id/4D8C5881.4090806@orkash.com
copy link · copy postgr.es
reply 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
Subject: Re: Why Index is not used
In-Reply-To: <4D8C5881.4090806@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