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. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
