Reorder snippets in lists using `updated_at` column (!34393) · Merge requests · GitLab.org / GitLab

Francisco Javier López (ex-Gitlab) requested to merge fj-default-order-snippet-lists into master Jun 11, 2020

What does this MR do?

In this MR we change the SnippetFinder to accept a sort param. This param will be passed from the index action of the *SnippetsController. By default, this order will be by the updated_at column and it will be calculated in the controllers.

Refs #215854 (closed)

Things Left

Understand the changes
There are several specs missing. I want you to identify what needs to be tested and try to implement the specs. The existing tests are a hint.

Query Plan

Old Query

EXPLAIN SELECT “snippets”.* FROM ((SELECT “snippets”.* FROM “snippets” WHERE (“snippets”.“visibility_level” IN (0, 10, 20) OR “snippets”.“author_id” = 5249152) AND “snippets”.“project_id” IS NULL)
UNION
(SELECT “snippets”.* FROM “snippets” INNER JOIN “projects” ON “projects”.“id” = “snippets”.“project_id” INNER JOIN “project_features” ON “project_features”.“project_id” = “projects”.“id” WHERE (“snippets”.“visibility_level” IN (0, 10, 20) OR “snippets”.“author_id” = 5249152) AND (projects.visibility_level IN (0,10,20)) AND “project_features”.“snippets_access_level” IN (20, 30))
UNION
(SELECT “snippets”.* FROM “snippets” INNER JOIN “projects” ON “projects”.“id” = “snippets”.“project_id” INNER JOIN “project_features” ON “project_features”.“project_id” = “projects”.“id” WHERE “project_features”.“snippets_access_level” IN (20, 30, 10) AND (EXISTS (SELECT 1 FROM “project_authorizations” WHERE (project_id = snippets.project_id) AND “project_authorizations”.“user_id” = 5249152))))

Old Plan

Unique  (cost=1432912.18..1449713.55 rows=353713 width=2777) (actual time=3925.334..5583.964 rows=269692 loops=1)
   Buffers: shared hit=1131638
   ->  Sort  (cost=1432912.18..1433796.46 rows=353713 width=2777) (actual time=3925.332..4810.198 rows=270420 loops=1)
         Sort Key: snippets.id, snippets.title, snippets.content, snippets.author_id, snippets.project_id, snippets.created_at, snippets.updated_at, snippets.file_name, snippets.type, snippets.visibility_level, snippets.title_html, snippets.content_html, snippets.cached_markdown_version, snippets.description, snippets.description_html, snippets.encrypted_secret_token, snippets.encrypted_secret_token_iv, snippets.secret
         Sort Method: external merge  Disk: 261088kB
         Buffers: shared hit=1105498
         ->  Append  (cost=0.42..581916.60 rows=353713 width=2777) (actual time=0.017..1804.690 rows=270420 loops=1)
               Buffers: shared hit=1091030
               ->  Index Scan using index_snippets_on_project_id_and_visibility_level on public.snippets  (cost=0.42..71703.99 rows=157476 width=2089) (actual time=0.016..375.530 rows=189315 loops=1)
                     Index Cond: (snippets.project_id IS NULL)
                     Filter: ((snippets.visibility_level = ANY ('{0,10,20}'::integer[])) OR (snippets.author_id = 5249152))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=142269
               ->  Nested Loop  (cost=1.29..501542.92 rows=196215 width=2089) (actual time=0.067..1337.033 rows=80273 loops=1)
                     Buffers: shared hit=921083
                     ->  Nested Loop  (cost=0.86..466268.17 rows=64889 width=2093) (actual time=0.046..997.210 rows=80273 loops=1)
                           Buffers: shared hit=599897
                           ->  Index Scan using index_snippets_on_project_id_and_visibility_level on public.snippets snippets_1  (cost=0.42..74649.80 rows=234514 width=2089) (actual time=0.023..537.664 rows=281933 loops=1)
                                 Filter: ((snippets_1.visibility_level = ANY ('{0,10,20}'::integer[])) OR (snippets_1.author_id = 5249152))
                                 Rows Removed by Filter: 0
                                 Buffers: shared hit=229140
                           ->  Index Scan using index_project_features_on_project_id on public.project_features  (cost=0.43..1.67 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=281933)
                                 Index Cond: (project_features.project_id = snippets_1.project_id)
                                 Filter: (project_features.snippets_access_level = ANY ('{20,30}'::integer[]))
                                 Rows Removed by Filter: 0
                                 Buffers: shared hit=370757
                     ->  Index Scan using projects_pkey on public.projects  (cost=0.43..0.53 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=80273)
                           Index Cond: (projects.id = project_features.project_id)
                           Filter: (projects.visibility_level = ANY ('{0,10,20}'::integer[]))
                           Rows Removed by Filter: 0
                           Buffers: shared hit=321186
               ->  Nested Loop  (cost=211.96..3363.99 rows=22 width=2089) (actual time=3.365..41.203 rows=832 loops=1)
                     Buffers: shared hit=27678
                     ->  Nested Loop  (cost=211.53..3351.56 rows=26 width=2097) (actual time=3.348..38.492 rows=839 loops=1)
                           Buffers: shared hit=24322
                           ->  Nested Loop  (cost=211.10..2712.83 rows=1253 width=8) (actual time=2.297..24.282 rows=3083 loops=1)
                                 Buffers: shared hit=14246
                                 ->  HashAggregate  (cost=210.67..223.16 rows=1249 width=4) (actual time=2.279..3.401 rows=3083 loops=1)
                                       Group Key: project_authorizations.project_id
                                       Buffers: shared hit=1723
                                       ->  Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations  (cost=0.57..207.54 rows=1253 width=4) (actual time=0.028..1.386 rows=3083 loops=1)
                                             Index Cond: (project_authorizations.user_id = 5249152)
                                             Heap Fetches: 518
                                             Buffers: shared hit=1723
                                 ->  Index Only Scan using projects_pkey on public.projects projects_1  (cost=0.43..1.99 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3083)
                                       Index Cond: (projects_1.id = project_authorizations.project_id)
                                       Heap Fetches: 471
                                       Buffers: shared hit=12523
                           ->  Index Scan using index_snippets_on_project_id_and_visibility_level on public.snippets snippets_2  (cost=0.42..0.48 rows=3 width=2089) (actual time=0.003..0.004 rows=0 loops=3083)
                                 Index Cond: (snippets_2.project_id = projects_1.id)
                                 Buffers: shared hit=10076
                     ->  Index Scan using index_project_features_on_project_id on public.project_features project_features_1  (cost=0.43..0.48 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=839)
                           Index Cond: (project_features_1.project_id = projects_1.id)
                           Filter: (project_features_1.snippets_access_level = ANY ('{20,30,10}'::integer[]))
                           Rows Removed by Filter: 0
                           Buffers: shared hit=3356

Old Execution times

Time: 8.058 s
  - planning: 21.450 ms
  - execution: 8.036 s
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1131638 (~8.60 GiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Temp buffers:
  - reads: 32636 (~255.00 MiB)
  - writes: 32639 (~255.00 MiB)

New Query

EXPLAIN SELECT “snippets”.* FROM ((SELECT “snippets”.* FROM “snippets” WHERE (“snippets”.“visibility_level” IN (0, 10, 20) OR “snippets”.“author_id” = 5249152) AND “snippets”.“project_id” IS NULL)
UNION
(SELECT “snippets”.* FROM “snippets” INNER JOIN “projects” ON “projects”.“id” = “snippets”.“project_id” INNER JOIN “project_features” ON “project_features”.“project_id” = “projects”.“id” WHERE (“snippets”.“visibility_level” IN (0, 10, 20) OR “snippets”.“author_id” = 5249152) AND (projects.visibility_level IN (0,10,20)) AND “project_features”.“snippets_access_level” IN (20, 30))
UNION
(SELECT “snippets”.* FROM “snippets” INNER JOIN “projects” ON “projects”.“id” = “snippets”.“project_id” INNER JOIN “project_features” ON “project_features”.“project_id” = “projects”.“id” WHERE “project_features”.“snippets_access_level” IN (20, 30, 10) AND (EXISTS (SELECT 1 FROM “project_authorizations” WHERE (project_id = snippets.project_id) AND “project_authorizations”.“user_id” = 5249152)))) snippets ORDER BY “snippets”.“updated_at” DESC

New Plan

Sort  (cost=2304246.26..2305130.54 rows=353713 width=2777) (actual time=7928.546..8262.382 rows=269692 loops=1)
   Sort Key: snippets.updated_at DESC
   Sort Method: external merge  Disk: 260392kB
   Buffers: shared hit=1131638
   ->  Unique  (cost=1432912.18..1449713.55 rows=353713 width=2777) (actual time=4790.426..6447.847 rows=269692 loops=1)
         Buffers: shared hit=1131638
         ->  Sort  (cost=1432912.18..1433796.46 rows=353713 width=2777) (actual time=4790.423..5635.889 rows=270420 loops=1)
               Sort Key: snippets.id, snippets.title, snippets.content, snippets.author_id, snippets.project_id, snippets.created_at, snippets.updated_at, snippets.file_name, snippets.type, snippets.visibility_level, snippets.title_html, snippets.content_html, snippets.cached_markdown_version, snippets.description, snippets.description_html, snippets.encrypted_secret_token, snippets.encrypted_secret_token_iv, snippets.secret
               Sort Method: external merge  Disk: 261088kB
               Buffers: shared hit=1105498
               ->  Append  (cost=0.42..581916.60 rows=353713 width=2777) (actual time=0.019..1801.711 rows=270420 loops=1)
                     Buffers: shared hit=1091030
                     ->  Index Scan using index_snippets_on_project_id_and_visibility_level on public.snippets  (cost=0.42..71703.99 rows=157476 width=2089) (actual time=0.018..347.137 rows=189315 loops=1)
                           Index Cond: (snippets.project_id IS NULL)
                           Filter: ((snippets.visibility_level = ANY ('{0,10,20}'::integer[])) OR (snippets.author_id = 5249152))
                           Rows Removed by Filter: 0
                           Buffers: shared hit=142269
                     ->  Nested Loop  (cost=1.29..501542.92 rows=196215 width=2089) (actual time=0.056..1366.430 rows=80273 loops=1)
                           Buffers: shared hit=921083
                           ->  Nested Loop  (cost=0.86..466268.17 rows=64889 width=2093) (actual time=0.039..1013.765 rows=80273 loops=1)
                                 Buffers: shared hit=599897
                                 ->  Index Scan using index_snippets_on_project_id_and_visibility_level on public.snippets snippets_1  (cost=0.42..74649.80 rows=234514 width=2089) (actual time=0.021..538.885 rows=281933 loops=1)
                                       Filter: ((snippets_1.visibility_level = ANY ('{0,10,20}'::integer[])) OR (snippets_1.author_id = 5249152))
                                       Rows Removed by Filter: 0
                                       Buffers: shared hit=229140
                                 ->  Index Scan using index_project_features_on_project_id on public.project_features  (cost=0.43..1.67 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=281933)
                                       Index Cond: (project_features.project_id = snippets_1.project_id)
                                       Filter: (project_features.snippets_access_level = ANY ('{20,30}'::integer[]))
                                       Rows Removed by Filter: 0
                                       Buffers: shared hit=370757
                           ->  Index Scan using projects_pkey on public.projects  (cost=0.43..0.53 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=80273)
                                 Index Cond: (projects.id = project_features.project_id)
                                 Filter: (projects.visibility_level = ANY ('{0,10,20}'::integer[]))
                                 Rows Removed by Filter: 0
                                 Buffers: shared hit=321186
                     ->  Nested Loop  (cost=211.96..3363.99 rows=22 width=2089) (actual time=3.753..36.944 rows=832 loops=1)
                           Buffers: shared hit=27678
                           ->  Nested Loop  (cost=211.53..3351.56 rows=26 width=2097) (actual time=3.735..34.018 rows=839 loops=1)
                                 Buffers: shared hit=24322
                                 ->  Nested Loop  (cost=211.10..2712.83 rows=1253 width=8) (actual time=2.622..21.830 rows=3083 loops=1)
                                       Buffers: shared hit=14246
                                       ->  HashAggregate  (cost=210.67..223.16 rows=1249 width=4) (actual time=2.601..3.787 rows=3083 loops=1)
                                             Group Key: project_authorizations.project_id
                                             Buffers: shared hit=1723
                                             ->  Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations  (cost=0.57..207.54 rows=1253 width=4) (actual time=0.030..1.529 rows=3083 loops=1)
                                                   Index Cond: (project_authorizations.user_id = 5249152)
                                                   Heap Fetches: 518
                                                   Buffers: shared hit=1723
                                       ->  Index Only Scan using projects_pkey on public.projects projects_1  (cost=0.43..1.99 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=3083)
                                             Index Cond: (projects_1.id = project_authorizations.project_id)
                                             Heap Fetches: 471
                                             Buffers: shared hit=12523
                                 ->  Index Scan using index_snippets_on_project_id_and_visibility_level on public.snippets snippets_2  (cost=0.42..0.48 rows=3 width=2089) (actual time=0.003..0.003 rows=0 loops=3083)
                                       Index Cond: (snippets_2.project_id = projects_1.id)
                                       Buffers: shared hit=10076
                           ->  Index Scan using index_project_features_on_project_id on public.project_features project_features_1  (cost=0.43..0.48 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=839)
                                 Index Cond: (project_features_1.project_id = projects_1.id)
                                 Filter: (project_features_1.snippets_access_level = ANY ('{20,30,10}'::integer[]))
                                 Rows Removed by Filter: 0
                                 Buffers: shared hit=3356

New Execution times

Time: 8.389 s
  - planning: 23.571 ms
  - execution: 8.365 s
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms
Shared buffers:
  - hits: 1131638 (~8.60 GiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
Temp buffers:
  - reads: 65185 (~509.30 MiB)
  - writes: 65191 (~509.30 MiB)

Does this MR meet the acceptance criteria?

Conformity

Edited Jul 06, 2020 by Dibyadarshi Dash

Reorder snippets in lists using `updated_at` column

What does this MR do?

Things Left

Does this MR meet the acceptance criteria?

Conformity

Merge request reports