Re: Slow update with simple query

On Wed, 13 Dec 2006 13:23:41 +0100, Arnaud Lesauvage <thewild(at)freesurf(dot)fr>
wrote:

> Jens Schipkowski a écrit :
>> the problem is a combination of bad formed SQL and maybe missing
>> indexes.
>> try this:
>> UPDATE t1
>> SET booleanfield = foo.bar
>> FROM (SELECT uid,(field IN ('some','other') AND field2 = 'Y') AS bar
>> FROM t2) AS foo
>> WHERE t1.uid=foo.uid;
>
>
> Hi Jens,
> Why is this query better than the other one ? Because it runs the
> "(field IN ('some','other') AND field2 = 'Y')" once and then executes
> the join with the resulting set ?
True. The Subselect in FROM clause will be executed once and will be
joined using the condition at where clause. So your condition at t2 is not
executed for each row in t1(2mio records) but for each row in t2(1k
records). And the boolean value is already set during update.

regards,
Jens

>
>> and index t1.uid, t2.uid, t2.field, t2.field2
>
> t1.field can only take 3 or 4 values (don't remember exactly), and
> field2 only 2 ('Y' or 'N'). So this fields have a very low cardinality.
> Won't the planner chose to do a table scan in such a case ?
>
> Thanks for your advices !
>
> --
> Arnaud

--
**
APUS Software GmbH