Skip site navigation (1) Skip section navigation (2)

Re: UPDATE with subquery too slow

From: Kevin Brown <kevin(at)sysexperts(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: UPDATE with subquery too slow
Date: 2004-02-18 06:52:26
Message-ID: 20040218065225.GB3090@filer (view raw or flat)
Thread:
Lists: 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(at)sysexperts(dot)com

In response to

pgsql-performance by date

Next:From: Jeff BoesDate: 2004-02-18 15:24:22
Subject: Optimizer difference using function index between 7.3 and 7.4
Previous:From: Christopher Kings-LynneDate: 2004-02-18 04:52:55
Subject: Re: Slow response of PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group