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

From Kevin Brown
Subject Re: UPDATE with subquery too slow
Date
Msg-id 20040218065225.GB3090@filer
Whole thread Raw
In response to UPDATE with subquery too slow  ("Eric Jain" <Eric.Jain@isb-sib.ch>)
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

pgsql-performance by date:

Previous
From: Christopher Kings-Lynne
Date:
Subject: Re: Slow response of PostgreSQL
Next
From: Jeff Boes
Date:
Subject: Optimizer difference using function index between 7.3 and 7.4