Add author_id index to audit_events
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 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