Re: slow down on UPDATE using IN statements

From: <mallah(at)trade-india(dot)com>
To: <gandalf_mp(at)yahoo(dot)com(dot)br>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: slow down on UPDATE using IN statements
Date: 2003-11-03 15:34:44
Message-ID: 1474.203.145.130.142.1067873684.squirrel@mail.trade-india.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Is the query below any different from what you are trying to accomplish.

UPDATE requisicao SET conclusao='3' WHERE now()-data>'15 days' ;

if you want to use the exact query more efficiently you may rewrite
using EXISTS as

UPDATE requisicao AS a SET conclusao='3' WHERE
EXISTS (select * from requisicao where reg=a.reg and
now()-data>'15 days' ) ;

Best Regards
Mallah.

> Hi there,
>
> I was in troubles with a UPDATE+IN statement:
>
> The following command use to take about 5 minutes to
> be done:
>
> UPDATE requisicao SET conclusao='3' WHERE reg IN
> (SELECT reg FROM requisicao WHERE now()-data>'15
> days');
>
> The table 'requisicao' has only about 400 lines (!!).
>
> If I change it to:
>
> UPDATE requisicao SET conclusao='3' WHERE reg IN (12,
> 45, 87, 98, 129, 350, 389);
>
> I have detected that the major problem isn't in the
> amount of lines changed, but in the subselect.
>
> How can I solve/optimize it? I would like to use the
> IN, but in the last case I would make a software
> change.
>
> Thanks in advance and
> Best regards,
>
> Marcelo Pereira
> Brazil
>
> Yahoo! Mail - o melhor webmail do Brasil
> http://mail.yahoo.com.br
>
> ---------------------------(end of broadcast)--------------------------- TIP 5: Have you
> checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stuart Johnston 2003-11-03 15:36:16 Re: Internet based database
Previous Message Greg Stark 2003-11-03 15:28:57 Re: insert data of composite type