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

That did the trick. Thank you for the quick detailed answer. It runs in
about a minute now.

Jeff Barrett

"Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote in message
news:Pine.BSF.4.21.0109210830550.88512-100000@megazone23.bigpanda.com...
> 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 majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly