Re: UPDATE with JOIN not using index

Arnaud Lesauvage <arnaud(dot)listes(at)codata(dot)eu> writes:
> 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))"

Please notice that the planner thinks the second plan is much more
expensive than the first. I think you will find that it's right.
Using an index is not always the best way to do a query.

regards, tom lane