Message-ID: <513600A4.7090602@ulb.ac.be>
Date: Tue, 05 Mar 2013 15:26:44 +0100
From: Julien Cigar <jcigar@ulb.ac.be>
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64;
	rv:17.0) Gecko/20130211 Thunderbird/17.0.2
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Optimize SELECT * from table WHERE foreign_key_id IN
	(key1,key2,key3,key4...)
References: <DC8BDA57-E91E-4E82-A83C-5217D77B02FF@autouncle.com>
In-Reply-To: <DC8BDA57-E91E-4E82-A83C-5217D77B02FF@autouncle.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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.


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