From: Kevin Brown <kevin@sysexperts.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: UPDATE with subquery too slow
Date: Tue, 17 Feb 2004 22:52:26 -0800
Message-ID: <20040218065225.GB3090@filer> (raw)
In-Reply-To: <00ed01c3f552$ec054e80$c300000a@caliente>
References: <00ed01c3f552$ec054e80$c300000a@caliente>
Eric Jain wrote:
> I can't get the following statement to complete with reasonable time.
> I've had it running for over ten hours without getting anywhere. I
> suspect (hope) there may be a better way to accomplish what I'm trying
> to do (set fields containing unique values to null):
[...]
> Using EXISTS rather than IN (I'm using 7.4-RC2, not sure if IN queries
> were already improved in this release):
>
> UPDATE requests
> SET session = NULL
> WHERE NOT EXISTS
> (
> SELECT r.session
> FROM requests r
> WHERE
> r.session = session
> AND NOT r.id = id
> );
I suppose you could try:
UPDATE requests
SET session = NULL
WHERE EXISTS
(
SELECT r.session
FROM requests r
WHERE
r.session = session
GROUP BY r.session
HAVING count(*) = 1
);
but I don't know that you'll get much different results than your
version.
--
Kevin Brown kevin@sysexperts.com
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-performance@postgresql.org
Cc: kevin@sysexperts.com
Subject: Re: UPDATE with subquery too slow
In-Reply-To: <20040218065225.GB3090@filer>
* 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