From: Julien Cigar <jcigar@ulb.ac.be>
To: pgsql-performance@postgresql.org
Subject: Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)
Date: Wed, 06 Mar 2013 13:33:05 +0100
Message-ID: <51373781.3070706@ulb.ac.be> (raw)
In-Reply-To: <653B0909-A122-44B5-B2A7-A8A524963AB6@autouncle.com>
References: <DC8BDA57-E91E-4E82-A83C-5217D77B02FF@autouncle.com>
<513600A4.7090602@ulb.ac.be>
<653B0909-A122-44B5-B2A7-A8A524963AB6@autouncle.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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 <jcigar@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 instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jcigar@ulb.ac.be
Subject: Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)
In-Reply-To: <51373781.3070706@ulb.ac.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox