From: Josh Berkus <josh@agliodbs.com>
To: Jeff Barrett <jbarrett@familynetwork.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: select is fast, update based on same where clause is slow
Date: Fri, 21 Sep 2001 09:21:45 -0700
Message-ID: <web-123339@davinci.ethosmedia.com> (raw)
In-Reply-To: <9ofjt8$177a$1@news.tht.net>
Jeff,
I think that you're running into a syntactical problem here:
> 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;
In the query above, there is no linkage between the instance of
sessions2 (sessions2) you are updating and the instance you are limiting
(s). As a result, you are running an update on 74,000^2 rows (about 55
trillion), and if the query ever completed you would find that
sinceinception would be set to 0 for all rows in sessions2. Your query
should read:
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;
It's a peculiarity of Update queries that the name of the updated table
cannot be aliased anywhere in the query. To add to the confusion, in MS
Transact SQL, you are expected to repeat the name of the updated table
in the FROM clause, while in Postgres such repetition is prohibited.
This is mainly due to the fact that UPDATE ... FROM is not well-defined
in the SQL 92 standard.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
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: josh@agliodbs.com, jbarrett@familynetwork.com
Subject: Re: select is fast, update based on same where clause is slow
In-Reply-To: <web-123339@davinci.ethosmedia.com>
* 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