Skip to content

Improve usage ping metrics calculations with new index added to security scans table

Mikołaj Wawrzyniak requested to merge mwaw/add_index_to_security_scans_on_created_at_and_build_id into master

What does this MR do?

It is a follow up to discussion !48233 (comment 456059839)

It adds dedicated index on date(timezone('UTC', created_at)), id to security_scans to improve MIN/MAX query calculations for usage ping metrics

Database labs checks

Index creation (in migration concurrent index creation is used, so execution time shouldn't be an issue)

exec CREATE INDEX index_security_scans_on_created_at_and_id ON security_scans USING btree (date(timezone('UTC', created_at)), id);
Session: 1759
The query has been executed. Duration: 9.266 s

Without index on created_at

MIN query: https://explain.depesz.com/s/mvt6 MAX query: https://explain.depesz.com/s/pv81

With new index

MIN query:

SELECT 
  (SELECT "security_scans"."id" FROM "security_scans" WHERE DATE(TIMEZONE('UTC', date_range_source)) = DATE(TIMEZONE('UTC', "security_scans"."created_at")) ORDER BY "security_scans"."id" ASC LIMIT 1) 
FROM generate_series('2020-12-20 17:20:16'::timestamp, '2021-01-17 17:20:16'::timestamp, '1 day'::interval) date_range_source 
ORDER BY 1 ASC NULLS LAST 
LIMIT 1

https://explain.depesz.com/s/iPiW

MAX query:

SELECT 
(SELECT "security_scans"."id" FROM "security_scans" WHERE DATE(TIMEZONE('UTC', date_range_source)) = DATE(TIMEZONE('UTC', "security_scans"."created_at")) ORDER BY "security_scans"."id" DESC LIMIT 1) 
FROM generate_series('2020-12-20 17:20:16'::timestamp, '2021-01-17 17:20:16'::timestamp, '1 day'::interval) date_range_source 
ORDER BY 1 DESC NULLS LAST 
LIMIT 1

https://explain.depesz.com/s/cShC

Does this MR meet the acceptance criteria?

Conformity

Edited by Mikołaj Wawrzyniak

Merge request reports