Re: select is fast, update based on same where clause is slow

On Fri, 21 Sep 2001, Jeff Barrett wrote:

> I have a select statement that returns a set of 74,000+ results back in
> under a minute as follows:
>
> select s.sessid, s.membid, s.datetime
> from sessions2 s, (select min(datetime) as datetime, membid
> from sessions2
> where membid is not null
> group by membid) as minsess
> where s.membid = minsess.membid
> and s.datetime = minsess.datetime;
>
> The final cost from explain for the above select is 22199.15 ... 24318.40
> with rows = 5 and width = 28.
>
> Then I issue an update as follows (to update those 74,000+ rows):
> update sessions2 set sinceinception = 0
> from sessions2 s, (select min(datetime) as datetime, membid from sessions2
> group by membid) as mindate
> where s.membid = mindate.membid
> and s.datetime = mindate.datetime;
>
> The final cost from explain for the above update is 31112.11...98869.91 with
> rows = 2013209 and width=87.
>
> This update statement has been left running over night and does not
> complete. The ram usage on the machine is at about 3/4 capacity (800mb)
> during this time and CPU usage is near 100%. The machine has the -F option
> set and memory segments of 200mb and is running 7.1.2.
>
> What could be causing this update statement to not complete?
> Why are the costs so different since it seems to me that besides the cost of
> the update they are the same query?

I thought that the updated table is always in your from list (implicitly),
so you'd want:
update sessions2 set sinceinception = 0
from (select min(datetime) as datetime, membid from sessions2 group by
membid) as mindate
where sessions2.membid=mindate.membid and
sessions2.datetime=mindate.datetime;

I think your query would be joining the s/mindate results against
sessions2.