Re: Performance Killer 'IN' ?

Marko Kreen wrote:
> On 3/31/06, Kai Hessing <kai(dot)hessing(at)hobsons(dot)de> wrote:
>> The second one (UPDATE xyz WHERE id IN (xyz1, xyz2, ....) AND
>> status>-1;) returns:
>> ------------------
>> Seq Scan on phon (cost=0.00..1573304.58 rows=105931 width=148) (actual
>> time=369563.565..369563.565 rows=0 loops=1)
>
> Just a shot in the dark: does the plan stay the same,
> when you remove the ' AND status > -1' ?

No difference: I skipped the 'AND status > -1' and have the following
results...

Using 2000x 'UPDATE phon SET status=-6 WHERE ' returns each time:
----------------------------
Index Scan using phon_phon_idx on phon (cost=0.00..5179.80 rows=1587
width=148) (actual time=31.452..31.470 rows=1 loops=1)

Index Cond: ((phon)::text = 'wink4103(at)uni-trier(dot)de'::text)

Total runtime:3.414 ms
----------------------------
(Total runtime for all 2000 Updates: 23335.393 ms

Using the 'UPDATE xyz WHERE id IN ('xyz1', 'xyz2', other 2000
values.....)' returns:
----------------------------
Seq Scan on phon (cost=0.00..1564960.67 rows=317227 width=148) (actual
time=68.315..365621.761 rows=2522 loops=1)

Filter: (((phon)::text = '.....

Total runtime: 393182.745 ms
----------------------------