On 03/05/2013 15:00, Niels Kristian Schjødt wrote:
> 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.
why do you want a join here ? if you don't need any "cars" data there is
no need to JOIN that table.
Now a select ... from ... where id in (id1, id2, ..., idn) isn't very
scalable.
Instead of passing id1, id2, ..., idn you'be better pass the condition
and do a where id in (select ... ), or where exists (select 1 ... where
...), or a join, or ...
> 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
3ms isn't slow
> Can I do anything to optimize that query or maybe the index or something?
your index is already used
> The table has 16.000.000 rows
>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.