Re: UPDATE with JOIN not using index

Le 16/03/2010 14:50, Richard Huxton a écrit :
> On 16/03/10 13:05, Arnaud Lesauvage wrote:
>>  PostgreSQL 8.4 here.
>>  I have a simple update query that looks like this :
>>
>>  UPDATE t1
>>  SET col = t2.col
>>  FROM t2
>>  WHERE t1.key1 = t2.key1 AND t1.key2 = t2.key2;
>>
>>  There is an index on (key1,key2) on the joined table (t2).
>>  This query does not use the index.
>
> What does it do, then? The output of EXPLAIN would be a start if EXPLAIN
> ANALYSE is too expensive.
 > Oh - and how many rows will this actually update?

I launched the EXPLAIN ANALYZE as soon as I posted the message, but it
is not over yet. You are right that I should have posted the explain.
Approximatively 500.000 rows will be updated.


First query :

"Merge Join  (cost=699826.38..704333.80 rows=13548 width=836)"
"  Merge Cond: (((c.rue)::text = (r.rue)::text) AND ((c.codesite)::text
= (r.codesite)::text))"
"  ->  Sort  (cost=696320.21..697701.07 rows=552343 width=823)"
"        Sort Key: c.rue, c.codesite"
"        ->  Seq Scan on cellules c  (cost=0.00..443520.43 rows=552343
width=823)"
"  ->  Sort  (cost=3504.88..3596.96 rows=36833 width=43)"
"        Sort Key: r.rue, r.codesite"
"        ->  Seq Scan on rues r  (cost=0.00..711.33 rows=36833 width=43)"


Second query :

"Seq Scan on cellules c  (cost=0.00..5018080.39 rows=552343 width=823)"
"  SubPlan 1"
"    ->  Index Scan using idx_rues_ruecodesite on rues r
(cost=0.00..8.28 rows=1 width=13)"
"          Index Cond: (((rue)::text = ($1)::text) AND ((codesite)::text
= ($0)::text))"