Wacky query plan, why?

Поиск
Список
Период
Сортировка
От Maksim Likharev
Тема Wacky query plan, why?
Дата
Msg-id 56510AAEF435D240958D1CE8C6B1770A016D2DD0@mailc03.aurigin.com
обсуждение исходный текст
Ответы Re: Wacky query plan, why?  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список pgsql-general
I have 2 queries, one is never returns, like explain shows 677195846.00
cost
    and another doing the same job works ( cost 6072.00 )

I do not understand one thing, why query number one, generates so
unbelievably
screwed up plan?

why it does not use index?

query # 1:
------------------------------------------------------------------------
------
explain update prod.t_results set expdate=e.termdate from
    work.termdate e, prod.t_results r where e.docid=r.docid;
        or  update prod.t_results set expdate=e.termdate from
work.termdate e
            INNER JOIN prod.t_results r on
(e.docid=r.docid);

 Nested Loop  (cost=0.00..677195846.00 rows=19269540000 width=138)
   ->  Nested Loop  (cost=0.00..3046.00 rows=1000 width=16)
         ->  Seq Scan on termdate e  (cost=0.00..20.00 rows=1000
width=12)
         ->  Index Scan using t_resultsid on t_results r
(cost=0.00..3.01 rows=1 width=4)
               Index Cond: ("outer".docid = r.docid)
   ->  Seq Scan on t_results  (cost=0.00..484497.40 rows=19269540
width=122)

query # 2:
------------------------------------------------------------------------
------
explain update prod.t_results set expdate=e.termdate from
        (select r.docid, t.termdate from work.termdate t,
prod.t_results r
            where t.docid=r.docid) as e where
prod.t_results.docid=e.docid;

 Nested Loop  (cost=0.00..6072.00 rows=1000 width=138)
   ->  Nested Loop  (cost=0.00..3046.00 rows=1000 width=16)
         ->  Seq Scan on termdate t  (cost=0.00..20.00 rows=1000
width=12)
         ->  Index Scan using t_resultsid on t_results r
(cost=0.00..3.01 rows=1 width=4)
               Index Cond: ("outer".docid = r.docid)
   ->  Index Scan using t_resultsid on t_results  (cost=0.00..3.01
rows=1 width=122)
         Index Cond: (t_results.docid = "outer".docid)

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

Предыдущее
От: Karsten Hilbert
Дата:
Сообщение: Re: Using YY-MM-DD date input
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: Wacky query plan, why?