WIP: Instrument time period for usage ping metric incident_labeled_issues
What does this MR do?
Counts the number of issues created from alerts within a week.
database query plans with created_at
SQL queries produced by
Gitlab::UsageData.incident_labeled_issues_usage_data(1.week.ago)
SELECT MIN("label_links"."id") FROM "label_links" WHERE "label_links"."target_type" = 'Issue' AND (label_links.created_at >= '2020-06-24 11:07:02.292600')
SELECT MAX("label_links"."id") FROM "label_links" WHERE "label_links"."target_type" = 'Issue' AND (label_links.created_at >= '2020-06-24 11:07:02.292600')
explain SELECT COUNT("label_links"."id") FROM "label_links" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "label_links"."target_type" = 'Issue' AND "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "label_links"."id" BETWEEN 2295287 AND 2395287;
SELECT MIN
🐢
Takes ~10s on GitLab.com with a cold cache (first query) cache
Time: 10.625 s
- planning: 1.491 ms
- execution: 10.624 s
- I/O read: 10.384 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 29400 (~229.70 MiB) from the buffer pool
- reads: 5340 (~41.70 MiB) from the OS file cache, including disk I/O
- dirtied: 1 (~8.00 KiB)
- writes: 0
Click to expand
Query:
SELECT MIN("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678'
Plan: https://explain.depesz.com/s/V2eK
❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.
❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).
SELECT MAX
🏃
Takes only 39ms on GitLab.com with a warm (due to previous query) cache
Time: 39.543 ms
- planning: 1.328 ms
- execution: 38.215 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 34737 (~271.40 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Click to expand
Query:
SELECT MAX("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678'
Plan: https://explain.depesz.com/s/9Eyf
❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.
❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).
SELECT COUNT
🏃
Takes only 39ms on GitLab.com with a warm (due to previous query) cache
Time: 24.091 ms
- planning: 1.400 ms
- execution: 22.691 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 5863 (~45.80 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Click to expand
Query:
SELECT COUNT("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678' AND "issues"."id" BETWEEN 0 AND 99999
Plan: https://explain.depesz.com/s/y7lX
❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.
❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).
database query plans without created_at
Previous
SELECT MIN
🐢
Takes ~13s on GitLab.com with a cold cache (first query) cache
Time: 13.344 s
- planning: 1.190 ms
- execution: 13.342 s
- I/O read: 12.811 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 24167 (~188.80 MiB) from the buffer pool
- reads: 8799 (~68.70 MiB) from the OS file cache, including disk I/O
- dirtied: 420 (~3.30 MiB)
- writes: 0
Click to expand
Query:
SELECT MIN("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678'
Plan: https://explain.depesz.com/s/ruRi
❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.
❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).
❗ VACUUM ANALYZE needed – Visibility map(s) for the table(s) involved in the query are outdated. For better performance: 1) run VACUUM ANALYZE on them as a one-time solution, 2) perform autovacuum tuning as a long-term permanent solution (tuning "vacuum" part of autovacuum).
SELECT MAX
🏃
Takes only 41ms on GitLab.com with a warm (due to previous query) cache
Time: 41.887 ms
- planning: 1.745 ms
- execution: 40.142 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 32966 (~257.50 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Click to expand
Query:
SELECT MAX("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678'
Plan: https://explain.depesz.com/s/9b5C
❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.
❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).
❗ VACUUM ANALYZE needed – Visibility map(s) for the table(s) involved in the query are outdated. For better performance: 1) run VACUUM ANALYZE on them as a one-time solution, 2) perform autovacuum tuning as a long-term permanent solution (tuning "vacuum" part of autovacuum).
SELECT COUNT
🏃
Takes only 13ms on GitLab.com with a warm (due to previous query) cache
Time: 13.425 ms
- planning: 1.215 ms
- execution: 12.210 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 5863 (~45.80 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Click to expand
Query:
SELECT COUNT("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678' AND "issues"."id" BETWEEN 0 AND 99999
Plan: https://explain.depesz.com/s/vDap
❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.
❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).
database query plans
OldClick to unfold
explain SELECT COUNT(“issues”.“id”) FROM “issues” INNER JOIN “label_links” ON “label_links”.“target_type” = ‘Issue’ AND “label_links”.“target_id” = “issues”.“id” INNER JOIN “labels” ON “labels”.“id” = “label_links”.“label_id” WHERE “labels”.“title” = ‘incident’ AND “labels”.“color” = ‘#CC0033’ AND “labels”.“description” = ‘Denotes a disruption to IT services and the associated issues require immediate attention’ AND “issues”.“created_at” >= ‘2020-06-04 19:48:44.608519’ AND “issues”.“id” BETWEEN 0 AND 99999
Time: 1.590 s
- planning: 1.038 ms
- execution: 1.589 s
- I/O read: 1.557 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 355 (~2.80 MiB) from the buffer pool
- reads: 5444 (~42.50 MiB) from the OS file cache, including disk I/O
- dirtied: 168 (~1.30 MiB)
- writes: 0
Aggregate (cost=149.70..149.71 rows=1 width=8) (actual time=1589.349..1589.350 rows=1 loops=1)
Buffers: shared hit=355 read=5444 dirtied=168
I/O Timings: read=1557.370
-> Nested Loop (cost=1.56..149.70 rows=1 width=4) (actual time=1589.345..1589.345 rows=0 loops=1)
Buffers: shared hit=355 read=5444 dirtied=168
I/O Timings: read=1557.370
-> Nested Loop (cost=1.00..147.57 rows=3 width=4) (actual time=21.955..1582.595 rows=5704 loops=1)
Buffers: shared hit=355 read=5444 dirtied=168
I/O Timings: read=1557.370
-> Index Scan using index_labels_on_title on public.labels (cost=0.43..68.92 rows=1 width=4) (actual time=8.431..257.055 rows=61 loops=1)
Index Cond: ((labels.title)::text = 'incident'::text)
Filter: (((labels.color)::text = '#CC0033'::text) AND ((labels.description)::text = 'Denotes a disruption to IT services and the associated issues require immediate attention'::text))
Rows Removed by Filter: 72
Buffers: shared read=137 dirtied=6
I/O Timings: read=255.549
-> Index Scan using index_label_links_on_label_id on public.label_links (cost=0.56..77.21 rows=144 width=8) (actual time=3.342..21.693 rows=94 loops=61)
Index Cond: (label_links.label_id = labels.id)
Filter: ((label_links.target_type)::text = 'Issue'::text)
Rows Removed by Filter: 0
Buffers: shared hit=355 read=5307 dirtied=162
I/O Timings: read=1301.820
-> Index Scan using issues_pkey on public.issues (cost=0.56..0.71 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=5704)
Index Cond: ((issues.id = label_links.target_id) AND (issues.id >= 0) AND (issues.id <= 99999))
Filter: (issues.created_at >= '2020-06-04 19:48:44.608519+00'::timestamp with time zone)
Rows Removed by Filter: 0
--------------------------------------------------------------------------------------
explain SELECT MAX("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-04 19:48:44.608519'
Time: 8.162 s
- planning: 0.917 ms
- execution: 8.161 s
- I/O read: 8.022 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 25900 (~202.30 MiB) from the buffer pool
- reads: 8456 (~66.10 MiB) from the OS file cache, including disk I/O
- dirtied: 64 (~512.00 KiB)
- writes: 0
Aggregate (cost=149.69..149.70 rows=1 width=4) (actual time=8160.579..8160.580 rows=1 loops=1)
Buffers: shared hit=25900 read=8456 dirtied=64
I/O Timings: read=8022.372
-> Nested Loop (cost=1.56..149.69 rows=1 width=4) (actual time=15.087..8160.403 rows=117 loops=1)
Buffers: shared hit=25900 read=8456 dirtied=64
I/O Timings: read=8022.372
-> Nested Loop (cost=1.00..147.57 rows=3 width=4) (actual time=0.046..29.460 rows=5704 loops=1)
Buffers: shared hit=5799
-> Index Scan using index_labels_on_title on public.labels (cost=0.43..68.92 rows=1 width=4) (actual time=0.031..0.540 rows=61 loops=1)
Index Cond: ((labels.title)::text = 'incident'::text)
Filter: (((labels.color)::text = '#CC0033'::text) AND ((labels.description)::text = 'Denotes a disruption to IT services and the associated issues require immediate attention'::text))
Rows Removed by Filter: 72
Buffers: shared hit=137
-> Index Scan using index_label_links_on_label_id on public.label_links (cost=0.56..77.21 rows=144 width=8) (actual time=0.013..0.418 rows=94 loops=61)
Index Cond: (label_links.label_id = labels.id)
Filter: ((label_links.target_type)::text = 'Issue'::text)
Rows Removed by Filter: 0
Buffers: shared hit=5662
-> Index Scan using issues_pkey on public.issues (cost=0.56..0.70 rows=1 width=4) (actual time=1.423..1.423 rows=0 loops=5704)
Index Cond: (issues.id = label_links.target_id)
Filter: (issues.created_at >= '2020-06-04 19:48:44.608519+00'::timestamp with time zone)
Rows Removed by Filter: 1
Buffers: shared hit=20101 read=8456 dirtied=64
I/O Timings: read=8022.372
---------------------------------------------------------------------------------------
explain SELECT MIN("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-04 19:48:44.608519'
Time: 32.359 ms
- planning: 0.979 ms
- execution: 31.380 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 34353 (~268.40 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Aggregate (cost=149.69..149.70 rows=1 width=4) (actual time=31.302..31.302 rows=1 loops=1)
Buffers: shared hit=34353
-> Nested Loop (cost=1.56..149.69 rows=1 width=4) (actual time=0.107..31.275 rows=117 loops=1)
Buffers: shared hit=34353
-> Nested Loop (cost=1.00..147.57 rows=3 width=4) (actual time=0.052..8.671 rows=5704 loops=1)
Buffers: shared hit=5799
-> Index Scan using index_labels_on_title on public.labels (cost=0.43..68.92 rows=1 width=4) (actual time=0.032..0.232 rows=61 loops=1)
Index Cond: ((labels.title)::text = 'incident'::text)
Filter: (((labels.color)::text = '#CC0033'::text) AND ((labels.description)::text = 'Denotes a disruption to IT services and the associated issues require immediate attention'::text))
Rows Removed by Filter: 72
Buffers: shared hit=137
-> Index Scan using index_label_links_on_label_id on public.label_links (cost=0.56..77.21 rows=144 width=8) (actual time=0.006..0.119 rows=94 loops=61)
Index Cond: (label_links.label_id = labels.id)
Filter: ((label_links.target_type)::text = 'Issue'::text)
Rows Removed by Filter: 0
Buffers: shared hit=5662
-> Index Scan using issues_pkey on public.issues (cost=0.56..0.70 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=5704)
Index Cond: (issues.id = label_links.target_id)
Filter: (issues.created_at >= '2020-06-04 19:48:44.608519+00'::timestamp with time zone)
Rows Removed by Filter: 1
Buffers: shared hit=28554
Collapse