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))"