Add missing issue due date filters
What does this MR do and why?
Adds any, today, and tomorrow issue due_date filters to API.
MR created during pairing session with @espadav8 - thanks Andrew
DB
There are identical and more complex plans for existing queries so we should be good
Details for searching all issues in a project
SELECT COUNT(*) FROM "issues" WHERE "issues"."project_id" = 6 AND "issues"."due_date" = '2022-01-22'
Aggregate (cost=3.45..3.46 rows=1 width=8) (actual time=0.041..0.041 rows=1 loops=1)
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using idx_issues_on_project_id_and_due_date_and_id_and_state_id on public.issues (cost=0.43..3.45 rows=1 width=0) (actual time=0.036..0.037 rows=0 loops=1)
Index Cond: ((issues.project_id = 6) AND (issues.due_date = '2022-01-22'::date))
Heap Fetches: 0
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
cold:
Time: 1.922 ms
- planning: 0.343 ms
- execution: 1.579 ms
- I/O read: 1.466 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 1 (~8.00 KiB) from the buffer pool
- reads: 2 (~16.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
warm:
Time: 0.371 ms
- planning: 0.290 ms
- execution: 0.081 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 3 (~24.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
SELECT "issues".* FROM "issues" WHERE "issues"."project_id" = 6 AND "issues"."due_date" = '2022-01-22' ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 20 OFFSET 0
Limit (cost=3.46..3.46 rows=1 width=1417) (actual time=0.042..0.043 rows=0 loops=1)
Buffers: shared hit=9
I/O Timings: read=0.000 write=0.000
-> Sort (cost=3.46..3.46 rows=1 width=1417) (actual time=0.040..0.041 rows=0 loops=1)
Sort Key: issues.created_at DESC, issues.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=9
I/O Timings: read=0.000 write=0.000
-> Index Scan using idx_issues_on_project_id_and_due_date_and_id_and_state_id on public.issues (cost=0.43..3.45 rows=1 width=1417) (actual time=0.020..0.020 rows=0 loops=1)
Index Cond: ((issues.project_id = 6) AND (issues.due_date = '2022-01-22'::date))
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
Time: 2.752 ms
- planning: 2.664 ms
- execution: 0.088 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 9 (~72.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Details for searching all issues in a group
explain SELECT COUNT(*) FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970)
UNION
(SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" 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 (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "projects"."archived" = FALSE AND "issues"."due_date" = '2022-01-22'
Aggregate (cost=24916.79..24916.80 rows=1 width=8) (actual time=26.771..26.778 rows=1 loops=1)
Buffers: shared hit=17208
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=1553.97..24916.79 rows=1 width=0) (actual time=16.673..26.770 rows=4 loops=1)
Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
Rows Removed by Filter: 0
Buffers: shared hit=17208
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1553.54..24916.29 rows=1 width=4) (actual time=16.648..26.714 rows=4 loops=1)
Buffers: shared hit=17192
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1553.11..23099.06 rows=3883 width=4) (actual time=3.284..20.179 rows=1641 loops=1)
Buffers: shared hit=12264
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=1552.54..1554.54 rows=200 width=4) (actual time=3.202..3.290 rows=292 loops=1)
Group Key: namespaces.id
Buffers: shared hit=1519
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_and_descendants namespaces (cost=1546.01..1550.03 rows=201 width=4) (actual time=0.047..3.088 rows=292 loops=1)
Buffers: shared hit=1519
I/O Timings: read=0.000 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=0.43..1546.01 rows=201 width=358) (actual time=0.045..2.645 rows=292 loops=1)
Buffers: shared hit=1519
I/O Timings: read=0.000 write=0.000
-> Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.46 rows=1 width=358) (actual time=0.037..0.039 rows=1 loops=1)
Index Cond: (namespaces_1.id = 9970)
Filter: ((namespaces_1.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.56..153.85 rows=20 width=358) (actual time=0.019..0.323 rows=48 loops=6)
Buffers: shared hit=1515
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.005 rows=49 loops=6)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.35 rows=2 width=358) (actual time=0.004..0.006 rows=1 loops=292)
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=1515
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.56..107.53 rows=19 width=8) (actual time=0.013..0.057 rows=6 loops=292)
Index Cond: (projects.namespace_id = namespaces.id)
Filter: ((NOT projects.archived) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{0,10,20}'::integer[]))))
Rows Removed by Filter: 0
Buffers: shared hit=10745
I/O Timings: read=0.000 write=0.000
SubPlan 1
-> Index Only Scan using index_project_authorizations_on_project_id_user_id on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=1641)
Index Cond: ((project_authorizations.project_id = projects.id) AND (project_authorizations.user_id = 1))
Heap Fetches: 164
Buffers: shared hit=7812
I/O Timings: read=0.000 write=0.000
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..228.48 rows=6448 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (project_authorizations_1.user_id = 1)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using idx_issues_on_project_id_and_due_date_and_id_and_state_id on public.issues (cost=0.43..0.46 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1641)
Index Cond: ((issues.project_id = projects.id) AND (issues.due_date = '2022-01-22'::date))
Heap Fetches: 1
Buffers: shared hit=4928
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.48 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=4)
Index Cond: (project_features.project_id = projects.id)
Buffers: shared hit=16
I/O Timings: read=0.000 write=0.000
cold:
Time: 6.065 s
- planning: 2.207 ms
- execution: 6.063 s
- I/O read: 6.000 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 10168 (~79.40 MiB) from the buffer pool
- reads: 7051 (~55.10 MiB) from the OS file cache, including disk I/O
- dirtied: 105 (~840.00 KiB)
- writes: 0
warm:
Time: 29.307 ms
- planning: 2.215 ms
- execution: 27.092 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 17208 (~134.40 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 26)
UNION
(SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" 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 (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "projects"."archived" = FALSE AND "issues"."due_date" = '2022-01-22' ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 20 OFFSET 0
Limit (cost=26667.57..26667.57 rows=1 width=1417) (actual time=0.083..0.086 rows=0 loops=1)
Buffers: shared hit=10
I/O Timings: read=0.000 write=0.000
-> Sort (cost=26667.57..26667.57 rows=1 width=1417) (actual time=0.082..0.084 rows=0 loops=1)
Sort Key: issues.created_at DESC, issues.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=10
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=1553.97..26667.56 rows=1 width=1417) (actual time=0.026..0.028 rows=0 loops=1)
Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
Rows Removed by Filter: 0
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1553.54..26667.06 rows=1 width=1421) (actual time=0.025..0.027 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1553.11..23099.06 rows=3883 width=4) (actual time=0.025..0.027 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=1552.54..1554.54 rows=200 width=4) (actual time=0.025..0.026 rows=0 loops=1)
Group Key: namespaces.id
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_and_descendants namespaces (cost=1546.01..1550.03 rows=201 width=4) (actual time=0.023..0.024 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=0.43..1546.01 rows=201 width=358) (actual time=0.022..0.023 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.46 rows=1 width=358) (actual time=0.019..0.020 rows=0 loops=1)
Index Cond: (namespaces_1.id = 26)
Filter: ((namespaces_1.type)::text = 'Group'::text)
Rows Removed by Filter: 1
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.56..153.85 rows=20 width=358) (actual time=0.001..0.002 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.35 rows=2 width=358) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.56..107.53 rows=19 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (projects.namespace_id = namespaces.id)
Filter: ((NOT projects.archived) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{0,10,20}'::integer[]))))
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
SubPlan 1
-> Index Only Scan using index_project_authorizations_on_project_id_user_id on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations.project_id = projects.id) AND (project_authorizations.user_id = 1))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..228.48 rows=6448 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (project_authorizations_1.user_id = 1)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using idx_issues_on_project_id_and_due_date_and_id_and_state_id on public.issues (cost=0.43..0.91 rows=1 width=1417) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((issues.project_id = projects.id) AND (issues.due_date = '2022-01-22'::date))
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.48 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (project_features.project_id = projects.id)
I/O Timings: read=0.000 write=0.000
Time: 4.911 ms
- planning: 4.489 ms
- execution: 0.422 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 10 (~80.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Details for searching all issues (unlikely)
SELECT COUNT(*) FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1933526 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "projects"."archived" = FALSE AND "issues"."due_date" = '2022-01-21'
Aggregate (cost=93778.07..93778.08 rows=1 width=8) (actual time=39734.782..39734.784 rows=1 loops=1)
Buffers: shared hit=45464 read=46372 dirtied=2164
I/O Timings: read=39012.844 write=0.000
-> Nested Loop Left Join (cost=1.30..93770.22 rows=3141 width=0) (actual time=863.558..39726.191 rows=4430 loops=1)
Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
Rows Removed by Filter: 1
Buffers: shared hit=45464 read=46372 dirtied=2164
I/O Timings: read=39012.844 write=0.000
-> Nested Loop (cost=0.87..92190.18 rows=3196 width=4) (actual time=858.048..35133.903 rows=4431 loops=1)
Buffers: shared hit=31889 read=42223 dirtied=2089
I/O Timings: read=34494.127 write=0.000
-> Index Only Scan using idx_issues_on_project_id_and_due_date_and_id_and_state_id on public.issues (cost=0.43..69232.14 rows=3266 width=4) (actual time=842.243..28186.917 rows=4434 loops=1)
Index Cond: (issues.due_date = '2022-01-21'::date)
Heap Fetches: 5260
Buffers: shared hit=4099 read=34364 dirtied=1583
I/O Timings: read=27754.661 write=0.000
-> Index Scan using projects_pkey on public.projects (cost=0.44..7.03 rows=1 width=4) (actual time=1.561..1.561 rows=1 loops=4434)
Index Cond: (projects.id = issues.project_id)
Filter: ((NOT projects.archived) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{0,10,20}'::integer[]))))
Rows Removed by Filter: 0
Buffers: shared hit=27655 read=7859 dirtied=371
I/O Timings: read=6739.466 write=0.000
SubPlan 1
-> Index Only Scan using index_project_authorizations_on_project_id_user_id on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.887..0.887 rows=0 loops=4431)
Index Cond: ((project_authorizations.project_id = projects.id) AND (project_authorizations.user_id = 1933526))
Heap Fetches: 1
Buffers: shared hit=14284 read=3446
I/O Timings: read=3872.822 write=0.000
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..216.79 rows=6406 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (project_authorizations_1.user_id = 1933526)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.48 rows=1 width=8) (actual time=1.033..1.033 rows=1 loops=4431)
Index Cond: (project_features.project_id = projects.id)
Buffers: shared hit=13575 read=4149 dirtied=75
I/O Timings: read=4518.717 write=0.000
cold:
Time: 39.745 s
- planning: 10.393 ms
- execution: 39.735 s
- I/O read: 39.013 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 45464 (~355.20 MiB) from the buffer pool
- reads: 46372 (~362.30 MiB) from the OS file cache, including disk I/O
- dirtied: 2164 (~16.90 MiB)
- writes: 0
warm:
Time: 201.422 ms
- planning: 1.956 ms
- execution: 199.466 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 89947 (~702.70 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1933526 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "projects"."archived" = FALSE AND "issues"."due_date" = '2022-01-21' ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 20 OFFSET 0
Limit (cost=98125.19..98125.24 rows=20 width=1418) (actual time=2475.253..2475.263 rows=20 loops=1)
Buffers: shared hit=86493 read=1956
I/O Timings: read=2208.665 write=0.000
-> Sort (cost=98125.19..98133.04 rows=3141 width=1418) (actual time=2475.251..2475.257 rows=20 loops=1)
Sort Key: issues.created_at DESC, issues.id DESC
Sort Method: top-N heapsort Memory: 77kB
Buffers: shared hit=86493 read=1956
I/O Timings: read=2208.665 write=0.000
-> Nested Loop Left Join (cost=1.30..98041.61 rows=3141 width=1418) (actual time=2.882..2466.080 rows=4430 loops=1)
Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
Rows Removed by Filter: 1
Buffers: shared hit=86487 read=1956
I/O Timings: read=2208.665 write=0.000
-> Nested Loop (cost=0.87..96461.58 rows=3196 width=1422) (actual time=2.869..2437.817 rows=4431 loops=1)
Buffers: shared hit=68763 read=1956
I/O Timings: read=2208.665 write=0.000
-> Index Scan using idx_issues_on_project_id_and_due_date_and_id_and_state_id on public.issues (cost=0.43..73503.53 rows=3266 width=1418) (actual time=2.792..2366.900 rows=4434 loops=1)
Index Cond: (issues.due_date = '2022-01-21'::date)
Buffers: shared hit=33297 read=1956
I/O Timings: read=2208.665 write=0.000
-> Index Scan using projects_pkey on public.projects (cost=0.44..7.03 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=4434)
Index Cond: (projects.id = issues.project_id)
Filter: ((NOT projects.archived) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{0,10,20}'::integer[]))))
Rows Removed by Filter: 0
Buffers: shared hit=35466
I/O Timings: read=0.000 write=0.000
SubPlan 1
-> Index Only Scan using index_project_authorizations_on_project_id_user_id on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=4431)
Index Cond: ((project_authorizations.project_id = projects.id) AND (project_authorizations.user_id = 1933526))
Heap Fetches: 1
Buffers: shared hit=17730
I/O Timings: read=0.000 write=0.000
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..216.79 rows=6406 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (project_authorizations_1.user_id = 1933526)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.48 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=4431)
Index Cond: (project_features.project_id = projects.id)
Buffers: shared hit=17724
I/O Timings: read=0.000 write=0.000
cold:
Time: 2.480 s
- planning: 4.175 ms
- execution: 2.475 s
- I/O read: 2.209 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 86493 (~675.70 MiB) from the buffer pool
- reads: 1956 (~15.30 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
warm:
Time: 192.688 ms
- planning: 1.646 ms
- execution: 191.042 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 88449 (~691.00 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0How to set up and validate locally
Assign some due dates to some issues e.g.
"TODAY"- http://gdk.test:3000/flightjs/Flight/-/issues/36
"TOMORROW"- http://gdk.test:3000/flightjs/Flight/-/issues/35
Hit the API to see if the right issues are returned:
`#35` and `#36`- http://gdk.test:3000/api/v4/projects/6/issues?due_date=any
`#36`- http://gdk.test:3000/api/v4/projects/6/issues?due_date=today
`#35`- http://gdk.test:3000/api/v4/projects/6/issues?due_date=tomorrow
`#35` and `#36`- http://gdk.test:3000/api/v4/groups/26/issues?due_date=any
`#36`- http://gdk.test:3000/api/v4/groups/26/issues?due_date=today
`#35`- http://gdk.test:3000/api/v4/groups/26/issues?due_date=tomorrow
`#35` and `#36`- http://gdk.test:3000/api/v4/issues?due_date=any&scope=all
`#36`- http://gdk.test:3000/api/v4/issues?due_date=today&scope=all
`#35`- http://gdk.test:3000/api/v4/issues?due_date=tomorrow&scope=allMR 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 Lee Tickett