Implement sort order for snoozed to-dos

Paul Gascou-Vaillancourtrequested to merge
17712-snoozed-todos-sort-order into master

What does this MR do and why?

Implement sort order for snoozed to-dos

Ensures that to-dos are sorted by snoozed_until and created_at timestamps. This causes snoozed to-dos that made it back to the pending tab to appear before non-snoozed to-dos that got created before the snoozed_until timestamp.

This requires the snoozed_todos_sort_order feature flag to be enabled, otherwise, the previous sort behavior applies.

Why the feature flag?

In a previous MR, we had switched the default sort order from sorting to-dos by ID instead of created_at. This was done to address performance issues that didn't scale well in production. With this new sort order, we are essentially re-introducing this scalability concern, so we'll want to iterate towards a more performant solution before enabling widely the feature flag.

How do the performances compare?

Here's the query plan for kind of query we currently run in production: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/35267/commands/109370

SELECT "todos".* FROM "todos" WHERE "todos"."user_id" = 113870 AND ("todos"."state" IN ('done')) ORDER BY "todos"."id" DESC LIMIT 20 OFFSET 0

This query completes in a matter of milliseconds:

Time: 34.737 ms
  - planning: 1.563 ms
  - execution: 33.174 ms
    - I/O read: 32.033 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 0 from the buffer pool
  - reads: 24 (~192.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 7 (~56.00 KiB)
  - writes: 0

Now, when introducing the new sort order, the query becomes excruciatingly slow: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/35267/commands/109372

SELECT "todos".* FROM "todos" WHERE "todos"."user_id" = 113870 AND ("todos"."state" IN ('done')) ORDER BY COALESCE(todos.snoozed_until, todos.created_at) DESC LIMIT 20 OFFSET 0
Time: 26.043 s
  - planning: 1.778 ms
  - execution: 26.041 s
    - I/O read: 25.606 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 613 (~4.80 MiB) from the buffer pool
  - reads: 35199 (~275.00 MiB) from the OS file cache, including disk I/O
  - dirtied: 651 (~5.10 MiB)
  - writes: 0

SQL query

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/35514/commands/109990

SELECT
    "todos".*
FROM
    "todos"
    LEFT JOIN banned_users ON todos.author_id = banned_users.user_id
WHERE
    "todos"."user_id" = 1
    AND ("todos"."state" IN ('pending'))
    AND "banned_users"."user_id" IS NULL
    AND ("todos"."state" IN ('pending'))
    AND ("todos"."snoozed_until" <= '2025-01-16 14:29:08.585417'
        OR "todos"."snoozed_until" IS NULL)
ORDER BY
    COALESCE(todos.snoozed_until, todos.created_at) DESC
LIMIT 21

Example data

The following data was collected by running this script in the Rails console:

current_user = User.first
finder = TodosFinder.new(current_user)
todos = finder.execute
todos.map { |todo| { id: todo.id, created_at: todo.created_at, snoozed_until: todo.snoozed_until } }

Before

The to-dos are sorted by ID.

[
  {:id=>56, :created_at=>Thu, 16 Jan 2025 12:38:46.676089000 UTC +00:00, :snoozed_until=>nil},
  {:id=>55, :created_at=>Tue, 07 Jan 2025 16:04:19.972360000 UTC +00:00, :snoozed_until=>Thu, 16 Jan 2025 13:59:58.898000000 UTC +00:00},
  {:id=>54, :created_at=>Thu, 02 Jan 2025 19:33:47.756024000 UTC +00:00, :snoozed_until=>nil},
  {:id=>51, :created_at=>Thu, 02 Jan 2025 17:34:27.747281000 UTC +00:00, :snoozed_until=>Thu, 16 Jan 2025 13:03:31.429000000 UTC +00:00},
  {:id=>50, :created_at=>Thu, 02 Jan 2025 17:31:25.107876000 UTC +00:00, :snoozed_until=>Fri, 10 Jan 2025 19:58:05.686000000 UTC +00:00},
  {:id=>49, :created_at=>Thu, 02 Jan 2025 17:30:50.653911000 UTC +00:00, :snoozed_until=>Thu, 09 Jan 2025 23:11:37.062000000 UTC +00:00},
  {:id=>48, :created_at=>Thu, 02 Jan 2025 17:30:26.339994000 UTC +00:00, :snoozed_until=>Fri, 10 Jan 2025 13:00:00.000000000 UTC +00:00},
  {:id=>46, :created_at=>Thu, 02 Jan 2025 17:23:22.896320000 UTC +00:00, :snoozed_until=>nil},
  {:id=>45, :created_at=>Thu, 02 Jan 2025 17:07:57.440361000 UTC +00:00, :snoozed_until=>nil},
  {:id=>44, :created_at=>Thu, 02 Jan 2025 16:57:30.947043000 UTC +00:00, :snoozed_until=>nil},
  {:id=>43, :created_at=>Thu, 19 Dec 2024 20:19:28.029269000 UTC +00:00, :snoozed_until=>nil},
  {:id=>28, :created_at=>Tue, 23 Jul 2024 16:18:54.419327000 UTC +00:00, :snoozed_until=>nil},
  {:id=>24, :created_at=>Mon, 22 Apr 2024 16:15:34.424764000 UTC +00:00, :snoozed_until=>Tue, 31 Dec 2024 20:52:21.528000000 UTC +00:00},
  {:id=>22, :created_at=>Mon, 22 Apr 2024 16:11:11.744000000 UTC +00:00, :snoozed_until=>nil},
  {:id=>20, :created_at=>Tue, 20 Feb 2024 22:32:01.592163000 UTC +00:00, :snoozed_until=>nil},
  {:id=>15, :created_at=>Thu, 04 Jan 2024 15:35:07.811467000 UTC +00:00, :snoozed_until=>nil}
]

After

The to-dos are sorted by snoozed_until and created_at.

[
  {:id=>55, :created_at=>Tue, 07 Jan 2025 16:04:19.972360000 UTC +00:00, :snoozed_until=>Thu, 16 Jan 2025 13:59:58.898000000 UTC +00:00},
  {:id=>51, :created_at=>Thu, 02 Jan 2025 17:34:27.747281000 UTC +00:00, :snoozed_until=>Thu, 16 Jan 2025 13:03:31.429000000 UTC +00:00},
  {:id=>56, :created_at=>Thu, 16 Jan 2025 12:38:46.676089000 UTC +00:00, :snoozed_until=>nil},
  {:id=>50, :created_at=>Thu, 02 Jan 2025 17:31:25.107876000 UTC +00:00, :snoozed_until=>Fri, 10 Jan 2025 19:58:05.686000000 UTC +00:00},
  {:id=>48, :created_at=>Thu, 02 Jan 2025 17:30:26.339994000 UTC +00:00, :snoozed_until=>Fri, 10 Jan 2025 13:00:00.000000000 UTC +00:00},
  {:id=>49, :created_at=>Thu, 02 Jan 2025 17:30:50.653911000 UTC +00:00, :snoozed_until=>Thu, 09 Jan 2025 23:11:37.062000000 UTC +00:00},
  {:id=>54, :created_at=>Thu, 02 Jan 2025 19:33:47.756024000 UTC +00:00, :snoozed_until=>nil},
  {:id=>46, :created_at=>Thu, 02 Jan 2025 17:23:22.896320000 UTC +00:00, :snoozed_until=>nil},
  {:id=>45, :created_at=>Thu, 02 Jan 2025 17:07:57.440361000 UTC +00:00, :snoozed_until=>nil},
  {:id=>44, :created_at=>Thu, 02 Jan 2025 16:57:30.947043000 UTC +00:00, :snoozed_until=>nil},
  {:id=>24, :created_at=>Mon, 22 Apr 2024 16:15:34.424764000 UTC +00:00, :snoozed_until=>Tue, 31 Dec 2024 20:52:21.528000000 UTC +00:00},
  {:id=>43, :created_at=>Thu, 19 Dec 2024 20:19:28.029269000 UTC +00:00, :snoozed_until=>nil},
  {:id=>28, :created_at=>Tue, 23 Jul 2024 16:18:54.419327000 UTC +00:00, :snoozed_until=>nil},
  {:id=>22, :created_at=>Mon, 22 Apr 2024 16:11:11.744000000 UTC +00:00, :snoozed_until=>nil},
  {:id=>20, :created_at=>Tue, 20 Feb 2024 22:32:01.592163000 UTC +00:00, :snoozed_until=>nil},
  {:id=>15, :created_at=>Thu, 04 Jan 2024 15:35:07.811467000 UTC +00:00, :snoozed_until=>nil}
]

References

Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

How to set up and validate locally

  1. Enable the snoozed_todos_sort_order feature flag: http://gdk.test:3000/rails/features.
  2. Navigate to the to-dos dashboard: http://gdk.test:3000/dashboard/todos.
  3. Snooze some to-dos.
    • When those snoozed to-dos reach the snoozed_until time and get moved back to the pending tab, they should appear above non-snoozed to-dos that got created before the snoozed_until timestamp.
  4. Create new to-dos (by mentioning yourself in an issue for example).
    • New (non-snoozed) to-dos should appear above previously snoozed to-dos that had their snoozed_until timestamp set to before the new to-dos creation date.
    • In the Done and All tabs, the usual sort order is used (sorted by ID).

For convenience, apply the following patch so that to-dos can be snoozed for only 10 seconds which should make things easier to test:

diff --git a/app/assets/javascripts/todos/components/toggle_snoozed_status.vue b/app/assets/javascripts/todos/components/toggle_snoozed_status.vue
index 7efa88bc62bf97..b2a45a96182055 100644
--- a/app/assets/javascripts/todos/components/toggle_snoozed_status.vue
+++ b/app/assets/javascripts/todos/components/toggle_snoozed_status.vue
@@ -3,7 +3,7 @@ import { GlButton, GlDisclosureDropdown, GlTooltip } from '@gitlab/ui';
 import { uniqueId } from 'lodash';
 import dateFormat from '~/lib/dateformat';
 import { s__, sprintf } from '~/locale';
-import { nHoursAfter } from '~/lib/utils/datetime_utility';
+import { nHoursAfter, nSecondsAfter } from '~/lib/utils/datetime_utility';
 import { reportToSentry } from '~/ci/utils';
 import { localeDateFormat } from '~/lib/utils/datetime/locale_dateformat';
 import Tracking from '~/tracking';
@@ -44,7 +44,7 @@ export default {
       return !this.isSnoozed && this.isPending;
     },
     dropdownOptions() {
-      const forAnHour = nHoursAfter(this.currentTime, 1);
+      const forAnHour = nSecondsAfter(this.currentTime, 10);
       const untilLaterToday = nHoursAfter(this.currentTime, 4);
       const untilTomorrow = new Date(
         this.currentTime.getFullYear(),
@@ -60,7 +60,7 @@ export default {
           name: s__('Todos|Snooze'),
           items: [
             {
-              text: s__('Todos|For one hour'),
+              text: s__('Todos|For 10 seconds'),
               formattedDate: sprintf(s__('Todos|%{day}, %{time}'), {
                 day: dateFormat(forAnHour, 'DDDD'),
                 time: toTimeString(forAnHour),

Related to #17712 (closed)

Edited by Paul Gascou-Vaillancourt

Merge request reports