Add index to support epics filtering by award emoji
What does this MR do?
Improve query to filter epics without a given award emoji. This work is supposed to support the query executed once !57001 (merged) gets merged.
Migration output
== 20210329192716 AddCompositeIndexToAwardEmoji: migrating ====================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:award_emoji, [:user_id, :name, :awardable_type, :awardable_id], {:name=>"idx_award_emoji_on_user_emoji_name_awardable_type_awardable_id", :algorithm=>:concurrently})
-> 0.0022s
-- execute("SET statement_timeout TO 0")
-> 0.0004s
-- add_index(:award_emoji, [:user_id, :name, :awardable_type, :awardable_id], {:name=>"idx_award_emoji_on_user_emoji_name_awardable_type_awardable_id", :algorithm=>:concurrently})
-> 0.0097s
-- execute("RESET ALL")
-> 0.0007s
== 20210329192716 AddCompositeIndexToAwardEmoji: migrated (0.0139s) ===========
== 20210331180118 RemoveDeprecatedIndexFromAwardEmoji: migrating ==============
-- transaction_open?()
-> 0.0000s
-- indexes(:award_emoji)
-> 0.0018s
-- remove_index(:award_emoji, {:algorithm=>:concurrently, :name=>"index_award_emoji_on_user_id_and_name"})
-> 0.0040s
== 20210331180118 RemoveDeprecatedIndexFromAwardEmoji: migrated (0.0066s) =====SQL Query
-
This query will only be executed by epic boards with 20 LIMIT, however it can be executed via GraphQL with max 100 LIMIT.
So we are using the worst-case scenario to measure:100 LIMITforgitlab-orggroup. Also the same query which is used to filter issues by reaction emoji could take benefit from the index added here. -
Timings were taken from database-lab without warming up caches, they can be higher than the production environment.
SELECT "epics".*
FROM "epics"
LEFT OUTER JOIN "boards_epic_board_positions"
ON "boards_epic_board_positions"."epic_id" = "epics"."id"
WHERE "epics"."group_id" IN (WITH recursive "base_and_descendants" AS (
(
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND "epics"."state_id" = 1
AND NOT (
EXISTS
(
SELECT true
FROM "award_emoji"
WHERE "award_emoji"."user_id" = 426128
AND "award_emoji"."awardable_type" = 'Epic'
AND "award_emoji"."awardable_id" = "epics"."id"
AND "award_emoji"."name" = 'thumbsup'))
AND (
"boards_epic_board_positions"."epic_board_id" = 4
OR "boards_epic_board_positions"."epic_board_id" IS NULL)
ORDER BY boards_epic_board_positions.relative_position ASC nulls last,
epics.id DESC limit 100Query plan and timings WITHOUT INDEX
Limit (cost=4318.95..4318.98 rows=13 width=1205) (actual time=8209.075..8209.127 rows=100 loops=1)
Buffers: shared hit=15113 read=8473 dirtied=256
I/O Timings: read=7965.406
-> Sort (cost=4318.95..4318.98 rows=13 width=1205) (actual time=8209.073..8209.097 rows=100 loops=1)
Sort Key: boards_epic_board_positions.relative_position, epics.id DESC
Sort Method: top-N heapsort Memory: 212kB
Buffers: shared hit=15113 read=8473 dirtied=256
I/O Timings: read=7965.406
-> Nested Loop Anti Join (cost=4231.08..4318.71 rows=13 width=1205) (actual time=4559.970..8195.658 rows=3871 loops=1)
Buffers: shared hit=15110 read=8473 dirtied=256
I/O Timings: read=7965.406
-> Hash Right Join (cost=4230.65..4286.40 rows=13 width=1205) (actual time=4530.884..4540.380 rows=3871 loops=1)
Hash Cond: (boards_epic_board_positions.epic_id = epics.id)
Filter: ((boards_epic_board_positions.epic_board_id = 4) OR (boards_epic_board_positions.epic_board_id IS NULL))
Rows Removed by Filter: 0
Buffers: shared hit=3348 read=4605 dirtied=242
I/O Timings: read=4408.664
-> Seq Scan on public.boards_epic_board_positions (cost=0.00..51.30 rows=1130 width=20) (actual time=0.007..0.008 rows=0 loops=1)
-> Hash (cost=4214.72..4214.72 rows=1274 width=1201) (actual time=4530.844..4530.854 rows=3871 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 3566kB
Buffers: shared hit=3348 read=4605 dirtied=242
I/O Timings: read=4408.664
-> Nested Loop (cost=1432.83..4214.72 rows=1274 width=1201) (actual time=602.633..4505.722 rows=3871 loops=1)
Buffers: shared hit=3348 read=4605 dirtied=242
I/O Timings: read=4408.664
-> HashAggregate (cost=1432.41..1434.12 rows=171 width=4) (actual time=595.356..595.671 rows=233 loops=1)
Group Key: namespaces.id
Buffers: shared hit=760 read=401 dirtied=2
I/O Timings: read=580.870
-> CTE Scan on base_and_descendants namespaces (cost=1426.85..1430.27 rows=171 width=4) (actual time=6.980..594.746 rows=233 loops=1)
Buffers: shared hit=760 read=401 dirtied=2
I/O Timings: read=580.870
CTE base_and_descendants
-> Recursive Union (cost=0.43..1426.85 rows=171 width=344) (actual time=6.971..593.299 rows=233 loops=1)
Buffers: shared hit=760 read=401 dirtied=2
I/O Timings: read=580.870
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=6.955..6.958 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 9970))
Buffers: shared read=4
I/O Timings: read=6.877
-> Nested Loop (cost=0.56..142.00 rows=17 width=344) (actual time=5.327..97.271 rows=39 loops=6)
Buffers: shared hit=760 read=397 dirtied=2
I/O Timings: read=573.993
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.043 rows=39 loops=6)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..14.16 rows=2 width=344) (actual time=1.411..2.498 rows=1 loops=233)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=760 read=397 dirtied=2
I/O Timings: read=573.993
-> Index Scan using index_epics_on_group_id on public.epics (cost=0.42..16.19 rows=7 width=1201) (actual time=0.705..16.742 rows=17 loops=233)
Index Cond: (epics.group_id = namespaces.id)
Filter: (epics.state_id = 1)
Rows Removed by Filter: 9
Buffers: shared hit=2588 read=4204 dirtied=240
I/O Timings: read=3827.794
-> Index Scan using index_award_emoji_on_awardable_type_and_awardable_id on public.award_emoji (cost=0.43..2.34 rows=1 width=4) (actual time=0.942..0.942 rows=0 loops=3871)
Index Cond: (((award_emoji.awardable_type)::text = 'Epic'::text) AND (award_emoji.awardable_id = epics.id))
Filter: ((award_emoji.user_id = 426128) AND ((award_emoji.name)::text = 'thumbsup'::text))
Rows Removed by Filter: 1
Buffers: shared hit=11762 read=3868 dirtied=14
I/O Timings: read=3556.742Time: 8.219 s
- planning: 8.755 ms
- execution: 8.210 s
- I/O read: 7.965 s
- I/O write: N/A
Shared buffers:
- hits: 15113 (~118.10 MiB) from the buffer pool
- reads: 8473 (~66.20 MiB) from the OS file cache, including disk I/O
- dirtied: 256 (~2.00 MiB)
- writes: 0Query plan and timings WITH INDEX
Limit (cost=4290.42..4290.45 rows=13 width=1205) (actual time=4266.384..4266.419 rows=100 loops=1)
Buffers: shared hit=3351 read=4609 dirtied=242
I/O Timings: read=4147.863
-> Sort (cost=4290.42..4290.45 rows=13 width=1205) (actual time=4266.382..4266.403 rows=100 loops=1)
Sort Key: boards_epic_board_positions.relative_position, epics.id DESC
Sort Method: top-N heapsort Memory: 212kB
Buffers: shared hit=3351 read=4609 dirtied=242
I/O Timings: read=4147.863
-> Nested Loop Anti Join (cost=4231.21..4290.18 rows=13 width=1205) (actual time=4257.541..4262.946 rows=3871 loops=1)
Buffers: shared hit=3348 read=4609 dirtied=242
I/O Timings: read=4147.863
-> Hash Right Join (cost=4230.65..4286.40 rows=13 width=1205) (actual time=4257.253..4260.582 rows=3871 loops=1)
Hash Cond: (boards_epic_board_positions.epic_id = epics.id)
Filter: ((boards_epic_board_positions.epic_board_id = 4) OR (boards_epic_board_positions.epic_board_id IS NULL))
Rows Removed by Filter: 0
Buffers: shared hit=3348 read=4605 dirtied=242
I/O Timings: read=4147.619
-> Seq Scan on public.boards_epic_board_positions (cost=0.00..51.30 rows=1130 width=20) (actual time=0.008..0.009 rows=0 loops=1)
-> Hash (cost=4214.72..4214.72 rows=1274 width=1201) (actual time=4257.218..4257.228 rows=3871 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 3566kB
Buffers: shared hit=3348 read=4605 dirtied=242
I/O Timings: read=4147.619
-> Nested Loop (cost=1432.83..4214.72 rows=1274 width=1201) (actual time=563.662..4234.031 rows=3871 loops=1)
Buffers: shared hit=3348 read=4605 dirtied=242
I/O Timings: read=4147.619
-> HashAggregate (cost=1432.41..1434.12 rows=171 width=4) (actual time=557.926..558.244 rows=233 loops=1)
Group Key: namespaces.id
Buffers: shared hit=760 read=401 dirtied=2
I/O Timings: read=545.746
-> CTE Scan on base_and_descendants namespaces (cost=1426.85..1430.27 rows=171 width=4) (actual time=11.605..557.374 rows=233 loops=1)
Buffers: shared hit=760 read=401 dirtied=2
I/O Timings: read=545.746
CTE base_and_descendants
-> Recursive Union (cost=0.43..1426.85 rows=171 width=344) (actual time=11.598..556.301 rows=233 loops=1)
Buffers: shared hit=760 read=401 dirtied=2
I/O Timings: read=545.746
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=11.577..11.580 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 9970))
Buffers: shared read=4
I/O Timings: read=11.484
-> Nested Loop (cost=0.56..142.00 rows=17 width=344) (actual time=3.632..90.344 rows=39 loops=6)
Buffers: shared hit=760 read=397 dirtied=2
I/O Timings: read=534.262
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.033 rows=39 loops=6)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..14.16 rows=2 width=344) (actual time=1.205..2.320 rows=1 loops=233)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=760 read=397 dirtied=2
I/O Timings: read=534.262
-> Index Scan using index_epics_on_group_id on public.epics (cost=0.42..16.19 rows=7 width=1201) (actual time=0.840..15.735 rows=17 loops=233)
Index Cond: (epics.group_id = namespaces.id)
Filter: (epics.state_id = 1)
Rows Removed by Filter: 9
Buffers: shared hit=2588 read=4204 dirtied=240
I/O Timings: read=3601.873
-> Materialize (cost=0.56..3.58 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=3871)
Buffers: shared read=4
I/O Timings: read=0.244
-> Index Only Scan using index_award_emoji_on_user_id_and_name_and_awardable_type_and_aw on public.award_emoji (cost=0.56..3.58 rows=1 width=4) (actual time=0.281..0.281 rows=0 loops=1)
Index Cond: ((award_emoji.user_id = 426128) AND (award_emoji.name = 'thumbsup'::text) AND (award_emoji.awardable_type = 'Epic'::text))
Heap Fetches: 0
Buffers: shared read=4
I/O Timings: read=0.244Time: 4.275 s
- planning: 8.024 ms
- execution: 4.267 s
- I/O read: 4.148 s
- I/O write: N/A
Shared buffers:
- hits: 3351 (~26.20 MiB) from the buffer pool
- reads: 4609 (~36.00 MiB) from the OS file cache, including disk I/O
- dirtied: 242 (~1.90 MiB)
- writes: 0Edited by Felipe Artur