Re: Performance Killer 'IN' ?

Kai Hessing <kai(dot)hessing(at)hobsons(dot)de> writes:
> Yes... The 0 rows are there because I did the command before. Now I
> resetted the test database to a previous state and dopped the 'AND
> status>-1' in the SQL-syntax:

> 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 = '.....

Well, here's our problem it would seem: the planner is estimating the IN
clause to match 317227 rows, rather than the actual 2522. That's
naturally going to bias it against choosing an indexscan. You need to
get that estimate closer before there's going to be much chance of
choosing the right plan.

> What do you mean with larger statistics target?

See ALTER TABLE SET STATISTICS, or just change default_statistics_target
and re-ANALYZE.

regards, tom lane