Move ID in pages_deployments_deleted_at_index to end

The following discussion from !135993 (merged) should be addressed:

  • @ahegyi started a discussion: (+2 comments)

    Looks ok, a project has only a few deployments and the active scope (deleted_at filter). If the deployment count doesn't go above thousands of rows, we don't need batching.

    I noticed that we have an index that covers a few conditions:

    "pages_deployments_deleted_at_index" btree (id, project_id, path_prefix) WHERE deleted_at IS NULL

    Unfortunately, the index won't be utilized in these queries because the first column is id. Do you know why this index was added? (I don't see any usage for this index)

    As a follow up, I'd try adding a new index that covers the following columns: (project_id, path_prefix, id). This would support the query mentioned in !130848 (comment 1557474543) and it would also fully support some of the scopes introduced in this MR.

    One issue with the index starting with the id column is that the DB will need to scan all index entries matching the id condition first.

    For example: (id > 2000 and id < 3000) AND project_id = 4 and path_prefix = ''

    The DB will iterate over each index entries (globally) within this range and checks if the 2nd column (project_id) matches the condition and then do the same for the path_prefix condition. If we assume that there are no gaps in the ids, we look at 1000 index entry scans.

    With the proposed index, the DB will jump to the index entries where project_id=4, within the entries, the DB will jump to the entries where the path_prefix condition matches. From there filtering the id range requires much less I/O because the lookup is already reduced to a single project_id id and path_prefix combination. If we assume that not all records in the id range (id > 2000 and id < 3000) belong to the given project and path prefix, then the DB will use much less I/O.

Development plan:

  • Add new index that covers the following columns: (project_id, path_prefix, id): !185610 (merged)
  • Remove existing index pages_deployments_deleted_at_index in follow up MR: !186432 (merged)
Edited by Naman Jagdish Gala