如果表已经有正确的索引，如何摆脱位图堆扫描

我正在运行 PostgreSQL 9.6。这些是相关的定义：

CREATE TABLE IF NOT EXISTS instagram.profiles_1000 (
    id                          SERIAL PRIMARY KEY,
    username                    VARCHAR(255) NOT NULL UNIQUE,
    followers                   BIGINT,
    tsv                         TSVECTOR
);

CREATE UNIQUE INDEX IF NOT EXISTS instagram_username_index
    ON instagram.profiles_1000(username);
CREATE INDEX IF NOT EXISTS instagram_followers_index
    ON instagram.profiles_1000(followers);
CREATE INDEX IF NOT EXISTS instagram_textsearch_index
    ON instagram.profiles_1000 USING GIN(tsv);

文本向量由触发器更新：

CREATE FUNCTION instagram_documents_search_trigger() RETURNS trigger AS $$
begin
  new.tsv :=
        setweight(to_tsvector(COALESCE(new.username, '')), 'D') || ' ' ||
        setweight(to_tsvector(COALESCE(new.full_name, '')), 'C') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_country, '')), 'B') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_region, '')), 'B') || ' ' ||
        setweight(to_tsvector(COALESCE(new.biography, '')), 'A') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_city, '')), 'A');
  return new;
end
$$ LANGUAGE plpgsql;


CREATE TRIGGER instagram_tsvectorupdate BEFORE INSERT OR UPDATE
    ON instagram.profiles_1000 FOR EACH ROW
    EXECUTE PROCEDURE instagram_documents_search_trigger();

这是查询：

select instagram.profiles_1000.*, categories, followers as rank                                                                                            
from instagram.profiles_1000
join plainto_tsquery('arts') as q on q @@ tsv
left outer join instagram.profile_categories_agg on instagram.profiles_1000.username = instagram.profile_categories_agg.username
where followers is not null and followers > 0
order by (followers, -id) desc
limit 50;

这是的输出EXPLAIN (ANALYZE, BUFFERS)：

https://explain.depesz.com/s/ceCd

罪魁祸首是位图堆扫描，它占了总执行时间的大部分。坦率地说，我不明白为什么需要它，特别是因为位图索引扫描instagram_textsearch_index已经根据搜索词过滤了行。

有人可以阐明一下吗？

编辑有人指出我误读了解释输出。事实上，左外连接花费了很多时间。我尝试按如下方式删除它：

select instagram.profiles_1000.*, followers as rank
from instagram.profiles_1000
join plainto_tsquery('arts') as q on q @@ tsv                                              
where followers is not null and followers > 0
order by (followers, -id) desc
limit 50;

但是查询仍然需要 13 秒！这是EXPLAIN (ANALYZE, BUFFERS)输出：

https://explain.depesz.com/s/awfH

现在瓶颈似乎是全文搜索。真的有那么慢吗？该表只有 500 万行，并且tsv（具有 type TSVECTOR）由以下索引索引：

CREATE INDEX IF NOT EXISTS instagram_textsearch_index_1000
    ON instagram.profiles_1000 USING GIN(tsv);

编辑 2我意识到如果我只处理与搜索匹配的配置文件（最多总是 50 个），我可以编写更精简的查询。使用此查询：

select p.*, categories
from
    (select id
    from instagram.profiles_1000, plainto_tsquery('arts') as q
    where q @@ tsv and followers is not null and followers > 0
    order by (followers, -id) desc
    limit 50) as ids
inner join instagram.profiles_1000 as p on
    p.id = ids.id
left outer join instagram.profile_categories_agg as c on
    c.username = p.username;

我能够得到这个结果： https ://explain.depesz.com/s/OvG

这使搜索时间约为 3 秒。至少达到 1 秒会更好。