Re: Wacky query plan, why?

On Fri, 25 Jul 2003, Maksim Likharev wrote:

> Yes I see, no words about FROM cause in SQL92/99, but
> it seems like Postgres supports that.

Yeah, it's an extension to the standard behavior.

> So bottom line:
> insted of
>
> update prod.t_results set fan = a.fullname, fin=i.fullname
> from prod.t_results r inner join prod.t_agn a
> on r.faid = a.aid
> inner join prod.t_inv i
> on r.fiid = i.iid
> where r.docid = 22544257;
>
> I should use
>
> update prod.t_results set fan = a.fullname, fin=i.fullname
> from prod.t_results r inner join prod.t_agn a
> on r.faid = a.aid
> inner join prod.t_inv i
> on r.fiid = i.iid
> where r.docid = 22544257 and prod.t_results.docid =
> r.docid;

Why not just something like:

update prod.t_results set fan=a.fullname, fin=i.fullname
from prod.t_agn a, prod.t_inv i
where prod.t_results.faid = a.aid and
prod.t_results.fiid = i.iid and
prod.t_results.docid = 22544257;

I don't see much need to join a second copy of t_results into the query.

> BTW, what it's doing in a first place, looks up tuples generated in FROM
> clause
> against prod.t_results table?

AFAIK it's similar in behavior to if you'd written a select of the form

select * from
t_results, t_results r inner join t_agn a on r.faid=a.aid inner join
t_inv i on r.fiid = i.iid
where r.docid = 22544257;

You've got two copies of t_results being joined in the result and the
first is not constrained in any way so you get an "output" row for each
row of the inner join set for each row in t_results.