Re: regarding IN clause

Probably, the sequential scan was faster.

There is a big cost to jumping all over the place, loading both the
index pages and the data pages.

Typically, it is about 10% of the data volume for a file, but I don't
know what the metric is for PostgreSQL.

If you want to force the index behavior, you might try finding out how
many items you can put into a IN list, and then repeating it with UNION.

E.g.:

SELECT * FROM table_name WHERE id IN (2,4,6,8)

UNION

SELECT * FROM table_name WHERE id IN (1,2,3,4)

UNION

SELECT * FROM table_name WHERE id IN (9,121,13,14)

Etc.

I am guessing that the sequential scan is faster.

You could also try OR lists, which accomplish the same thing.

You might also select the ID values into a temporary table and then do a
join. I am guessing that the join query would work pretty well.

Give it a try and see, and then tell us what you saw.

________________________________

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Surabhi Ahuja
Sent: Tuesday, May 17, 2005 10:13 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] regarding IN clause

I have a table where the primary key "id" is a serial key.

now i have a query to this table where in, i have to get information
related to a set of ids.
This set contains around 130 ids.

I was thinking that i can form a select statement of the form

select * from table where id in(4,8,9,12, and so on....);
i was testing this query on the table with 3000 rows only...
and when i did
explain analyze select * from table where id in(4,8,9,12, and so
on....);..
it said that it was doing a sequential scan.

why is it not searching the indexes(index scan)? and how can i make such
a query much faster?