Re: UPDATE with subquery too slow - Mailing list pgsql-performance

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