Skip to content

Add partial index on project_features.id

Vitali Tatarintev requested to merge 382972-add-monitor_access_level-index-to-project_features into master

What does this MR do and why?

Add index on project_features.id where monitor_access_level is not zero. The index is needed to optimize a new metric (to be introduced in !109380 (merged))

Contributes to #382972 (closed)

Metric's query

SELECT COUNT("project_features"."id")
  FROM "project_features"
  WHERE "project_features"."monitor_access_level" != 0 AND "project_features"."id" BETWEEN 1 AND 100000

Index

CREATE INDEX idx_project_features_id_where_monitor_access_level_not_zero
    ON project_features USING btree (id) WHERE monitor_access_level <> 0;
-- The query has been executed. Duration: 3.670 min

Before

After

Alternative indexes

I've also tried the following indexes, but it seems to me they didn't perform well

CREATE INDEX idx_project_features_monitor_access_level_and_id
  ON project_features USING btree (monitor_access_level, id);
-- The query has been executed. Duration: 3.648 min
CREATE INDEX idx_project_features_monitor_access_level
  ON project_features USING btree (monitor_access_level);
-- The query has been executed. Duration: 40.174 s

rails db:migrate

→ be rails db:migrate
main: == 20230120093325 AddMonitorAccessLevelIndexToProjectFeatures: migrating ======
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0947s
main: -- index_exists?(:project_features, :id, {:where=>"monitor_access_level <> 0", :name=>"idx_on_project_features_id_where_monitor_access_level_not_zero", :algorithm=>:concurrently})
main:    -> 0.0026s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:project_features, :id, {:where=>"monitor_access_level <> 0", :name=>"idx_on_project_features_id_where_monitor_access_level_not_zero", :algorithm=>:concurrently})
main:    -> 0.0038s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20230120093325 AddMonitorAccessLevelIndexToProjectFeatures: migrated (0.1105s)

ci: == 20230120093325 AddMonitorAccessLevelIndexToProjectFeatures: migrating ======
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- index_exists?(:project_features, :id, {:where=>"monitor_access_level <> 0", :name=>"idx_on_project_features_id_where_monitor_access_level_not_zero", :algorithm=>:concurrently})
ci:    -> 0.0030s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0005s
ci: -- add_index(:project_features, :id, {:where=>"monitor_access_level <> 0", :name=>"idx_on_project_features_id_where_monitor_access_level_not_zero", :algorithm=>:concurrently})
ci:    -> 0.0044s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20230120093325 AddMonitorAccessLevelIndexToProjectFeatures: migrated (0.0176s)

rails db:rollback:main

→ be rails db:rollback:main
main: == 20230120093325 AddMonitorAccessLevelIndexToProjectFeatures: reverting ======
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1398s
main: -- indexes(:project_features)
main:    -> 0.0034s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:project_features, {:algorithm=>:concurrently, :name=>"idx_on_project_features_id_where_monitor_access_level_not_zero"})
main:    -> 0.0021s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20230120093325 AddMonitorAccessLevelIndexToProjectFeatures: reverted (0.1556s)

rails db:rollback:ci

→ be rails db:rollback:ci
ci: == 20230120093325 AddMonitorAccessLevelIndexToProjectFeatures: reverting ======
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.1672s
ci: -- indexes(:project_features)
ci:    -> 0.0070s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:project_features, {:algorithm=>:concurrently, :name=>"idx_on_project_features_id_where_monitor_access_level_not_zero"})
ci:    -> 0.0024s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20230120093325 AddMonitorAccessLevelIndexToProjectFeatures: reverted (0.1990s)

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Vitali Tatarintev

Merge request reports