Re: regarding IN clause

["Dann Corbit" <DCorbit@xxxxxxxxx>]

Title: 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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Surabhi Ahuja
Sent: Tuesday, May 17, 2005 10:13 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: 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?