Re: Performance Killer 'IN' ?

Joshua D. Drake wrote:
> Kai Hessing wrote:
>> 1.) 21.5 seconds
>> 2.) 363.7 seconds
>>
>> But it is still a significant difference.
>
> Can you provide an explain analyze of each query?

There isn't an explain analyze of the first variant, because there are
just 2000 SQL-Updates. The explain analyze for just one of the commands
(UPDATE xyz SET status=-6 WHERE phon='xyz1' AND status>-1;) is:
------------------
Index Scan using phon_phon_idx on phon (cost=0.00..5193.83 rows=530
width=148) (actual time=0.146..0.146 rows=0 loops=1)

Index Cond: ((phon)::text = 'xyz'::text)

Filter: (status > -1)

Total runtime: 0.387 ms

1 Datensätze (means data sets)

Laufzeit gesamt: 16.682 ms (means running time)
------------------

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)

Filter: ((((phon)::text = 'xyz1) OR ((phon)::text = 'xyz2'::text) OR
((phon)::text = 'xyz3'::text) OR ((phon)::text = 'xyz4'::text) OR ...
[all the 2000 entries from the IN clause]

Total runtime: 369566.954 ms

667 Datensätze (means data sets)

Laufzeit gesamt: 370,179.246 ms (means running time)
------------------

Hope, that helps. Have a nice weekend. Being back to work on monday ;)

*greets*
Kai