Skip to content

WIP: Exclude instance services from usage ping data

Arturo Herrero requested to merge exclude-instance-service-from-usage-ping-data into master

What does this MR do?

Instance-level services are not in use at the moment but we want to prevent sending usage ping data related to them.

Note: This merge request is targeting !25714 (merged) but the idea is to target master when !25714 (merged) get merged.

In order to exclude instance-level services, we have two options producing different queries:

Option 1

scope :for_projects, -> { where(template: false, instance: false) }
SELECT
    COUNT(*) AS count_all,
    "services"."type" AS services_type
FROM
    "services"
WHERE
    "services"."active" = TRUE
    AND "services"."template" = FALSE
    AND "services"."instance" = FALSE
    AND "services"."type" != 'JiraService'
GROUP BY
    "services"."type";

EXPLAIN 

HashAggregate  (cost=207406.73..207407.10 rows=37 width=32) (actual time=3833.062..3833.072 rows=38 loops=1)
   Group Key: services.type
   Buffers: shared hit=2240 read=87698
   I/O Timings: read=2392.551
   ->  Index Scan using index_services_on_instance on public.services  (cost=0.43..206244.39 rows=232469 width=24) (actual time=0.374..3661.936 rows=434028 loops=1)
         Index Cond: (services.instance = false)
         Filter: (services.active AND (NOT services.template) AND (NOT services.instance) AND ((services.type)::text <> 'JiraService'::text))
         Rows Removed by Filter: 3189568
         Buffers: shared hit=2240 read=87698
         I/O Timings: read=2392.551

Time: 3.833 s
  - planning: 0.163 ms
  - execution: 3.833 s
    - I/O read: 2.393 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2240 (~17.50 MiB) from the buffer pool
  - reads: 87698 (~685.10 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Option 2 (used in this merge request)

scope :for_projects, -> { where.not(project: nil) }
SELECT
    COUNT(*) AS count_all,
    "services"."type" AS services_type
FROM
    "services"
WHERE
    "services"."active" = TRUE
    AND "services"."project_id" IS NOT NULL
    AND "services"."type" != 'JiraService'
GROUP BY
    "services"."type"

EXPLAIN 

HashAggregate  (cost=291616.43..291616.80 rows=37 width=32) (actual time=3479.284..3479.292 rows=38 loops=1)
   Group Key: services.type
   Buffers: shared hit=2791113 read=9903
   I/O Timings: read=340.536
   ->  Index Scan using index_services_on_project_id on public.services  (cost=0.43..289291.76 rows=464935 width=24) (actual time=0.271..3343.549 rows=434028 loops=1)
         Index Cond: (services.project_id IS NOT NULL)
         Filter: (services.active AND ((services.type)::text <> 'JiraService'::text))
         Rows Removed by Filter: 3189498
         Buffers: shared hit=2791113 read=9903
         I/O Timings: read=340.536

Time: 3.480 s
  - planning: 0.197 ms
  - execution: 3.479 s
    - I/O read: 340.536 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2791113 (~21.30 GiB) from the buffer pool
  - reads: 9903 (~77.40 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
Edited by 🤖 GitLab Bot 🤖

Merge request reports