Add index on vulnerability_feedback (finding_uuid)
What does this MR do and why?
This MR adds an index to finding_uuid
to speed up the query. Related to Add index on vulnerability_feedback (finding_uuid) (#361551 - closed).
Screenshots or screen recordings
Database lab
CREATE INDEX CONCURRENTLY "index_vulnerability_feedback_finding_uuid" ON "vulnerability_feedback" USING hash ("finding_uuid");
The query has been executed. Duration: 1.505 s
Before
explain select * from vulnerability_feedback where finding_uuid = '8a35a9b9-8308-50a6-be9a-41c1a4c467e4';
Gather (cost=1000.00..28799.15 rows=1 width=145) (actual time=40.061..47.513 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=6572
I/O Timings: read=0.000 write=0.000
-> Parallel Seq Scan on public.vulnerability_feedback (cost=0.00..27799.05 rows=1 width=145) (actual time=29.821..29.821 rows=0 loops=3)
Filter: (vulnerability_feedb
[...SKIP...]
Time: 49.484 ms
- planning: 1.892 ms
- execution: 47.592 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 6572 (~51.30 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
After
select * from vulnerability_feedback where finding_uuid = '8a35a9b9-8308-50a6-be9a-41c1a4c467e4';
Plan with execution:
Index Scan using index_vulnerability_feedback_finding_uuid on public.vulnerability_feedback (cost=0.00..3.02 rows=1 width=145) (actual time=0.028..0.028 rows=0 loops=1)
Index Cond: (vulnerability_feedback.finding_uuid = '8a35a9b9-8308-50a6-be9a-41c1a4c467e4'::uuid)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
Time: 1.769 ms
- planning: 1.688 ms
- execution: 0.081 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 2 (~16.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
migrate::up
== 20220506180411 AddIndexToVulnerabilityFeedbackFindingUuid: migrating =======
-- transaction_open?()
-> 0.0000s
-- index_exists?(:vulnerability_feedback, :finding_uuid, {:using=>:hash, :name=>"index_vulnerability_feedback_finding_uuid", :algorithm=>:concurrently})
-> 0.0102s
-- execute("SET statement_timeout TO 0")
-> 0.0004s
-- add_index(:vulnerability_feedback, :finding_uuid, {:using=>:hash, :name=>"index_vulnerability_feedback_finding_uuid", :algorithm=>:concurrently})
-> 0.0154s
-- execute("RESET statement_timeout")
-> 0.0005s
== 20220506180411 AddIndexToVulnerabilityFeedbackFindingUuid: migrated (0.0392s)
migrate::down
== 20220506180411 AddIndexToVulnerabilityFeedbackFindingUuid: reverting =======
-- transaction_open?()
-> 0.0000s
-- index_exists?(:vulnerability_feedback, :finding_uuid, {:using=>:hash, :name=>"index_vulnerability_feedback_finding_uuid", :algorithm=>:concurrently})
-> 0.0076s
-- execute("SET statement_timeout TO 0")
-> 0.0003s
-- remove_index(:vulnerability_feedback, {:using=>:hash, :name=>"index_vulnerability_feedback_finding_uuid", :algorithm=>:concurrently, :column=>:finding_uuid})
-> 0.0400s
-- execute("RESET statement_timeout")
-> 0.0007s
== 20220506180411 AddIndexToVulnerabilityFeedbackFindingUuid: reverted (0.0576s)
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Stan Hu