Re: Why Index is not used

Поиск
Список
Период
Сортировка
От Andreas Kretschmer
Тема Re: Why Index is not used
Дата
Msg-id 20110325064427.GA18517@tux
обсуждение исходный текст
Ответ на Why Index is not used  (Adarsh Sharma <adarsh.sharma@orkash.com>)
Ответы Re: Why Index is not used  (Adarsh Sharma <adarsh.sharma@orkash.com>)
Список pgsql-performance
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
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

В списке pgsql-performance по дате отправления:

Предыдущее
От: Adarsh Sharma
Дата:
Сообщение: Why Index is not used
Следующее
От: Adarsh Sharma
Дата:
Сообщение: Re: Why Index is not used