On 03/06/2013 00:51, Niels Kristian Schjødt wrote:
Hi, thanks for answering. See comments inline.

Den 05/03/2013 kl. 15.26 skrev Julien Cigar <jci...@ulb.ac.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…

could you paste the full query, an explain analyze of it, and some details about your config (how much ram ? what's your: shared_buffers, effective_cache_size, cpu_tuple_cost, work_mem, ...) ?

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




--
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

Reply via email to