From: Jeff Barrett <jbarrett@familynetwork.com>
To: pgsql-sql@postgresql.org
Subject: select is fast, update based on same where clause is slow
Date: Fri, 21 Sep 2001 10:44:09 -0400
Message-ID: <9ofjt8$177a$1@news.tht.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jbarrett@familynetwork.com
Subject: Re: select is fast, update based on same where clause is slow
In-Reply-To: <9ofjt8$177a$1@news.tht.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox