Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)

Niels Kristian Schjødt <nielskristian@xxxxxxxxxxxxx> · Tue, 5 Mar 2013 15:00:48 +0100

Hi,

I'm running a rails app, where I have a model called Car that has_many Images. Now when I tell rails to include those images, when querying say 50 cars, then it often decides to use a SELECT * from images WHERE car_id IN (id1,id2,id3,id4…) instead of doing a join. 

Now either way it uses the index I have on car_id:

Index Scan using car_id_ix on adverts  (cost=0.47..5665.34 rows=1224 width=234)
	Index Cond: (car_id = ANY ('{7097561,7253541,5159633,6674471,...}'::integer[]))

But it's slow, it's very slow. In this case it took 3,323ms

Can I do anything to optimize that query or maybe the index or something?

The table has 16.000.000 rows

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance