Skip to content

Add author_id index to audit_events

Robert Hunt requested to merge 222639-follow-up-add-author_id-index-to-audit_events into master

What does this MR do?

Adds a new index to audit_events so we can query author_id in relation to the entity_id and entity_type in a more performant manner.

This was raised because we're starting to query author_id in relation to Group and Project level events on their respective audit log pages.

This also removes the older index as the two were overlapping and each index is pretty hefty 🙈. This overlap was found during MR review discussions and we determined that this newer index meets the requirements of the old one with the added bonus of also capturing author_id.

Database

Running the following query pre and post-index:

SELECT
    "audit_events".*
FROM
    "audit_events"
WHERE
    "audit_events"."entity_type" = 'Group'
    AND "audit_events"."entity_id" = 9970
    AND "audit_events"."author_id" = 5463194
ORDER BY
    "audit_events"."id" DESC
LIMIT 26 OFFSET 0

All tests were carried out using #database-lab

Pre-index:

Plan

 Limit  (cost=0.57..26.70 rows=1 width=224) (actual time=283.371..471.456 rows=26 loops=1)
   Buffers: shared hit=9 read=133 dirtied=64
   I/O Timings: read=399.632
   ->  Index Scan using index_audit_events_on_entity_id_and_entity_type_and_id_desc on public.audit_events  (cost=0.57..26.70 rows=1 width=224) (actual time=283.369..471.424 rows=26 loops=1)
         Index Cond: ((audit_events.entity_id = 9970) AND ((audit_events.entity_type)::text = 'Group'::text))
         Filter: (audit_events.author_id = 5463194)
         Rows Removed by Filter: 116
         Buffers: shared hit=9 read=133 dirtied=64
         I/O Timings: read=399.632

Summary

Time: 471.654 ms
  - planning: 0.149 ms
  - execution: 471.505 ms
    - I/O read: 399.632 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 9 (~72.00 KiB) from the buffer pool
  - reads: 133 (~1.00 MiB) from the OS file cache, including disk I/O
  - dirtied: 64 (~512.00 KiB)
  - writes: 0

Index being added:

 
CREATE INDEX index_on_author_id_and_entity_id_and_entity_type_and_id_desc ON public.audit_events USING btree (entity_id, entity_type, id DESC, author_id);

Time taken: 45.989 min

Post-index:

Plan

 Limit  (cost=0.57..3.81 rows=1 width=237) (actual time=11.962..33.049 rows=26 loops=1)
   Buffers: shared hit=1 read=28 dirtied=1
   I/O Timings: read=29.883
   ->  Index Scan using index_audit_events_toon on public.audit_events  (cost=0.57..3.81 rows=1 width=237) (actual time=11.960..33.035 rows=26 loops=1)
         Index Cond: ((audit_events.entity_id = 9970) AND ((audit_events.entity_type)::text = 'Group'::text) AND (audit_events.author_id = 5463194))
         Buffers: shared hit=1 read=28 dirtied=1
         I/O Timings: read=29.883

Summary

Time: 33.329 ms
  - planning: 0.224 ms
  - execution: 33.105 ms
    - I/O read: 29.883 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 28 (~224.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 1 (~8.00 KiB)
  - writes: 0

Outcome:
  • ~93% decrease in total time
  • ~88.8% decrease in buffer hits
  • ~79% decrease in buffer reads
  • ~98.4% decrease in dirtied buffers

Does this MR meet the acceptance criteria?

Conformity

Closes #222639 (closed)

Edited by Robert Hunt

Merge request reports