Thread: slow 'IN' clause

I have a slow sql:
SELECT * FROM mytable WHERE id IN (1,3,5,7,....3k here...);
mytable is about 10k rows.

if don't use the "IN" clause, it will cost 0,11 second, otherwise it
will cost 2.x second
I guess pg use linear search to deal with IN clause, is there any way
to let pg use other search method with IN clause? (ex.Binary Search or
hash Search)

On Sun, Apr 09, 2006 at 20:44:34 -0700, FavoYang@gmail.com wrote:
> I have a slow sql:
> SELECT * FROM mytable WHERE id IN (1,3,5,7,....3k here...);
> mytable is about 10k rows.
> 
> if don't use the "IN" clause, it will cost 0,11 second, otherwise it
> will cost 2.x second
> I guess pg use linear search to deal with IN clause, is there any way
> to let pg use other search method with IN clause? (ex.Binary Search or
> hash Search)

It would help if you told us what version of Postgres you are running
and showed EXPLAIN ANALYZE results for the two queries.

I have heard of people getting better speed by loading the constant list
into a temp table and then doing a join.

Some older versions of Postgres also had problems with large constant lists,
so if you are using an old version of Postgres upgrading might help.