pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...)
6+ messages / 4 participants
[nested] [flat]
* Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...)
@ 2013-03-05 14:00 Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-03-05 14:26 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...) Julien Cigar <jcigar@ulb.ac.be>
0 siblings, 1 reply; 6+ messages in thread
From: Niels Kristian Schjødt @ 2013-03-05 14:00 UTC (permalink / raw)
To: pgsql-performance
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.
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
Can I do anything to optimize that query or maybe the index or something?
The table has 16.000.000 rows
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)
2013-03-05 14:00 Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...) Niels Kristian Schjødt <nielskristian@autouncle.com>
@ 2013-03-05 14:26 ` Julien Cigar <jcigar@ulb.ac.be>
2013-03-05 23:51 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...) Niels Kristian Schjødt <nielskristian@autouncle.com>
0 siblings, 1 reply; 6+ messages in thread
From: Julien Cigar @ 2013-03-05 14:26 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...)
2013-03-05 14:00 Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...) Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-03-05 14:26 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...) Julien Cigar <jcigar@ulb.ac.be>
@ 2013-03-05 23:51 ` Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-03-06 00:07 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...) Joshua D. Drake <jd@commandprompt.com>
2013-03-06 12:33 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...) Julien Cigar <jcigar@ulb.ac.be>
0 siblings, 2 replies; 6+ messages in thread
From: Niels Kristian Schjødt @ 2013-03-05 23:51 UTC (permalink / raw)
To: Julien Cigar <jcigar@ulb.ac.be>; +Cc: pgsql-performance
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…
>> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)
2013-03-05 14:00 Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...) Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-03-05 14:26 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...) Julien Cigar <jcigar@ulb.ac.be>
2013-03-05 23:51 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...) Niels Kristian Schjødt <nielskristian@autouncle.com>
@ 2013-03-06 00:07 ` Joshua D. Drake <jd@commandprompt.com>
2013-03-06 16:44 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...) Jeff Janes <jeff.janes@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Joshua D. Drake @ 2013-03-06 00:07 UTC (permalink / raw)
To: Niels Kristian Schjødt <nielskristian@autouncle.com>; +Cc: Julien Cigar <jcigar@ulb.ac.be>; pgsql-performance
On 03/05/2013 03:51 PM, Niels Kristian Schjødt wrote:
>> 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?
What does explain analyze say versus just explain.
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC
@cmdpromptinc - 509-416-6579
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...)
2013-03-05 14:00 Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...) Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-03-05 14:26 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...) Julien Cigar <jcigar@ulb.ac.be>
2013-03-05 23:51 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...) Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-03-06 00:07 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...) Joshua D. Drake <jd@commandprompt.com>
@ 2013-03-06 16:44 ` Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jeff Janes @ 2013-03-06 16:44 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Niels Kristian Schjødt <nielskristian@autouncle.com>; Julien Cigar <jcigar@ulb.ac.be>; pgsql-performance
On Tue, Mar 5, 2013 at 4:07 PM, Joshua D. Drake <jd@commandprompt.com>wrote:
>
> On 03/05/2013 03:51 PM, Niels Kristian Schjødt wrote:
>
> 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?
>>
>
> What does explain analyze say versus just explain.
>
Better yet, "explain (analyze, buffers)" with track_io_timing turned on.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)
2013-03-05 14:00 Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...) Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-03-05 14:26 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...) Julien Cigar <jcigar@ulb.ac.be>
2013-03-05 23:51 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...) Niels Kristian Schjødt <nielskristian@autouncle.com>
@ 2013-03-06 12:33 ` Julien Cigar <jcigar@ulb.ac.be>
1 sibling, 0 replies; 6+ messages in thread
From: Julien Cigar @ 2013-03-06 12:33 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-03-06 16:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-05 14:00 Optimize SELECT * from table WHERE foreign_key_id IN (key1, key2, key3, key4...) Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-03-05 14:26 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...) Julien Cigar <jcigar@ulb.ac.be>
2013-03-05 23:51 ` Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-03-06 00:07 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...) Joshua D. Drake <jd@commandprompt.com>
2013-03-06 16:44 ` Jeff Janes <jeff.janes@gmail.com>
2013-03-06 12:33 ` Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...) Julien Cigar <jcigar@ulb.ac.be>
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