代码之家 › 专栏 › 技术社区 › Chet

为什么我的多列索引只能扫描索引中的所有行,而只能在PostgreSQL中返回一行?

indexing postgresql sql

Chet · 技术社区 · 7 年前

我的Postgres数据库中有以下多列索引:

create index activity_get_latest_idx on activity using btree (
    type,
    space_id,
    navigable_block_id,
    collection_id,
    mentioned_user_id,
    mentioned_block_id,
    mentioned_property,
    top_level_block_id,
    collection_row_id,
    discussion_id,
    invited_user_id,
    collection_view_id,
    collection_property_id,
    permission_group_id,
    end_time desc,
    id -- index-only scan
);

此查询成功地对该索引执行仅索引扫描:

explain analyze
select id from activity
where type = 'block-edited' 
and space_id = '6d702c09-8795-4185-abb3-dc6b3e8907dc' 
and navigable_block_id = '67dcd86c-f1ed-4708-9d32-4bb4bdb41ac7' 
and collection_id is null
and mentioned_user_id is null
and mentioned_block_id is null
and mentioned_property is null
and top_level_block_id is null
and collection_row_id is null
and discussion_id is null
and invited_user_id is null
and collection_view_id is null
and collection_property_id is null
and permission_group_id is null
order by end_time desc
limit 1;

然而,当我 explain analyze 这个问题看来 rows=891 被希普索尔带走并重新分类。

Limit  (cost=8.75..8.76 rows=1 width=24) (actual time=0.999..1.000 rows=1 loops=1)
  ->  Sort  (cost=8.75..8.76 rows=1 width=24) (actual time=0.998..0.998 rows=1 loops=1)
        Sort Key: end_time DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Index Only Scan using activity_get_latest_idx on activity  (cost=0.69..8.74 rows=1 width=24) (actual time=0.032..0.755 rows=891 loops=1)
              Index Cond: ((type = 'block-edited'::activity_type) AND (space_id = '6d702c09-8795-4185-abb3-dc6b3e8907dc'::uuid) AND (navigable_block_id = '67dcd86c-f1ed-4708-9d32-4bb4bdb41ac7'::uuid) AND (collection_id IS NULL) AND (mentioned_user_id IS NULL) AND (mentioned_block_id IS NULL) AND (mentioned_property IS NULL) AND (top_level_block_id IS NULL) AND (collection_row_id IS NULL) AND (discussion_id IS NULL) AND (invited_user_id IS NULL) AND (collection_view_id IS NULL) AND (collection_property_id IS NULL) AND (permission_group_id IS NULL))
              Heap Fetches: 18
Planning time: 0.184 ms
Execution time: 1.028 ms

知道为什么会这样吗?似乎博士后应该能够使用 end_time desc 索引以获取最新值,大约快20倍。

1 回复 | 直到 7 年前

The Impaler 7 年前

因为你的查询无法流水线处理。

在可以管道化的查询中,不需要完成前一个运算符的结果就可以开始处理下一个运算符。

在您的情况下,必须先完成操作员“仅索引扫描”,然后才能执行下一个操作员“排序”。

查询无法管道化,因为 没有唯一的约束 这将覆盖所有查询列。

尽管如此,就性能而言,这几乎无关紧要。排序几行(可能只有1行?)不需要对数据库引擎进行任何操作。

也许您应该尝试向表中添加唯一约束,看看行为是否发生了变化。尝试添加以下约束:

alter table activity add constraint uq1_activity unique (
  type, space_id, navigable_block_id, collection_id, mentioned_user_id,
  mentioned_block_id, mentioned_property, top_level_block_id,
  collection_row_id, discussion_id, invited_user_id, 
  collection_view_id, collection_property_id, permission_group_id);

如果不能添加它,那么在非唯一和PostgreSQL中的列组合是正确的,以考虑需要排序的多行。

如果你可以添加它,那么就得到新的执行计划。