On Wed, 13 Dec 2006 13:23:41 +0100, Arnaud Lesauvage <[EMAIL PROTECTED]>
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
