увеличить скорость группы по запросу в таблице в postgres

У меня есть таблица соединений со следующей структурой:

CREATE TABLE adjectives_friends
(
  adjective_id integer,
  friend_id integer
)
WITH (
  OIDS=FALSE
);
ALTER TABLE adjectives_friends
  OWNER TO rails;


CREATE UNIQUE INDEX index_adjectives_friends_on_adjective_id_and_friend_id
  ON adjectives_friends
  USING btree
  (adjective_id , friend_id );

CREATE UNIQUE INDEX index_adjectives_friends_on_friend_id_and_adjective_id
  ON adjectives_friends
  USING btree
  (friend_id , adjective_id );
ALTER TABLE adjectives_friends CLUSTER ON index_adjectives_friends_on_friend_id_and_adjective_id;

Эта таблица содержит около 50 миллионов записей.

Таблица прилагательных представляет собой справочную таблицу из примерно 150 записей. Что я хотел бы сделать, так это найти друга, который наиболее точно соответствует списку прилагательных. Предположим, что максимальное количество прилагательных у друга равно 10. Итак, я попробовал этот запрос:

SELECT count(friend_id) count, friend_id
  FROM adjectives_friends
  where adjective_id in (1,2,3,4,5,6,7,8,9,10)
  group by friend_id
  order by count desc
  limit 100

Это занимает около 10 секунд на моей машине разработки с планом запроса

"Limit  (cost=831652.00..831652.25 rows=100 width=4)"
"  ->  Sort  (cost=831652.00..831888.59 rows=94634 width=4)"
"        Sort Key: (count(friend_id))"
"        ->  GroupAggregate  (cost=804185.31..828035.16 rows=94634 width=4)"
"              ->  Sort  (cost=804185.31..811819.81 rows=3053801 width=4)"
"                    Sort Key: friend_id"
"                    ->  Bitmap Heap Scan on adjectives_friends  (cost=85958.72..350003.24 rows=3053801 width=4)"
"                          Recheck Cond: (adjective_id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))"
"                          ->  Bitmap Index Scan on index_adjectives_friends_on_adjective_id_and_friend_id  (cost=0.00..85195.26 rows=3053801 width=0)"
"                                Index Cond: (adjective_id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))"

Порядок - это то, что меня убивает, но я не знаю, как этого избежать. Количество не может быть предварительно вычислено, потому что прилагательные, которые нужно выбрать, совершенно произвольны, и есть> 150 выберите 10 комбинаций. Прямо сейчас я думаю, что лучший вариант — это взять 100 лучших результатов по созданию друзей, сохранить результаты, а затем обновлять их каждые n интервалов времени. Это было бы приемлемо, поскольку ожидается, что прилагательные будут переключаться так часто, и я не знаю точных 100 лучших результатов. Но если бы я мог получить скорость запроса около 1-2 секунд, в этом не было бы необходимости. Какие-либо предложения?


postgresql indexing database-performance sql-order-by jointable
person me-    schedule 30.01.2012    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Я не думаю, что вы добьетесь большего успеха с этим планом запроса. Поверю вам на слово, что счет нельзя вычислить заранее.

Я думаю, что ваши лучшие ставки

Если вы можете использовать smallint вместо integer, ваши таблицы и индексы будут уже, больше поместится на странице, а ваши запросы должны выполняться быстрее. Но smallint — это 2-байтовое целое число в диапазоне от -32768 до +32767. Если вам нужно больше идентификаторов, smallint не сработает.

person Mike Sherrill 'Cat Recall'    schedule 30.01.2012