Re: regarding IN clause - Mailing list pgsql-general

From Dann Corbit
Subject Re: regarding IN clause
Date
Msg-id D425483C2C5C9F49B5B7A41F89441547055BAD@postal.corporate.connx.com
Whole thread Raw
In response to regarding IN clause  ("Surabhi Ahuja " <surabhi.ahuja@iiitb.ac.in>)
List pgsql-general

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

pgsql-general by date:

Previous
From: "Surabhi Ahuja "
Date:
Subject: regarding IN clause
Next
From: Greg Stark
Date:
Subject: Re: regarding IN clause