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

Hi, thanks for answering. See comments inline.

Den 05/03/2013 kl. 15.26 skrev Julien Cigar <jcigar(at)ulb(dot)ac(dot)be>:

> 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.
I need both
> 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 …
>
I tried this now, and it doesn't seem to do a very big difference unfortunately…

>> 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
>
Sorry, it's 3323ms!

>> Can I do anything to optimize that query or maybe the index or something?
>
> your index is already used

Okay this leaves me with - "get better hardware" or?

>
>> The table has 16.000.000 rows
>>
>
>
> --
> No trees were killed in the creation of this message.
> However, many electrons were terribly inconvenienced.
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance