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

From: "Jeff Barrett" <jbarrett(at)familynetwork(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: select is fast, update based on same where clause is slow
Date: 2001-09-21 14:44:09
Message-ID: 9ofjt8$177a$1@news.tht.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

Any help would be great!

Jeff Barrett

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2001-09-21 15:34:00 Re: select is fast, update based on same where clause is slow
Previous Message Ludek Finstrle 2001-09-21 13:32:00 LEFT OUTER JOIN problem