Optimise index on audit events for CSV export
What does this MR do?
To facilitate exporting audit events to CSV, we need to adjust the existing index to cater for filtering by created_at.
Targeted queries:
Warm-up
SELECT
"audit_events".*
FROM
"audit_events"
WHERE
"audit_events"."entity_type" = 'User'
AND "audit_events"."entity_id" = '-1'
AND "audit_events"."author_id" = '-1'
AND "audit_events"."created_at" <= '2020-09-01 00:00:00'
AND "audit_events"."created_at" >= '2020-08-01 00:00:00'
ORDER BY
"audit_events"."id" DESC
LIMIT 10000Batched
SELECT
"audit_events".*
FROM
"audit_events"
WHERE
"audit_events"."entity_type" = 'User'
AND "audit_events"."entity_id" = '-1'
AND "audit_events"."author_id" = '-1'
AND "audit_events"."created_at" <= '2020-09-01 00:00:00'
AND "audit_events"."created_at" >= '2020-08-01 00:00:00'
AND "audit_events"."id" > 357698842
ORDER BY
"audit_events"."id" ASC
LIMIT 1000Databases
Before
Warm-up 5.728 min
Time: 5.728 min
- planning: 0.160 ms
- execution: 5.728 min
- I/O read: 5.628 min
- I/O write: 184.880 ms
Shared buffers:
- hits: 250812 (~1.90 GiB) from the buffer pool
- reads: 309145 (~2.40 GiB) from the OS file cache, including disk I/O
- dirtied: 7657 (~59.80 MiB)
- writes: 1589 (~12.40 MiB)Batched 555.271 ms
Time: 555.271 ms
- planning: 0.282 ms
- execution: 554.989 ms
- I/O read: 547.247 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 516 (~4.00 MiB) from the buffer pool
- reads: 493 (~3.90 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0After
Warm-up 323.917 ms
Time: 323.917 ms
- planning: 0.322 ms
- execution: 323.595 ms
- I/O read: 289.804 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 3953 (~30.90 MiB) from the buffer pool
- reads: 5928 (~46.30 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Batched 46.027 ms
Time: 46.027 ms
- planning: 0.228 ms
- execution: 45.799 ms
- I/O read: 41.385 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 514 (~4.00 MiB) from the buffer pool
- reads: 491 (~3.80 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Index creation
creation takes 63.330 min, occupies 17 GB
CREATE INDEX CONCURRENTLY idx_audit_events_with_created_at
ON public.audit_events
USING btree (entity_id, entity_type, id DESC, author_id, created_at)Session: joe-bt844no350j13v068ut0
The query has been executed. Duration: 63.330 min (edited) Migration output
UP
== 20200903064431 AddCreatedAtIndexToAuditEvents: migrating ===================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:audit_events, [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"idx_audit_events_on_entity_id_desc_author_id_created_at", :algorithm=>:concurrently})
-> 0.0028s
-- add_index(:audit_events, [:entity_id, :entity_type, :id, :author_id, :created_at], {:order=>{:id=>:desc}, :name=>"idx_audit_events_on_entity_id_desc_author_id_created_at", :algorithm=>:concurrently})
-> 0.0062s
-- transaction_open?()
-> 0.0000s
-- indexes(:audit_events)
-> 0.0022s
-- remove_index(:audit_events, {:algorithm=>:concurrently, :name=>"index_audit_events_on_entity_id_entity_type_id_desc_author_id"})
-> 0.0032s
== 20200903064431 AddCreatedAtIndexToAuditEvents: migrated (0.0169s) ==========DOWN
== 20200903064431 AddCreatedAtIndexToAuditEvents: reverting ===================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:audit_events, [:entity_id, :entity_type, :id, :author_id], {:order=>{:id=>:desc}, :name=>"index_audit_events_on_entity_id_entity_type_id_desc_author_id", :algorithm=>:concurrently})
-> 0.0064s
-- add_index(:audit_events, [:entity_id, :entity_type, :id, :author_id], {:order=>{:id=>:desc}, :name=>"index_audit_events_on_entity_id_entity_type_id_desc_author_id", :algorithm=>:concurrently})
-> 0.1180s
-- transaction_open?()
-> 0.0000s
-- indexes(:audit_events)
-> 0.0028s
-- remove_index(:audit_events, {:algorithm=>:concurrently, :name=>"idx_audit_events_on_entity_id_desc_author_id_created_at"})
-> 0.0045s
== 20200903064431 AddCreatedAtIndexToAuditEvents: reverted (0.1329s) ==========Does 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
Mentions #1449 (closed)
Edited by Tan Le