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)
