Re: UPDATE with JOIN not using index

On 16/03/10 13:57, Arnaud Lesauvage wrote:
> 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))"

OK - we have a merge join in the first case where it joins the
pre-sorted output of both tables.

In the second case it queries the index once for each row in "cellules".

Now look at the costs. The first one is around 704,000 and the second
one is 5,000,000 - about 6 times as much. That's why it's not using the
index, because it thinks it will be more expensive.

If it's not really more expensive that suggests your configuration
values aren't very close to reality.

The first query should run faster if it has more work_mem available too.
At the moment, it's probably going back and fore doing an on-disk sort.

--
Richard Huxton
Archonet Ltd