pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (4+ messages)  latest in thread

Message-ID: <9ofjt8$177a$1@news.tht.net>
Permalink:  ../9ofjt8$177a$1@news.tht.net/
Also on:    postgresql.org/message-id/9ofjt8$177a$1@news.tht.net

 · 

reply

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