Re: slow down on UPDATE using IN statements

In versions 7.3 and before, IN was a dog compared to the equivalent EXISTS
statement. But given your timings, how long has it been since you did a
VACUUM ANALYZE FULL on your database?

Or REINDEX on any of the indexes?

Hope this helps,

On Mon, Nov 03, 2003 at 10:52:44AM -0300, MaRcElO PeReIrA wrote:
> 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

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato