Skip to content

Improve query performance for Dashboard::SnippetsController#index

Andreas Brandl requested to merge 42877-snippets-dashboard-slow into master

This change addresses the snippet dashboard which is really slow at the moment. See https://gitlab.com/gitlab-org/gitlab-ce/issues/42877 for statistics.

It does this by:

  • Change query in Project.public_or_visible_to_user to UNION instead of OR visibility_level IN (..) and push down conditions
  • Add partial index on project to leverage index-only scan: (id) WHERE visibility_level IN (10, 20)
  • Optimize Project.public_or_visible_to_user to omit the visibility check completely when given all levels available (0,10,20)

See https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/17088#note_59749778 for query comparison details.

Database Checklist

When adding migrations:

  • Updated db/schema.rb
  • Added a down method so the migration can be reverted
  • Added the output of the migration(s) to the MR body
  • Added tests for the migration in spec/migrations if necessary (e.g. when migrating data)

Migration:

== 20180213131630 AddPartialIndexToProjectsForIndexOnlyScans: migrating =======
-- index_exists?(:projects, :id, {:name=>"index_projects_on_id_partial_for_visibility"})
   -> 0.0065s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- add_index(:projects, :id, {:name=>"index_projects_on_id_partial_for_visibility", :unique=>true, :where=>"visibility_level IN (10,20)", :algorithm=>:concurrently})
   -> 0.0068s
== 20180213131630 AddPartialIndexToProjectsForIndexOnlyScans: migrated (0.0139s)

Rollback:

== 20180213131630 AddPartialIndexToProjectsForIndexOnlyScans: reverting =======
-- index_exists?(:projects, :id, {:name=>"index_projects_on_id_partial_for_visibility"})
   -> 0.0072s
-- transaction_open?()
   -> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
   -> 0.0003s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_on_id_partial_for_visibility"})
   -> 0.0080s
== 20180213131630 AddPartialIndexToProjectsForIndexOnlyScans: reverted (0.0161s)

When adding or modifying queries to improve performance:

  • Included data that shows the performance improvement, preferably in the form of a benchmark
  • Included the output of EXPLAIN (ANALYZE, BUFFERS) of the relevant queries

General Checklist

Edited by Yorick Peterse

Merge request reports