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

Поиск
Список
Период
Сортировка
От Niels Kristian Schjødt
Тема Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)
Дата
Msg-id DC8BDA57-E91E-4E82-A83C-5217D77B02FF@autouncle.com
обсуждение исходный текст
Ответы Re: Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Steven Crandell
Дата:
Сообщение: Re: hardware upgrade, performance degrade?
Следующее
От: Mark Smith
Дата:
Сообщение: Re: Poor performance after update from SLES11 SP1 to SP2