Wacky query plan, why?

From: "Maksim Likharev" <mlikharev(at)aurigin(dot)com>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: Wacky query plan, why?
Date: 2003-07-25 22:28:44
Message-ID: 56510AAEF435D240958D1CE8C6B1770A016D2DD0@mailc03.aurigin.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2003-07-25 23:12:09 Re: Wacky query plan, why?
Previous Message Karsten Hilbert 2003-07-25 22:06:37 Re: Using YY-MM-DD date input