CODEWITHSUNDEEP
sqlpostgresqlindexing
Chet
Chet

Reputation: 19849

Why is my multicolumn index-only scan fetching all rows in the index and resorting just to return one row in PostgreSQL?

I have the following multicolumn index in my Postgres database:

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
);

This query successfully does an index-only scan over that index:

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;

However, when I explain analyze this query, it appears that rows=891 were fetched and re-sorted with heapsort.

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

Any ideas why this happens? It seems like Postgres should be able to use the end_time desc index to fetch just the latest value about 20x faster.

Upvotes: 2

Views: 44

Answers (1)

The Impaler
The Impaler

Reputation: 48810

Because your query cannot be pipelined.

In a query that can be pipelined, the result from the previous operator does NOT need to be completed in order to start processing the next operator.

In your case the operator "Index Only Scan" must complete before the next operator "Sort" can be executed.

The query cannot be pipelined because there's no unique constraint that emcompasses all query columns.

Nevertheless, it barely matters in terms of performance. Sorting a few rows (just 1 maybe?) doesn't require any effort to the database engine.

Maybe you should try adding the unique constraint to the table, and see if the behavior changes. Try adding the following constraint:

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);

If you cannot add it, then the column combination in not unique and PostgreSQL is right to consider multiple rows that would need to be sorted.

If you can add it, then get the new execution plan.

Upvotes: 2

Related Questions