Re: UPDATE with JOIN not using index

Le 16/03/2010 15:25, Richard Huxton a écrit :
> 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.

Indeed !
I admit that I had not tested the second query, I just thought that the
first one took way too long to execute.

I will try increasing work_mem, but it is already set at 16MB which I
found is quite high.

Thanks a lot for clarifying that !

Regards
Arnaud