Skip to content

Adjust condition for partial index

Andreas Brandl requested to merge ab/services-partial-indexes into master

What does this MR do?

This is an alternative to !33032 (closed):

We adjust the partial condition of two indexes such that those get picked up by the queries we are using. This has the same effect as shown in #219094 (comment 348889466) (but the fix is different).

Example query:

 SELECT "services".* FROM "services" WHERE "services"."instance" = TRUE AND "services"."type" IN ('AlertsService', 'AsanaService', 'AssemblaService', 'BambooService', 'BugzillaService', 'BuildkiteService', 'CampfireService', 'CustomIssueTrackerService', 'DiscordService', 'DroneCiService', 'EmailsOnPushService', 'ExternalWikiService', 'FlowdockService', 'GithubService', 'GitlabSlackApplicationService', 'HangoutsChatService', 'HipchatService', 'IrkerService', 'JenkinsService', 'JiraService', 'MattermostService', 'MattermostSlashCommandsService', 'MicrosoftTeamsService', 'PackagistService', 'PipelinesEmailService', 'PivotaltrackerService', 'PrometheusService', 'PushoverService', 'RedmineService', 'SlackService', 'SlackSlashCommandsService', 'TeamcityService', 'UnifyCircuitService', 'WebexTeamsService', 'YoutrackService')

From #database-labs:

  1. Before: 106,000 ms https://explain.depesz.com/s/PEDVP
  2. After: <1ms https://explain.depesz.com/s/o8DH

Of course this is less dramatic on .com, and rather in the range displayed in #219094 (comment 348889466) (700ms down to <1ms).

GitLab.com statistics (based on pg_stat_user_indexes show that both partial indexes (in their current form) are not being used (since the last stats reset, likely the time we upgraded PG11).

Does this MR meet the acceptance criteria?

Conformity

Edited by Yorick Peterse

Merge request reports