Wacky query plan, why?
От | Maksim Likharev |
---|---|
Тема | Wacky query plan, why? |
Дата | |
Msg-id | 56510AAEF435D240958D1CE8C6B1770A016D2DD0@mailc03.aurigin.com обсуждение исходный текст |
Ответы |
Re: Wacky query plan, why?
|
Список | 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 по дате отправления: