From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-performance@postgresql.org
Subject: Re: Why Index is not used
Date: Fri, 25 Mar 2011 08:24:33 +0100
Message-ID: <imhfvh$qji$1@dough.gmane.org> (raw)
In-Reply-To: <4D8C3B86.3040208@orkash.com>
References: <4D8C37CA.3090601@orkash.com>
<20110325064427.GA18517@tux>
<4D8C3B86.3040208@orkash.com>
Adarsh Sharma, 25.03.2011 07:51:
>
> 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)
>
>
How many rows are there in clause2 in total?
31853084 rows are returned from that table which sounds like the whole table qualifies for the join condition.
Regards
Thomas
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: spam_eater@gmx.net
Subject: Re: Why Index is not used
In-Reply-To: <imhfvh$qji$1@dough.gmane.org>
* 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