Add index for compliance merged MRs to events
What does this MR do?
This MR adds partial index to events table to speed up count query on Compliance Dashboard.
Relates to #220328 (closed)
Migration
== 20200807070820 AddIndexForComplianceMergedMergeRequestToEvents: reverting ==
-- transaction_open?() -> 0.0000s-- indexes(:events)
-> 0.0072s
-- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_on_project_id_and_id_desc_on_merged_action"})
-> 0.0026s
== 20200807070820 AddIndexForComplianceMergedMergeRequestToEvents: reverted (0.0103s)
== 20200807070820 AddIndexForComplianceMergedMergeRequestToEvents: migrating ==
-- transaction_open?()
-> 0.0000s
-- index_exists?(:events, [:project_id, :id], {:order=>{:id=>:desc}, :where=>"action = 7", :name=>"index_events_on_project_id_and_id_desc_on_merged_action", :algorithm=>:concurrently})
-> 0.0101s
-- add_index(:events, [:project_id, :id], {:order=>{:id=>:desc}, :where=>"action = 7", :name=>"index_events_on_project_id_and_id_desc_on_merged_action", :algorithm=>:concurrently})
-> 0.0075s
== 20200807070820 AddIndexForComplianceMergedMergeRequestToEvents: migrated (0.0188s) Migration took 18.116 min on #database-lab.
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+---------------------------------------------------------+-------+--------+--------+--------+-------------
public | index_events_on_project_id_and_id_desc_on_merged_action | index | gitlab | events | 752 MB |
(1 row)List query
WITH "ordered_events_cte" AS (
SELECT
events.target_id as target_id
FROM
"projects"
JOIN LATERAL (
SELECT
"events"."created_at",
"events"."target_id"
FROM
"events"
WHERE
(projects.id = project_id)
AND "events"."action" = 7
ORDER BY
"events"."id" DESC
LIMIT
1
) events ON true
WHERE
"projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970
)
UNION
(
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"
)
)
SELECT
"namespaces"."id"
FROM
"base_and_descendants" AS "namespaces"
)
ORDER BY
events.created_at DESC
)
SELECT
"merge_requests".*
FROM
"merge_requests"
INNER JOIN "ordered_events_cte" ON "merge_requests"."id" = "ordered_events_cte"."target_id"
ORDER BY
array_position(
ARRAY(
SELECT
target_id
FROM
ordered_events_cte
),
merge_requests.id
)
LIMIT 20 OFFSET 0Count query
WITH "ordered_events_cte" AS (
SELECT
events.target_id as target_id
FROM
"projects"
JOIN LATERAL (
SELECT
"events"."created_at",
"events"."target_id"
FROM
"events"
WHERE
(projects.id = project_id)
AND "events"."action" = 7
ORDER BY
"events"."id" DESC
LIMIT
1
) events ON true
WHERE
"projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970
)
UNION
(
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"
)
)
SELECT
"namespaces"."id"
FROM
"base_and_descendants" AS "namespaces"
)
ORDER BY
events.created_at DESC
)
SELECT
COUNT(*)
FROM
"merge_requests"
INNER JOIN "ordered_events_cte" ON "merge_requests"."id" = "ordered_events_cte"."target_id"Before
List query timing
Time: 42.994 s
- planning: 2.076 ms
- execution: 42.991 s
- I/O read: 25.668 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 86696 (~677.30 MiB) from the buffer pool
- reads: 17522 (~136.90 MiB) from the OS file cache, including disk I/O
- dirtied: 192 (~1.50 MiB)
- writes: 0Count query timing
Time: 2.063 min
- planning: 1.798 ms
- execution: 2.063 min
- I/O read: 2.038 min
- I/O write: 0.000 ms
Shared buffers:
- hits: 14437 (~112.80 MiB) from the buffer pool
- reads: 87358 (~682.50 MiB) from the OS file cache, including disk I/O
- dirtied: 1828 (~14.30 MiB)
- writes: 0After
List query timing
# Cold cache
Time: 1.049 s
- planning: 1.865 ms
- execution: 1.047 s
- I/O read: 1.025 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 8845 (~69.10 MiB) from the buffer pool
- reads: 386 (~3.00 MiB) from the OS file cache, including disk I/O
- dirtied: 1 (~8.00 KiB)
- writes: 0
# Warm cache
Time: 18.415 ms
- planning: 1.926 ms
- execution: 16.489 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 9231 (~72.10 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Count query timing
# Cold cache
Time: 3.235 s
- planning: 1.778 ms
- execution: 3.233 s
- I/O read: 3.195 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 6045 (~47.20 MiB) from the buffer pool
- reads: 3216 (~25.10 MiB) from the OS file cache, including disk I/O
- dirtied: 116 (~928.00 KiB)
- writes: 0
# Warm cache
Time: 14.468 ms
- planning: 1.768 ms
- execution: 12.700 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 9224 (~72.10 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. - [-] Tested in all supported browsers
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
- [-] Label as security and @ mention
@gitlab-com/gl-security/appsec - [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
- [-] Security reports checked/validated by a reviewer from the AppSec team
Edited by Tan Le