提高 postgres 中表的分组查询速度

Question

我有一个具有以下结构的连接表：

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;

该表包含大约 5000 万条记录。

形容词表是一个包含约 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[]))"

order by 是杀死我的原因，但我不知道避免它的好方法。无法预先计算计数，因为要选择的形容词完全是任意的，并且有>个。 150 选择 10 种组合。现在，我认为最好的选择是获取好友创建的 100 个最佳结果，保存结果，然后每 n 个时间间隔更新一次。这是可以接受的，因为形容词应该经常更换，而且我不知道确切的 100 个最佳结果。但是，如果我可以将查询速度提高到 1 - 2 秒左右，那就没有必要了。有什么建议吗？

Answer 1

我认为您使用该查询计划不会做得更好。我相信你的话，计数无法预先计算。

我认为你最好的选择是

• 表调整
• 服务器调整
• 更快的硬件

如果你可以使用smallint而不是整数，您的表和索引将更窄，页面可以容纳更多内容，并且您的查询运行得更快。但smallint是一个2字节整数，范围从-32768到+32767。如果您需要更多的 id 编号，则smallint 将不起作用。