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

Re: UPDATE with subquery too slow

From: "Leeuw van der, Tim" <tim(dot)leeuwvander(at)nl(dot)unisys(dot)com>
To: "Eric Jain" <Eric(dot)Jain(at)isb-sib(dot)ch>,"pgsql-performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: UPDATE with subquery too slow
Date: 2004-02-17 12:49:29
Message-ID: DD0DC14935B1D211981A00105A1B28DB0A7B4D63@NL-ASD-EXCH-1 (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

This is not going to answer your question of course but did you already try to do this in 2 steps?

You said that the subquery itself doesn't take very long, so perhaps you can create a temporary table based on the subquery, then in the update do a join with the temporary table?

This might not be desirable in the end, but it might be useful just to check the performance of it.

And - isn't it an option to upgrade to 7.4.1 instead?


regards,

--Tim

THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers. 



-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org]On Behalf Of Eric Jain
Sent: dinsdag 17 februari 2004 13:38
To: pgsql-performance
Subject: [PERFORM] UPDATE with subquery too slow


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):

  UPDATE requests
  SET session = NULL
  WHERE session IN
  (
    SELECT session
    FROM requests
    GROUP BY session
    HAVING COUNT(*) = 1
  );

[...]


pgsql-performance by date

Next:From: Konstantin TokarDate: 2004-02-17 12:54:54
Subject: Tables on multiple disk drives
Previous:From: Eric JainDate: 2004-02-17 12:38:19
Subject: UPDATE with subquery too slow

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