Enqueue shift persistence jobs for rotations in batches (!55926) · Merge requests · GitLab.org / GitLab

Sarah Yasonik requested to merge sy-add-index-for-rotation-start-end into master Mar 06, 2021

What does this MR do?

This MR tunes the performance of the query IncidentManagement::PersistAllRotationsShiftsJob, which traverses nearly the entire incident_management_oncall_rotations table every ~5 minutes, and takes an action for most records. It's a follow-up from performance discussions in !53675 (merged).

Still needed: Decision on approach

Disclaimer: The incident_management_oncall_rotations table is nowhere near the scale that this query is causing problems. Even if we estimate 500x growth, the current query doesn't come close to our timing guidelines.

Still, there are a few different implementations considered as a part of the performance analysis for this MR, outlined below.

The implementation of #3 was suggested in !53675 (comment 512192251)), but I think #2 is probably the best way to move forward, so that's what I've got implemented in the MR currently. Because this is a background task that runs regularly, sticking to many short & snappy queries seems like the ideal way to prevent negative impact to users. And while the index does technically improve the performance, it's pretty negligible due to the number of records actually being acted on. Since this also isn't a user-facing query, I think an extra index probably isn't worthwhile.

The current query
- The OR in the query pretty much guarantees us a sequential scan of the table, regardless of indicies
```
IncidentManagement::OncallRotation.where('starts_at < NOW() AND (ends_at > NOW() OR ends_at IS NULL)'))
```
Simply adding batching & keeping the OR
- This essential gives us a bunch of small sequential scans, which may be ok considering that we're targeting more than just a few records
```
IncidentManagement::OncallRotation
  .each_batch do |rotations|
    rotations.where('starts_at < NOW() AND (ends_at > NOW() OR ends_at IS NULL)')
  end
```

Splitting the query in two & adding an index on starts_at, ends_at, & id

This gives us optimized queries within each batch, but comes at the cost of keeping the index up to date
Batching before the WHERE clause:

IncidentManagement::OncallRotation.each_batch do |rotations|
  rotations.where(starts_at: ..time, ends_at: time..).ids.each do |id|
    IncidentManagement::OncallRotations::PersistShiftsJob.perform_async(id)
  end
  rotations.where(starts_at: ..time, ends_at: nil).ids.each do |id|
    IncidentManagement::OncallRotations::PersistShiftsJob.perform_async(id)
  end
end

Batching after the WHERE clause:
- With the index, grabbing a targeted batch gets more performant with an upper threshold, but isn't quite as snappy as the non-filtered batches & also has the cost of keeping the index up to date

IncidentManagement::OncallRotation.where(starts_at: ..time, ends_at: time..).each_batch do |rotations|
  rotations.ids.each do |id|
    IncidentManagement::OncallRotations::PersistShiftsJob.perform_async(id)
  end
end

IncidentManagement::OncallRotation.where(starts_at: ..time, ends_at: nil).each_batch do |rotations|
  rotations.ids.each do |id|
    IncidentManagement::OncallRotations::PersistShiftsJob.perform_async(id)
  end
end

Database

Because this MR slipped a few milestones, we now actually have some records on gitlab.com! (As opposed to when I started the MR, there were 0).

We have ~250 rotation records on production.
~80% of rotations on prod are currently "in progress"
~20% of rotations have end times
~99% of rotations with end times have been completed.
The feature has existed for ~5 milestones.
We have an issue to limit historical shifts to 6 months, which could easily include rotations as well to limit un-actionable rows in the rotations table.

The ratios of rotations with start/end times all make sense with expected usage, so I'm going to assume usage will continue to grow ~proportionally. To estimate future usage, I seeded my local DB with rotation records roughly in line with these ratios.

Table of query options & performance details

	Production-like data	10x Growth	500x Growth
Data Composition	234 total rotations 219 have started 15 have not yet started 50 have ended 8 have not yet ended 176 have no end	2461 total rotations 2279 have started 182 have not yet started 437 have ended 75 have not yet ended 1949 have no end	114352 total rotations 106077 have started 8275 have not yet started 18892 have ended 3860 have not yet ended 91600 have no end

single query (ORIGINAL) - without index, - batch size of 1000 Queries (1) Single query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL))	Single query Average: ~0.066 ms / query Sum: ~0.066 ms https://explain.depesz.com/s/EBS5 All execution times (1) [0.066]	Single query Average: ~0.602 ms / query Sum: ~0.602 ms https://explain.depesz.com/s/T5rg All execution times (1) [0.602]	Single query Average: ~19.656 ms / query Sum: ~19.656 ms https://explain.depesz.com/s/eZ94 All execution times (1) [19.656]
batched by id, single query - without index, - batch size of 1000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 AND (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 LIMIT 1 OFFSET 1000	Batch query Average: ~0.067 ms / query Sum: ~0.067 ms https://explain.depesz.com/s/29ob All execution times (1) [0.067] Step query Average: ~0.064 ms / query Sum: ~0.064 ms https://explain.depesz.com/s/QuJN All execution times (1) [0.064]	Batch query Average: ~0.38 ms / query Sum: ~1.141 ms https://explain.depesz.com/s/y3vs All execution times (3) [0.428, 0.361, 0.352] Step query Average: ~0.333 ms / query Sum: ~0.999 ms https://explain.depesz.com/s/EfUW All execution times (3) [0.408, 0.324, 0.267]	Batch query Average: ~0.259 ms / query Sum: ~29.807 ms https://explain.depesz.com/s/RH7U All execution times (115) [0.319, 0.345, 0.29, 0.334, 0.32, 0.308, 0.295, 0.315, 0.29, 0.294, 0.285, 0.29, 0.287, 0.278, 0.268, 0.276, 0.27, 0.274, 0.268, 0.273, 0.261, 0.269, 0.266, 0.267, 0.268, 0.254, 0.237, 0.231, 0.235, 0.235, 0.25, 0.228, 0.232, 0.344, 0.231, 0.23, 0.228, 0.23, 0.232, 0.23, 0.232, 0.232, 0.247, 0.237, 0.23, 0.228, 0.229, 0.24, 0.235, 0.227, 0.232, 0.234, 0.265, 0.263, 0.269, 0.289, 0.391, 0.315, 0.305, 0.296, 0.296, 0.286, 0.28, 0.263, 0.277, 0.259, 0.265, 0.266, 0.284, 0.265, 0.261, 0.265, 0.267, 0.262, 0.263, 0.24, 0.23, 0.232, 0.229, 0.23, 0.237, 0.241, 0.23, 0.229, 0.23, 0.235, 0.245, 0.265, 0.269, 0.275, 0.265, 0.264, 0.275, 0.252, 0.262, 0.262, 0.256, 0.244, 0.263, 0.264, 0.234, 0.233, 0.233, 0.232, 0.233, 0.23, 0.242, 0.232, 0.237, 0.321, 0.244, 0.266, 0.237, 0.23, 0.087] Step query Average: ~0.259 ms / query Sum: ~29.839 ms https://explain.depesz.com/s/jhex All execution times (115) [0.326, 0.352, 0.293, 0.314, 0.333, 0.301, 0.302, 0.309, 0.286, 0.296, 0.297, 0.286, 0.278, 0.278, 0.269, 0.27, 0.269, 0.269, 0.273, 0.27, 0.262, 0.264, 0.278, 0.277, 0.275, 0.246, 0.23, 0.229, 0.232, 0.232, 0.23, 0.231, 0.229, 0.231, 0.231, 0.231, 0.23, 0.231, 0.23, 0.23, 0.23, 0.229, 0.229, 0.23, 0.231, 0.23, 0.23, 0.23, 0.23, 0.229, 0.23, 0.248, 0.262, 0.263, 0.277, 0.271, 0.362, 0.315, 0.305, 0.295, 0.286, 0.293, 0.278, 0.262, 0.262, 0.262, 0.263, 0.261, 0.27, 0.264, 0.273, 0.307, 0.263, 0.257, 0.261, 0.232, 0.23, 0.23, 0.23, 0.229, 0.229, 0.23, 0.23, 0.23, 0.23, 0.23, 0.267, 0.261, 0.262, 0.273, 0.278, 0.275, 0.267, 0.275, 0.264, 0.262, 0.242, 0.257, 0.262, 0.254, 0.23, 0.426, 0.242, 0.242, 0.24, 0.243, 0.242, 0.243, 0.235, 0.285, 0.259, 0.263, 0.242, 0.243, 0.087]
batched by id/start/end, single query - without index, - batch size of 1000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1573191 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1573191 LIMIT 1 OFFSET 1000	Batch query Average: ~0.084 ms / query Sum: ~0.084 ms https://explain.depesz.com/s/3vwv All execution times (1) [0.084] Step query Average: ~0.071 ms / query Sum: ~0.071 ms https://explain.depesz.com/s/r2rm All execution times (1) [0.071]	Batch query Average: ~0.438 ms / query Sum: ~0.875 ms https://explain.depesz.com/s/FuZaT All execution times (2) [0.391, 0.484] Step query Average: ~0.417 ms / query Sum: ~0.834 ms https://explain.depesz.com/s/8tN All execution times (2) [0.387, 0.447]	Batch query Average: ~0.33 ms / query Sum: ~29.031 ms https://explain.depesz.com/s/JCQK All execution times (88) [0.451, 0.445, 0.381, 0.374, 0.364, 0.348, 0.347, 0.311, 0.306, 0.298, 0.299, 0.304, 0.36, 0.305, 0.314, 0.317, 0.314, 0.312, 0.302, 0.311, 0.306, 0.296, 0.302, 0.313, 0.302, 0.295, 0.297, 0.308, 0.304, 0.293, 0.314, 0.331, 0.34, 0.312, 0.305, 0.312, 0.297, 0.431, 0.424, 0.399, 0.387, 0.355, 0.35, 0.352, 0.348, 0.359, 0.35, 0.342, 0.348, 0.35, 0.329, 0.317, 0.331, 0.364, 0.431, 0.348, 0.347, 0.352, 0.347, 0.353, 0.315, 0.306, 0.3, 0.307, 0.337, 0.384, 0.304, 0.315, 0.306, 0.301, 0.375, 0.309, 0.302, 0.31, 0.45, 0.32, 0.301, 0.299, 0.304, 0.305, 0.313, 0.313, 0.313, 0.3, 0.301, 0.38, 0.295, 0.062] Step query Average: ~0.32 ms / query Sum: ~28.126 ms https://explain.depesz.com/s/b6pK All execution times (88) [0.456, 0.423, 0.369, 0.354, 0.369, 0.338, 0.333, 0.296, 0.297, 0.295, 0.293, 0.297, 0.299, 0.292, 0.303, 0.297, 0.295, 0.303, 0.296, 0.298, 0.298, 0.29, 0.296, 0.292, 0.412, 0.291, 0.424, 0.299, 0.296, 0.291, 0.3, 0.325, 0.326, 0.325, 0.294, 0.302, 0.292, 0.415, 0.398, 0.387, 0.368, 0.347, 0.334, 0.34, 0.34, 0.338, 0.342, 0.298, 0.337, 0.342, 0.307, 0.302, 0.316, 0.299, 0.364, 0.339, 0.335, 0.348, 0.338, 0.339, 0.313, 0.298, 0.292, 0.498, 0.357, 0.313, 0.291, 0.3, 0.301, 0.297, 0.303, 0.295, 0.296, 0.302, 0.3, 0.305, 0.291, 0.298, 0.298, 0.3, 0.299, 0.297, 0.302, 0.295, 0.294, 0.306, 0.293, 0.063]
split queries - without index, - batch size of 1000 Queries (2) Split query (w/ ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at > '2021-10-11 22:46:44.011359') Split query (w/o ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at IS NULL)	Split query (w/ ends_at) Average: ~0.039 ms / query Sum: ~0.039 ms https://explain.depesz.com/s/20l All execution times (1) [0.039] Split query (w/o ends_at) Average: ~0.057 ms / query Sum: ~0.057 ms https://explain.depesz.com/s/etE7 All execution times (1) [0.057]	Split query (w/ ends_at) Average: ~0.329 ms / query Sum: ~0.329 ms https://explain.depesz.com/s/3Osy All execution times (1) [0.329] Split query (w/o ends_at) Average: ~0.657 ms / query Sum: ~0.657 ms https://explain.depesz.com/s/hvHfg All execution times (1) [0.657]	Split query (w/ ends_at) Average: ~12.685 ms / query Sum: ~12.685 ms https://explain.depesz.com/s/g1Vi All execution times (1) [12.685] Split query (w/o ends_at) Average: ~19.025 ms / query Sum: ~19.025 ms https://explain.depesz.com/s/Mmw3 All execution times (1) [19.025]
batched by id, split queries - without index, - batch size of 1000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:46:50.356212' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:46:50.356212' Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:46:50.356212' AND "incident_management_oncall_rotations"."ends_at" IS NULL Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 LIMIT 1 OFFSET 1000	Batch query (w/o NULL ends_at) Average: ~0.041 ms / query Sum: ~0.041 ms https://explain.depesz.com/s/nA1d All execution times (1) [0.041] Batch query (w/ NULL ends_at) Average: ~0.083 ms / query Sum: ~0.083 ms https://explain.depesz.com/s/ykUT All execution times (1) [0.083] Step query Average: ~0.063 ms / query Sum: ~0.063 ms https://explain.depesz.com/s/SlQK All execution times (1) [0.063]	Batch query (w/o NULL ends_at) Average: ~0.25 ms / query Sum: ~0.749 ms https://explain.depesz.com/s/fL3O All execution times (3) [0.218, 0.248, 0.283] Batch query (w/ NULL ends_at) Average: ~0.342 ms / query Sum: ~1.027 ms https://explain.depesz.com/s/ak36 All execution times (3) [0.383, 0.313, 0.331] Step query Average: ~0.282 ms / query Sum: ~0.847 ms https://explain.depesz.com/s/g32M All execution times (3) [0.305, 0.295, 0.247]	Batch query (w/o NULL ends_at) Average: ~0.169 ms / query Sum: ~19.386 ms https://explain.depesz.com/s/Yqnc All execution times (115) [0.317, 0.262, 0.229, 0.215, 0.208, 0.195, 0.189, 0.181, 0.184, 0.179, 0.183, 0.177, 0.174, 0.175, 0.178, 0.174, 0.174, 0.173, 0.172, 0.152, 0.158, 0.151, 0.151, 0.154, 0.152, 0.15, 0.151, 0.152, 0.152, 0.155, 0.155, 0.152, 0.154, 0.153, 0.151, 0.153, 0.153, 0.163, 0.157, 0.155, 0.152, 0.149, 0.152, 0.164, 0.173, 0.173, 0.174, 0.155, 0.162, 0.173, 0.212, 0.174, 0.171, 0.171, 0.151, 0.151, 0.151, 0.151, 0.152, 0.15, 0.155, 0.153, 0.151, 0.151, 0.154, 0.153, 0.155, 0.15, 0.156, 0.151, 0.151, 0.18, 0.184, 0.214, 0.213, 0.202, 0.195, 0.189, 0.188, 0.186, 0.182, 0.182, 0.26, 0.186, 0.176, 0.177, 0.178, 0.173, 0.165, 0.216, 0.152, 0.15, 0.157, 0.151, 0.151, 0.152, 0.152, 0.152, 0.152, 0.151, 0.153, 0.153, 0.149, 0.152, 0.154, 0.155, 0.153, 0.153, 0.152, 0.194, 0.161, 0.174, 0.172, 0.174, 0.067] Batch query (w/ NULL ends_at) Average: ~0.252 ms / query Sum: ~28.99 ms https://explain.depesz.com/s/QrUL All execution times (115) [0.38, 0.349, 0.347, 0.331, 0.301, 0.281, 0.285, 0.284, 0.264, 0.281, 0.262, 0.256, 0.256, 0.257, 0.269, 0.258, 0.267, 0.256, 0.221, 0.223, 0.225, 0.224, 0.23, 0.224, 0.229, 0.229, 0.223, 0.224, 0.246, 0.23, 0.228, 0.227, 0.239, 0.306, 0.227, 0.225, 0.236, 0.232, 0.262, 0.224, 0.232, 0.223, 0.234, 0.256, 0.264, 0.255, 0.256, 0.227, 0.237, 0.259, 0.26, 0.292, 0.253, 0.237, 0.224, 0.234, 0.224, 0.224, 0.225, 0.223, 0.227, 0.226, 0.224, 0.224, 0.435, 0.231, 0.224, 0.222, 0.226, 0.228, 0.273, 0.275, 0.327, 0.316, 0.304, 0.315, 0.279, 0.287, 0.27, 0.348, 0.278, 0.297, 0.277, 0.263, 0.261, 0.277, 0.256, 0.257, 0.223, 0.224, 0.224, 0.229, 0.222, 0.273, 0.222, 0.222, 0.222, 0.273, 0.225, 0.222, 0.225, 0.232, 0.225, 0.225, 0.235, 0.23, 0.225, 0.225, 0.221, 0.246, 0.264, 0.258, 0.254, 0.264, 0.096] Step query Average: ~0.249 ms / query Sum: ~28.633 ms https://explain.depesz.com/s/VUSD All execution times (115) [0.319, 0.314, 0.288, 0.266, 0.267, 0.266, 0.268, 0.267, 0.266, 0.249, 0.271, 0.251, 0.233, 0.234, 0.234, 0.234, 0.24, 0.233, 0.233, 0.234, 0.233, 0.234, 0.233, 0.235, 0.232, 0.232, 0.232, 0.238, 0.233, 0.233, 0.233, 0.232, 0.232, 0.232, 0.233, 0.233, 0.233, 0.233, 0.234, 0.234, 0.233, 0.233, 0.234, 0.233, 0.232, 0.232, 0.232, 0.233, 0.233, 0.235, 0.239, 0.232, 0.231, 0.232, 0.232, 0.234, 0.232, 0.232, 0.232, 0.242, 0.239, 0.294, 0.235, 0.251, 0.257, 0.238, 0.233, 0.231, 0.233, 0.236, 0.244, 0.246, 0.402, 0.376, 0.273, 0.278, 0.292, 0.298, 0.28, 0.293, 0.266, 0.305, 0.266, 0.264, 0.264, 0.277, 0.255, 0.232, 0.232, 0.233, 0.233, 0.234, 0.24, 0.242, 0.245, 0.244, 0.236, 0.233, 0.234, 0.248, 0.268, 0.267, 0.273, 0.281, 0.265, 0.266, 0.265, 0.265, 0.265, 0.276, 0.24, 0.235, 0.232, 0.238, 0.086]
batched by id/start/end, split queries - without index, - batch size of 1000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:46:53.266194' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:46:53.266194' AND "incident_management_oncall_rotations"."id" >= 1571845 AND "incident_management_oncall_rotations"."id" < 1627143 Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:46:53.266194' AND "incident_management_oncall_rotations"."ends_at" IS NULL AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1573234 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:46:53.266194' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:46:53.266194' AND "incident_management_oncall_rotations"."id" >= 1571845 AND "incident_management_oncall_rotations"."id" < 1627143 LIMIT 1 OFFSET 1000	Batch query (w/o NULL ends_at) Average: ~0.048 ms / query Sum: ~0.048 ms https://explain.depesz.com/s/EShE All execution times (1) [0.048] Batch query (w/ NULL ends_at) Average: ~0.066 ms / query Sum: ~0.066 ms https://explain.depesz.com/s/nZD2r All execution times (1) [0.066] Step query Average: ~0.055 ms / query Sum: ~0.109 ms https://explain.depesz.com/s/OyUM All execution times (2) [0.046, 0.063]	Batch query (w/o NULL ends_at) Average: ~0.32 ms / query Sum: ~0.32 ms https://explain.depesz.com/s/MoXi All execution times (1) [0.32] Batch query (w/ NULL ends_at) Average: ~0.44 ms / query Sum: ~0.879 ms https://explain.depesz.com/s/qbvH All execution times (2) [0.445, 0.434] Step query Average: ~0.392 ms / query Sum: ~1.175 ms https://explain.depesz.com/s/T5gi All execution times (3) [0.326, 0.428, 0.421]	Batch query (w/o NULL ends_at) Average: ~7.093 ms / query Sum: ~21.278 ms https://explain.depesz.com/s/6uf5u All execution times (3) [11.033, 9.086, 1.159] Batch query (w/ NULL ends_at) Average: ~0.324 ms / query Sum: ~27.879 ms https://explain.depesz.com/s/FraI All execution times (86) [0.442, 0.396, 0.345, 0.339, 0.347, 0.337, 0.305, 0.362, 0.351, 0.334, 0.342, 0.352, 0.355, 0.312, 0.336, 0.303, 0.306, 0.311, 0.304, 0.3, 0.303, 0.32, 0.3, 0.319, 0.297, 0.386, 0.341, 0.369, 0.354, 0.359, 0.356, 0.34, 0.356, 0.348, 0.354, 0.351, 0.344, 0.355, 0.308, 0.315, 0.301, 0.301, 0.308, 0.308, 0.302, 0.305, 0.303, 0.308, 0.306, 0.302, 0.298, 0.313, 0.304, 0.314, 0.314, 0.307, 0.311, 0.304, 0.332, 0.305, 0.439, 0.315, 0.306, 0.298, 0.311, 0.365, 0.357, 0.347, 0.365, 0.338, 0.305, 0.321, 0.319, 0.316, 0.316, 0.354, 0.338, 0.31, 0.317, 0.301, 0.304, 0.299, 0.296, 0.316, 0.305, 0.021] Step query Average: ~0.536 ms / query Sum: ~47.679 ms https://explain.depesz.com/s/l5DJb All execution times (89) [10.37, 9.043, 1.196, 0.431, 0.388, 0.342, 0.334, 0.338, 0.319, 0.308, 0.347, 0.344, 0.389, 0.341, 0.342, 0.302, 0.301, 0.305, 0.301, 0.3, 0.305, 0.298, 0.319, 0.302, 0.294, 0.295, 0.302, 0.296, 0.296, 0.337, 0.371, 0.349, 0.35, 0.352, 0.345, 0.345, 0.341, 0.349, 0.337, 0.337, 0.319, 0.303, 0.3, 0.296, 0.296, 0.3, 0.297, 0.298, 0.302, 0.3, 0.301, 0.304, 0.302, 0.295, 0.299, 0.302, 0.347, 0.298, 0.299, 0.306, 0.298, 0.303, 0.3, 0.302, 0.302, 0.302, 0.299, 0.302, 0.339, 0.346, 0.343, 0.345, 0.342, 0.301, 0.307, 0.301, 0.31, 0.307, 0.345, 0.322, 0.308, 0.304, 0.3, 0.304, 0.298, 0.296, 0.304, 0.302, 0.022]

single query (ORIGINAL) - with index on id, starts_at, ends_at, - batch size of 1000 Queries (1) Single query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL))	Single query Average: ~0.074 ms / query Sum: ~0.074 ms https://explain.depesz.com/s/JFoD All execution times (1) [0.074]	Single query Average: ~0.619 ms / query Sum: ~0.619 ms https://explain.depesz.com/s/ryEH All execution times (1) [0.619]	Single query Average: ~19.757 ms / query Sum: ~19.757 ms https://explain.depesz.com/s/JidK All execution times (1) [19.757]
batched by id, single query - with index on id, starts_at, ends_at, - batch size of 1000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 AND (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 LIMIT 1 OFFSET 1000	Batch query Average: ~0.09 ms / query Sum: ~0.09 ms https://explain.depesz.com/s/urjFw All execution times (1) [0.09] Step query Average: ~0.06 ms / query Sum: ~0.06 ms https://explain.depesz.com/s/Qgqf All execution times (1) [0.06]	Batch query Average: ~0.277 ms / query Sum: ~0.832 ms https://explain.depesz.com/s/hF7f All execution times (3) [0.346, 0.327, 0.159] Step query Average: ~0.327 ms / query Sum: ~0.982 ms https://explain.depesz.com/s/9Nj5n All execution times (3) [0.464, 0.352, 0.166]	Batch query Average: ~0.269 ms / query Sum: ~30.883 ms https://explain.depesz.com/s/cxME All execution times (115) [0.516, 0.384, 0.338, 0.318, 0.355, 0.345, 0.33, 0.331, 0.322, 0.313, 0.297, 0.27, 0.284, 0.262, 0.277, 0.261, 0.274, 0.27, 0.278, 0.274, 0.264, 0.275, 0.251, 0.243, 0.247, 0.242, 0.228, 0.265, 0.288, 0.273, 0.264, 0.277, 0.294, 0.295, 0.287, 0.286, 0.277, 0.26, 0.282, 0.274, 0.27, 0.261, 0.306, 0.277, 0.279, 0.259, 0.282, 0.245, 0.245, 0.229, 0.238, 0.228, 0.242, 0.228, 0.241, 0.246, 0.239, 0.228, 0.24, 0.228, 0.232, 0.244, 0.233, 0.242, 0.235, 0.234, 0.243, 0.228, 0.245, 0.229, 0.245, 0.235, 0.242, 0.23, 0.237, 0.235, 0.268, 0.286, 0.277, 0.268, 0.27, 0.282, 0.279, 0.301, 0.27, 0.27, 0.277, 0.285, 0.278, 0.273, 0.275, 0.23, 0.238, 0.323, 0.228, 0.241, 0.241, 0.227, 0.228, 0.236, 0.24, 0.244, 0.265, 0.228, 0.278, 0.247, 0.252, 0.23, 0.321, 0.375, 0.331, 0.321, 0.297, 0.309, 0.123] Step query Average: ~0.265 ms / query Sum: ~30.479 ms https://explain.depesz.com/s/fnnY All execution times (115) [0.398, 0.389, 0.333, 0.318, 0.34, 0.325, 0.332, 0.319, 0.308, 0.303, 0.27, 0.271, 0.29, 0.263, 0.276, 0.267, 0.278, 0.265, 0.272, 0.283, 0.264, 0.277, 0.255, 0.238, 0.233, 0.243, 0.231, 0.28, 0.265, 0.265, 0.264, 0.274, 0.28, 0.292, 0.287, 0.274, 0.264, 0.264, 0.278, 0.266, 0.264, 0.263, 0.28, 0.264, 0.275, 0.266, 0.266, 0.232, 0.25, 0.233, 0.244, 0.232, 0.241, 0.232, 0.231, 0.243, 0.231, 0.232, 0.243, 0.232, 0.232, 0.244, 0.232, 0.241, 0.231, 0.232, 0.243, 0.324, 0.245, 0.232, 0.242, 0.232, 0.245, 0.232, 0.232, 0.233, 0.264, 0.295, 0.271, 0.273, 0.264, 0.279, 0.274, 0.28, 0.276, 0.271, 0.278, 0.264, 0.274, 0.283, 0.273, 0.232, 0.243, 0.235, 0.234, 0.243, 0.242, 0.231, 0.231, 0.235, 0.231, 0.237, 0.244, 0.232, 0.242, 0.232, 0.248, 0.351, 0.322, 0.319, 0.327, 0.315, 0.297, 0.308, 0.109]
batched by id/start/end, single query - with index on id, starts_at, ends_at, - batch size of 1000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1573191 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1573191 LIMIT 1 OFFSET 1000	Batch query Average: ~0.097 ms / query Sum: ~0.097 ms https://explain.depesz.com/s/tr25 All execution times (1) [0.097] Step query Average: ~0.122 ms / query Sum: ~0.122 ms https://explain.depesz.com/s/9Mvy All execution times (1) [0.122]	Batch query Average: ~0.41 ms / query Sum: ~0.82 ms https://explain.depesz.com/s/RtBG All execution times (2) [0.427, 0.393] Step query Average: ~0.413 ms / query Sum: ~0.826 ms https://explain.depesz.com/s/M0pa All execution times (2) [0.472, 0.354]	Batch query Average: ~0.338 ms / query Sum: ~29.709 ms https://explain.depesz.com/s/fFpj All execution times (88) [0.596, 0.433, 0.375, 0.43, 0.349, 0.34, 0.369, 0.352, 0.353, 0.374, 0.353, 0.352, 0.343, 0.338, 0.368, 0.346, 0.322, 0.304, 0.301, 0.305, 0.313, 0.322, 0.313, 0.295, 0.342, 0.305, 0.3, 0.301, 0.324, 0.298, 0.293, 0.297, 0.293, 0.316, 0.298, 0.314, 0.309, 0.301, 0.302, 0.303, 0.32, 0.323, 0.343, 0.346, 0.335, 0.359, 0.357, 0.313, 0.412, 0.407, 0.399, 0.395, 0.363, 0.37, 0.363, 0.339, 0.336, 0.359, 0.353, 0.358, 0.315, 0.342, 0.292, 0.304, 0.304, 0.299, 0.307, 0.344, 0.43, 0.352, 0.35, 0.349, 0.338, 0.352, 0.358, 0.345, 0.361, 0.341, 0.369, 0.357, 0.322, 0.353, 0.345, 0.291, 0.308, 0.324, 0.297, 0.068] Step query Average: ~0.336 ms / query Sum: ~29.592 ms https://explain.depesz.com/s/kyAn All execution times (88) [0.479, 0.479, 0.359, 0.405, 0.33, 0.348, 0.353, 0.345, 0.353, 0.396, 0.383, 0.411, 0.333, 0.33, 0.352, 0.331, 0.289, 0.294, 0.294, 0.29, 0.338, 0.331, 0.298, 0.289, 0.304, 0.296, 0.289, 0.291, 0.339, 0.29, 0.34, 0.343, 0.29, 0.302, 0.289, 0.297, 0.336, 0.338, 0.305, 0.29, 0.318, 0.343, 0.327, 0.337, 0.333, 0.335, 0.358, 0.441, 0.395, 0.402, 0.401, 0.433, 0.345, 0.422, 0.394, 0.33, 0.332, 0.385, 0.381, 0.374, 0.293, 0.331, 0.286, 0.29, 0.291, 0.295, 0.293, 0.399, 0.346, 0.339, 0.332, 0.334, 0.332, 0.338, 0.339, 0.345, 0.33, 0.357, 0.334, 0.363, 0.341, 0.325, 0.334, 0.289, 0.302, 0.343, 0.288, 0.068]
split queries - with index on id, starts_at, ends_at, - batch size of 1000 Queries (2) Split query (w/ ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at > '2021-10-11 22:46:44.011359') Split query (w/o ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at IS NULL)	Split query (w/ ends_at) Average: ~0.046 ms / query Sum: ~0.046 ms https://explain.depesz.com/s/3FM2 All execution times (1) [0.046] Split query (w/o ends_at) Average: ~0.082 ms / query Sum: ~0.082 ms https://explain.depesz.com/s/lTVcp All execution times (1) [0.082]	Split query (w/ ends_at) Average: ~0.331 ms / query Sum: ~0.331 ms https://explain.depesz.com/s/9PH7 All execution times (1) [0.331] Split query (w/o ends_at) Average: ~0.632 ms / query Sum: ~0.632 ms https://explain.depesz.com/s/18Ip All execution times (1) [0.632]	Split query (w/ ends_at) Average: ~13.061 ms / query Sum: ~13.061 ms https://explain.depesz.com/s/yUL6 All execution times (1) [13.061] Split query (w/o ends_at) Average: ~24.291 ms / query Sum: ~24.291 ms https://explain.depesz.com/s/O5xK All execution times (1) [24.291]
batched by id, split queries - with index on id, starts_at, ends_at, - batch size of 1000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:01.865238' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:01.865238' Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:01.865238' AND "incident_management_oncall_rotations"."ends_at" IS NULL Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 LIMIT 1 OFFSET 1000	Batch query (w/o NULL ends_at) Average: ~0.039 ms / query Sum: ~0.039 ms https://explain.depesz.com/s/81kC All execution times (1) [0.039] Batch query (w/ NULL ends_at) Average: ~0.073 ms / query Sum: ~0.073 ms https://explain.depesz.com/s/CSaF All execution times (1) [0.073] Step query Average: ~0.054 ms / query Sum: ~0.054 ms https://explain.depesz.com/s/emTO All execution times (1) [0.054]	Batch query (w/o NULL ends_at) Average: ~0.067 ms / query Sum: ~0.2 ms https://explain.depesz.com/s/GtTU All execution times (3) [0.083, 0.079, 0.038] Batch query (w/ NULL ends_at) Average: ~0.263 ms / query Sum: ~0.788 ms https://explain.depesz.com/s/LZr5 All execution times (3) [0.327, 0.318, 0.143] Step query Average: ~0.286 ms / query Sum: ~0.859 ms https://explain.depesz.com/s/EWFl All execution times (3) [0.354, 0.344, 0.161]	Batch query (w/o NULL ends_at) Average: ~0.072 ms / query Sum: ~8.291 ms https://explain.depesz.com/s/pPmE All execution times (115) [0.087, 0.077, 0.077, 0.072, 0.069, 0.074, 0.071, 0.075, 0.077, 0.073, 0.074, 0.069, 0.071, 0.066, 0.064, 0.069, 0.07, 0.067, 0.073, 0.064, 0.064, 0.055, 0.057, 0.071, 0.07, 0.067, 0.074, 0.071, 0.064, 0.072, 0.063, 0.055, 0.057, 0.06, 0.06, 0.056, 0.061, 0.058, 0.062, 0.065, 0.07, 0.066, 0.064, 0.065, 0.079, 0.071, 0.074, 0.082, 0.082, 0.086, 0.162, 0.082, 0.076, 0.069, 0.071, 0.069, 0.069, 0.069, 0.071, 0.078, 0.083, 0.077, 0.076, 0.093, 0.071, 0.076, 0.076, 0.07, 0.072, 0.071, 0.073, 0.065, 0.091, 0.085, 0.082, 0.083, 0.087, 0.082, 0.08, 0.079, 0.079, 0.074, 0.074, 0.077, 0.072, 0.077, 0.075, 0.077, 0.078, 0.073, 0.072, 0.081, 0.063, 0.073, 0.067, 0.069, 0.067, 0.069, 0.069, 0.066, 0.071, 0.07, 0.067, 0.076, 0.077, 0.073, 0.068, 0.062, 0.071, 0.066, 0.067, 0.065, 0.069, 0.074, 0.035] Batch query (w/ NULL ends_at) Average: ~0.281 ms / query Sum: ~32.366 ms https://explain.depesz.com/s/UKH9 All execution times (115) [0.341, 0.342, 0.298, 0.289, 0.302, 0.301, 0.292, 0.321, 0.29, 0.291, 0.286, 0.266, 0.254, 0.275, 0.263, 0.252, 0.258, 0.25, 0.252, 0.243, 0.234, 0.277, 0.269, 0.264, 0.276, 0.277, 0.274, 0.257, 0.344, 0.241, 0.229, 0.223, 0.238, 0.226, 0.221, 0.223, 0.242, 0.237, 0.264, 0.255, 0.266, 0.256, 0.255, 0.253, 0.271, 0.267, 0.292, 0.319, 0.335, 0.326, 0.301, 0.283, 0.28, 0.292, 0.272, 0.285, 0.281, 0.276, 0.282, 0.286, 0.339, 0.285, 0.285, 0.292, 0.27, 0.282, 0.283, 0.279, 0.258, 0.321, 0.263, 0.324, 0.302, 0.305, 0.296, 0.302, 0.285, 0.284, 0.292, 0.282, 0.277, 0.286, 0.27, 0.294, 0.269, 0.275, 0.277, 0.29, 0.263, 0.258, 0.328, 0.242, 0.236, 0.249, 0.253, 0.242, 0.255, 0.257, 0.235, 0.344, 0.331, 0.36, 0.293, 0.282, 0.249, 0.39, 0.396, 0.359, 0.394, 0.399, 0.353, 0.246, 0.236, 0.323, 0.111] Step query Average: ~0.272 ms / query Sum: ~31.247 ms https://explain.depesz.com/s/0tK8 All execution times (115) [0.375, 0.339, 0.312, 0.292, 0.305, 0.325, 0.3, 0.291, 0.327, 0.261, 0.252, 0.266, 0.254, 0.264, 0.258, 0.293, 0.303, 0.349, 0.267, 0.251, 0.254, 0.302, 0.278, 0.276, 0.281, 0.288, 0.289, 0.328, 0.297, 0.287, 0.287, 0.331, 0.286, 0.265, 0.259, 0.253, 0.254, 0.252, 0.252, 0.249, 0.278, 0.252, 0.252, 0.254, 0.248, 0.247, 0.252, 0.253, 0.259, 0.271, 0.265, 0.251, 0.25, 0.253, 0.254, 0.246, 0.245, 0.251, 0.254, 0.284, 0.259, 0.247, 0.247, 0.25, 0.253, 0.251, 0.253, 0.251, 0.246, 0.252, 0.259, 0.251, 0.253, 0.254, 0.248, 0.255, 0.241, 0.269, 0.252, 0.247, 0.252, 0.286, 0.244, 0.249, 0.266, 0.249, 0.25, 0.271, 0.252, 0.267, 0.25, 0.251, 0.251, 0.257, 0.251, 0.251, 0.269, 0.254, 0.256, 0.33, 0.291, 0.347, 0.354, 0.287, 0.308, 0.29, 0.299, 0.29, 0.3, 0.294, 0.337, 0.328, 0.298, 0.298, 0.111]
batched by id/start/end, split queries - with index on id, starts_at, ends_at, - batch size of 1000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:04.793296' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:04.793296' AND "incident_management_oncall_rotations"."id" >= 1571845 AND "incident_management_oncall_rotations"."id" < 1627143 Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:04.793296' AND "incident_management_oncall_rotations"."ends_at" IS NULL AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1573234 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:04.793296' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:04.793296' AND "incident_management_oncall_rotations"."id" >= 1571845 AND "incident_management_oncall_rotations"."id" < 1627143 LIMIT 1 OFFSET 1000	Batch query (w/o NULL ends_at) Average: ~0.024 ms / query Sum: ~0.024 ms https://explain.depesz.com/s/GzE All execution times (1) [0.024] Batch query (w/ NULL ends_at) Average: ~0.085 ms / query Sum: ~0.085 ms https://explain.depesz.com/s/V488 All execution times (1) [0.085] Step query Average: ~0.061 ms / query Sum: ~0.122 ms https://explain.depesz.com/s/Jig5 All execution times (2) [0.027, 0.095]	Batch query (w/o NULL ends_at) Average: ~0.127 ms / query Sum: ~0.127 ms https://explain.depesz.com/s/89oK All execution times (1) [0.127] Batch query (w/ NULL ends_at) Average: ~0.37 ms / query Sum: ~0.74 ms https://explain.depesz.com/s/3Gws All execution times (2) [0.433, 0.307] Step query Average: ~0.28 ms / query Sum: ~0.839 ms https://explain.depesz.com/s/qKJRo All execution times (3) [0.126, 0.424, 0.289]	Batch query (w/o NULL ends_at) Average: ~2.447 ms / query Sum: ~7.34 ms https://explain.depesz.com/s/C2yi All execution times (3) [3.813, 3.158, 0.369] Batch query (w/ NULL ends_at) Average: ~0.348 ms / query Sum: ~29.911 ms https://explain.depesz.com/s/cQL4 All execution times (86) [0.427, 0.377, 0.332, 0.349, 0.362, 0.329, 0.336, 0.39, 0.302, 0.356, 0.343, 0.311, 0.331, 0.357, 0.418, 0.411, 0.606, 0.423, 0.439, 0.446, 0.644, 0.418, 0.426, 0.374, 0.365, 0.455, 0.419, 0.382, 0.378, 0.366, 0.343, 0.366, 0.366, 0.361, 0.369, 0.368, 0.375, 0.367, 0.361, 0.362, 0.347, 0.333, 0.347, 0.346, 0.333, 0.307, 0.301, 0.309, 0.3, 0.3, 0.299, 0.295, 0.301, 0.32, 0.289, 0.306, 0.303, 0.375, 0.292, 0.294, 0.311, 0.3, 0.293, 0.289, 0.296, 0.298, 0.298, 0.299, 0.331, 0.345, 0.362, 0.363, 0.359, 0.314, 0.309, 0.393, 0.337, 0.362, 0.334, 0.384, 0.315, 0.295, 0.303, 0.299, 0.294, 0.021] Step query Average: ~0.407 ms / query Sum: ~36.229 ms https://explain.depesz.com/s/LhC7 All execution times (89) [3.69, 3.261, 0.344, 0.424, 0.374, 0.347, 0.339, 0.336, 0.324, 0.322, 0.33, 0.331, 0.34, 0.337, 0.293, 0.326, 0.338, 0.423, 0.4, 0.498, 0.41, 0.415, 0.58, 0.441, 0.411, 0.39, 0.356, 0.347, 0.412, 0.398, 0.367, 0.366, 0.356, 0.335, 0.342, 0.348, 0.344, 0.351, 0.347, 0.366, 0.348, 0.351, 0.353, 0.337, 0.325, 0.351, 0.336, 0.302, 0.297, 0.301, 0.286, 0.287, 0.342, 0.295, 0.285, 0.295, 0.292, 0.286, 0.296, 0.299, 0.314, 0.287, 0.286, 0.285, 0.3, 0.286, 0.297, 0.287, 0.285, 0.287, 0.288, 0.32, 0.337, 0.334, 0.339, 0.326, 0.388, 0.575, 0.344, 0.328, 0.34, 0.304, 0.287, 0.29, 0.288, 0.295, 0.301, 0.285, 0.023]

single query (ORIGINAL) - with index on starts_at, ends_at, and id, - batch size of 1000 Queries (1) Single query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL))	Single query Average: ~0.076 ms / query Sum: ~0.076 ms https://explain.depesz.com/s/5eQv All execution times (1) [0.076]	Single query Average: ~1.156 ms / query Sum: ~1.156 ms https://explain.depesz.com/s/avga All execution times (1) [1.156]	Single query Average: ~49.925 ms / query Sum: ~49.925 ms https://explain.depesz.com/s/r9hq All execution times (1) [49.925]
batched by id, single query - with index on starts_at, ends_at, and id, - batch size of 1000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 AND (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 LIMIT 1 OFFSET 1000	Batch query Average: ~0.096 ms / query Sum: ~0.096 ms https://explain.depesz.com/s/brZL All execution times (1) [0.096] Step query Average: ~0.054 ms / query Sum: ~0.054 ms https://explain.depesz.com/s/QN1R All execution times (1) [0.054]	Batch query Average: ~0.325 ms / query Sum: ~0.976 ms https://explain.depesz.com/s/Awfl All execution times (3) [0.331, 0.338, 0.307] Step query Average: ~0.311 ms / query Sum: ~0.933 ms https://explain.depesz.com/s/Y8iB All execution times (3) [0.333, 0.319, 0.281]	Batch query Average: ~0.295 ms / query Sum: ~33.927 ms https://explain.depesz.com/s/hpNZ All execution times (115) [0.414, 0.383, 0.337, 0.3, 0.284, 0.277, 0.285, 0.276, 0.281, 0.28, 0.267, 0.255, 0.234, 0.258, 0.275, 0.232, 0.232, 0.325, 0.244, 0.23, 0.232, 0.231, 0.234, 0.235, 0.234, 0.235, 0.243, 0.231, 0.236, 0.313, 0.254, 0.239, 0.246, 0.242, 0.236, 0.23, 0.23, 0.286, 0.242, 0.238, 0.27, 0.268, 0.276, 0.275, 0.279, 0.265, 0.265, 0.267, 0.236, 0.341, 0.321, 0.311, 0.309, 0.295, 0.283, 0.284, 0.276, 0.267, 0.268, 0.275, 0.264, 0.257, 0.236, 0.272, 0.235, 0.232, 0.236, 0.237, 0.26, 0.272, 0.269, 0.274, 0.266, 0.27, 0.279, 0.269, 0.269, 0.261, 0.27, 0.266, 0.283, 0.277, 0.268, 0.244, 0.23, 0.244, 0.231, 0.234, 0.234, 0.24, 0.234, 0.235, 0.235, 0.288, 0.234, 0.233, 0.231, 0.232, 0.236, 0.231, 0.233, 0.234, 0.234, 0.232, 0.234, 0.233, 0.241, 0.234, 0.234, 0.243, 0.294, 0.272, 0.257, 0.251, 4.266] Step query Average: ~0.26 ms / query Sum: ~29.926 ms https://explain.depesz.com/s/udCJ All execution times (115) [0.388, 0.373, 0.346, 0.3, 0.281, 0.273, 0.273, 0.284, 0.265, 0.269, 0.265, 0.244, 0.233, 0.244, 0.243, 0.245, 0.244, 0.233, 0.232, 0.232, 0.232, 0.327, 0.233, 0.246, 0.312, 0.234, 0.231, 0.232, 0.233, 0.233, 0.246, 0.245, 0.234, 0.242, 0.233, 0.232, 0.233, 0.241, 0.246, 0.233, 0.277, 0.275, 0.266, 0.294, 0.267, 0.274, 0.266, 0.273, 0.232, 0.339, 0.333, 0.321, 0.301, 0.313, 0.282, 0.293, 0.283, 0.278, 0.277, 0.277, 0.279, 0.257, 0.371, 0.289, 0.245, 0.244, 0.315, 0.242, 0.284, 0.279, 0.267, 0.265, 0.265, 0.264, 0.272, 0.285, 0.279, 0.285, 0.264, 0.265, 0.269, 0.265, 0.264, 0.233, 0.231, 0.232, 0.233, 0.233, 0.233, 0.232, 0.234, 0.233, 0.237, 0.233, 0.232, 0.232, 0.233, 0.238, 0.233, 0.233, 0.247, 0.246, 0.231, 0.251, 0.241, 0.233, 0.244, 0.244, 0.276, 0.233, 0.265, 0.267, 0.244, 0.24, 0.099]
batched by id/start/end, single query - with index on starts_at, ends_at, and id, - batch size of 1000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1573191 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1573191 LIMIT 1 OFFSET 1000	Batch query Average: ~0.106 ms / query Sum: ~0.106 ms https://explain.depesz.com/s/Eu1S All execution times (1) [0.106] Step query Average: ~0.104 ms / query Sum: ~0.104 ms https://explain.depesz.com/s/Gp7v All execution times (1) [0.104]	Batch query Average: ~0.474 ms / query Sum: ~0.948 ms https://explain.depesz.com/s/iPbH All execution times (2) [0.387, 0.561] Step query Average: ~0.457 ms / query Sum: ~0.914 ms https://explain.depesz.com/s/jEHZ All execution times (2) [0.391, 0.523]	Batch query Average: ~0.381 ms / query Sum: ~33.537 ms https://explain.depesz.com/s/WhdS All execution times (88) [0.463, 0.525, 0.421, 0.411, 0.394, 0.372, 0.377, 0.398, 0.403, 0.382, 0.368, 0.386, 0.371, 0.359, 0.379, 0.37, 0.354, 0.394, 0.357, 0.355, 0.353, 0.337, 0.345, 0.301, 0.305, 0.324, 0.318, 0.338, 0.306, 0.329, 0.306, 0.301, 0.306, 0.373, 0.301, 0.308, 0.299, 0.321, 0.309, 0.305, 0.311, 0.308, 0.3, 0.309, 0.303, 0.308, 0.336, 0.364, 0.358, 0.35, 0.35, 0.33, 0.346, 0.332, 0.317, 0.365, 0.346, 0.341, 0.319, 0.353, 0.312, 0.31, 0.304, 0.311, 0.313, 0.313, 0.297, 0.304, 0.311, 0.31, 0.439, 0.306, 0.312, 0.318, 0.305, 0.31, 0.299, 0.298, 0.307, 0.341, 0.307, 0.312, 0.346, 0.351, 0.328, 0.316, 0.314, 4.073] Step query Average: ~0.373 ms / query Sum: ~32.807 ms https://explain.depesz.com/s/vhb4 All execution times (88) [0.477, 0.472, 0.409, 0.404, 0.402, 0.395, 0.367, 0.385, 0.435, 0.374, 0.359, 0.366, 0.364, 0.347, 0.357, 0.356, 0.34, 0.349, 0.343, 0.342, 0.344, 0.323, 0.328, 0.297, 0.301, 0.31, 0.327, 0.303, 0.299, 0.295, 0.303, 0.299, 0.3, 0.305, 0.298, 0.305, 0.333, 0.301, 0.305, 0.301, 0.299, 0.301, 0.301, 0.307, 0.3, 0.301, 0.346, 0.345, 0.34, 0.346, 0.346, 0.314, 0.336, 0.324, 0.346, 0.341, 0.337, 0.323, 0.302, 0.322, 0.31, 0.372, 0.295, 0.304, 0.305, 0.306, 0.299, 0.303, 0.313, 0.3, 0.303, 0.302, 0.3, 0.309, 0.304, 0.316, 0.295, 0.296, 0.303, 0.305, 0.302, 0.302, 0.365, 0.342, 0.318, 0.306, 0.343, 4.092]
split queries - with index on starts_at, ends_at, and id, - batch size of 1000 Queries (2) Split query (w/ ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at > '2021-10-11 22:46:44.011359') Split query (w/o ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at IS NULL)	Split query (w/ ends_at) Average: ~0.036 ms / query Sum: ~0.036 ms https://explain.depesz.com/s/8ZQl All execution times (1) [0.036] Split query (w/o ends_at) Average: ~0.1 ms / query Sum: ~0.1 ms https://explain.depesz.com/s/nuKS All execution times (1) [0.1]	Split query (w/ ends_at) Average: ~0.139 ms / query Sum: ~0.139 ms https://explain.depesz.com/s/uq1m All execution times (1) [0.139] Split query (w/o ends_at) Average: ~0.94 ms / query Sum: ~0.94 ms https://explain.depesz.com/s/X3lyJ All execution times (1) [0.94]	Split query (w/ ends_at) Average: ~4.352 ms / query Sum: ~4.352 ms https://explain.depesz.com/s/V5B8 All execution times (1) [4.352] Split query (w/o ends_at) Average: ~42.064 ms / query Sum: ~42.064 ms https://explain.depesz.com/s/S5Zs All execution times (1) [42.064]
batched by id, split queries - with index on starts_at, ends_at, and id, - batch size of 1000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:13.457025' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:13.457025' Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:13.457025' AND "incident_management_oncall_rotations"."ends_at" IS NULL Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1572838 LIMIT 1 OFFSET 1000	Batch query (w/o NULL ends_at) Average: ~0.021 ms / query Sum: ~0.021 ms https://explain.depesz.com/s/XBK4 All execution times (1) [0.021] Batch query (w/ NULL ends_at) Average: ~0.076 ms / query Sum: ~0.076 ms https://explain.depesz.com/s/EoXa All execution times (1) [0.076] Step query Average: ~0.053 ms / query Sum: ~0.053 ms https://explain.depesz.com/s/Rwbo All execution times (1) [0.053]	Batch query (w/o NULL ends_at) Average: ~0.199 ms / query Sum: ~0.596 ms https://explain.depesz.com/s/jOEK All execution times (3) [0.209, 0.294, 0.093] Batch query (w/ NULL ends_at) Average: ~0.304 ms / query Sum: ~0.911 ms https://explain.depesz.com/s/pBja All execution times (3) [0.289, 0.337, 0.285] Step query Average: ~0.306 ms / query Sum: ~0.917 ms https://explain.depesz.com/s/JiYh All execution times (3) [0.327, 0.314, 0.276]	Batch query (w/o NULL ends_at) Average: ~0.192 ms / query Sum: ~22.128 ms https://explain.depesz.com/s/actj All execution times (115) [0.242, 0.254, 0.221, 0.185, 0.179, 0.185, 0.174, 0.176, 0.175, 0.159, 0.155, 0.214, 0.209, 0.209, 0.197, 0.199, 0.193, 0.205, 0.202, 0.206, 0.193, 0.196, 0.186, 0.187, 0.179, 0.183, 0.189, 0.186, 0.181, 0.182, 0.181, 0.176, 0.175, 0.178, 0.158, 0.175, 0.174, 0.197, 0.154, 0.156, 0.157, 0.156, 0.155, 0.156, 0.157, 0.155, 0.157, 0.155, 0.154, 0.155, 0.156, 0.242, 0.157, 0.153, 0.154, 0.153, 0.154, 0.155, 0.156, 0.154, 0.159, 0.178, 0.177, 0.176, 0.177, 0.178, 0.179, 0.175, 0.156, 0.176, 0.169, 0.154, 0.157, 0.157, 0.179, 0.155, 0.2, 0.155, 0.154, 0.153, 0.153, 0.154, 0.153, 0.155, 0.152, 0.155, 0.153, 0.155, 0.156, 0.155, 0.155, 0.225, 0.155, 0.158, 0.19, 0.155, 0.154, 0.223, 0.155, 0.154, 0.156, 0.155, 0.152, 0.245, 0.171, 0.185, 0.178, 0.175, 0.155, 0.154, 0.155, 0.158, 0.158, 0.162, 2.424] Batch query (w/ NULL ends_at) Average: ~0.291 ms / query Sum: ~33.489 ms https://explain.depesz.com/s/cX3N All execution times (115) [0.484, 0.355, 0.301, 0.265, 0.285, 0.271, 0.261, 0.272, 0.272, 0.229, 0.347, 0.313, 0.316, 0.299, 0.286, 0.286, 0.284, 0.292, 0.293, 0.289, 0.285, 0.286, 0.279, 0.265, 0.268, 0.275, 0.269, 0.267, 0.273, 0.259, 0.266, 0.262, 0.261, 0.239, 0.266, 0.261, 0.242, 0.227, 0.232, 0.231, 0.23, 0.232, 0.239, 0.229, 0.233, 0.228, 0.227, 0.226, 0.229, 0.229, 0.228, 0.278, 0.234, 0.229, 0.228, 0.232, 0.361, 0.227, 0.227, 0.226, 0.238, 0.258, 0.259, 0.262, 0.263, 0.256, 0.261, 0.243, 0.266, 0.26, 0.228, 0.29, 0.245, 0.273, 0.24, 0.231, 0.241, 0.23, 0.227, 0.226, 0.237, 0.23, 0.245, 0.225, 0.347, 0.226, 0.225, 0.231, 0.236, 0.227, 0.235, 0.227, 0.224, 0.228, 0.228, 0.223, 0.225, 0.226, 0.227, 0.225, 0.227, 0.233, 0.308, 0.238, 0.257, 0.258, 0.261, 0.253, 0.226, 0.229, 0.23, 0.241, 0.239, 0.229, 4.381] Step query Average: ~0.274 ms / query Sum: ~31.53 ms https://explain.depesz.com/s/F4Ja All execution times (115) [0.323, 0.319, 0.29, 0.269, 0.304, 0.267, 0.279, 0.243, 0.236, 0.236, 0.236, 0.25, 0.248, 0.247, 0.245, 0.249, 0.252, 0.246, 0.245, 0.249, 0.247, 0.237, 0.311, 0.256, 0.248, 0.246, 0.244, 0.238, 0.236, 0.237, 0.236, 0.235, 0.235, 0.328, 0.322, 0.323, 0.337, 0.336, 0.339, 0.344, 0.336, 0.334, 0.336, 0.316, 0.305, 0.304, 0.303, 0.31, 0.305, 0.308, 0.307, 0.286, 0.284, 0.286, 0.286, 0.292, 0.316, 0.293, 0.307, 0.296, 0.291, 0.29, 0.294, 0.287, 0.341, 0.276, 0.278, 0.276, 0.275, 0.295, 0.288, 0.272, 0.269, 0.269, 0.272, 0.27, 0.269, 0.269, 0.268, 0.268, 0.268, 0.269, 0.27, 0.376, 0.268, 0.267, 0.283, 0.268, 0.27, 0.276, 0.271, 0.274, 0.267, 0.272, 0.269, 0.269, 0.268, 0.248, 0.236, 0.249, 0.246, 0.249, 0.252, 0.281, 0.237, 0.237, 0.236, 0.242, 0.236, 0.249, 0.247, 0.247, 0.24, 0.24, 0.158]
batched by id/start/end, split queries - with index on starts_at, ends_at, and id, - batch size of 1000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:16.353701' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:16.353701' AND "incident_management_oncall_rotations"."id" >= 1571845 AND "incident_management_oncall_rotations"."id" < 1627143 Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:16.353701' AND "incident_management_oncall_rotations"."ends_at" IS NULL AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1573234 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:16.353701' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:16.353701' AND "incident_management_oncall_rotations"."id" >= 1571845 AND "incident_management_oncall_rotations"."id" < 1627143 LIMIT 1 OFFSET 1000	Batch query (w/o NULL ends_at) Average: ~0.025 ms / query Sum: ~0.025 ms https://explain.depesz.com/s/vkYF All execution times (1) [0.025] Batch query (w/ NULL ends_at) Average: ~0.115 ms / query Sum: ~0.115 ms https://explain.depesz.com/s/anTv All execution times (1) [0.115] Step query Average: ~0.078 ms / query Sum: ~0.156 ms https://explain.depesz.com/s/pwko All execution times (2) [0.054, 0.102]	Batch query (w/o NULL ends_at) Average: ~0.1 ms / query Sum: ~0.1 ms https://explain.depesz.com/s/dsWG All execution times (1) [0.1] Batch query (w/ NULL ends_at) Average: ~0.471 ms / query Sum: ~0.942 ms https://explain.depesz.com/s/2szX All execution times (2) [0.443, 0.499] Step query Average: ~0.339 ms / query Sum: ~1.017 ms https://explain.depesz.com/s/ZjlX All execution times (3) [0.11, 0.449, 0.458]	Batch query (w/o NULL ends_at) Average: ~8.097 ms / query Sum: ~24.29 ms https://explain.depesz.com/s/HQrT All execution times (3) [11.295, 10.095, 2.9] Batch query (w/ NULL ends_at) Average: ~0.431 ms / query Sum: ~37.054 ms https://explain.depesz.com/s/eIOf All execution times (86) [0.485, 0.487, 0.451, 0.412, 0.406, 0.394, 0.386, 0.392, 0.398, 0.403, 0.383, 0.386, 0.387, 0.388, 0.377, 0.381, 0.401, 0.42, 0.383, 0.363, 0.419, 0.386, 0.366, 0.389, 0.383, 0.375, 0.387, 0.375, 0.426, 0.382, 0.376, 0.379, 0.426, 0.367, 0.403, 0.459, 0.406, 0.395, 0.376, 0.406, 0.395, 0.37, 0.381, 0.382, 0.372, 0.369, 0.363, 0.365, 0.382, 0.38, 0.38, 0.414, 0.376, 0.378, 0.364, 0.377, 0.37, 0.367, 0.365, 0.36, 0.366, 0.351, 0.379, 0.401, 0.378, 0.374, 0.375, 0.384, 0.374, 0.366, 0.352, 0.358, 0.352, 0.371, 0.37, 0.355, 0.342, 0.366, 0.354, 0.355, 0.37, 0.378, 0.36, 0.378, 0.35, 4.421] Step query Average: ~0.702 ms / query Sum: ~62.446 ms https://explain.depesz.com/s/UF19 All execution times (89) [13.765, 9.906, 3.218, 0.465, 0.462, 0.404, 0.39, 0.387, 0.38, 0.382, 0.357, 0.382, 0.38, 0.368, 0.363, 0.383, 0.35, 0.362, 0.367, 0.377, 0.385, 0.364, 0.393, 0.416, 0.354, 0.346, 0.409, 0.369, 0.356, 0.383, 0.363, 0.361, 0.378, 0.355, 0.363, 0.393, 0.354, 0.379, 0.416, 0.397, 0.38, 0.38, 0.394, 0.374, 0.357, 0.368, 0.363, 0.355, 0.356, 0.351, 0.353, 0.371, 0.387, 0.367, 0.365, 0.363, 0.366, 0.353, 0.362, 0.369, 0.343, 0.356, 0.342, 0.338, 0.35, 0.384, 0.374, 0.363, 0.362, 0.348, 0.369, 0.354, 0.352, 0.343, 0.352, 0.346, 0.355, 0.344, 0.34, 0.339, 0.352, 0.35, 0.341, 0.359, 0.369, 0.355, 0.358, 0.345, 4.247]

single query (ORIGINAL) - without index, - batch size of 10000 Queries (1) Single query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL))	Single query Average: ~0.067 ms / query Sum: ~0.067 ms https://explain.depesz.com/s/5csv All execution times (1) [0.067]	Single query Average: ~0.583 ms / query Sum: ~0.583 ms https://explain.depesz.com/s/EBw1 All execution times (1) [0.583]	Single query Average: ~20.266 ms / query Sum: ~20.266 ms https://explain.depesz.com/s/Huth All execution times (1) [20.266]
batched by id, single query - without index, - batch size of 10000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 AND (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 LIMIT 1 OFFSET 10000	Batch query Average: ~0.062 ms / query Sum: ~0.062 ms https://explain.depesz.com/s/7qPyM All execution times (1) [0.062] Step query Average: ~0.063 ms / query Sum: ~0.063 ms https://explain.depesz.com/s/Obga All execution times (1) [0.063]	Batch query Average: ~0.582 ms / query Sum: ~0.582 ms https://explain.depesz.com/s/FOnb All execution times (1) [0.582] Step query Average: ~0.746 ms / query Sum: ~0.746 ms https://explain.depesz.com/s/k8KF All execution times (1) [0.746]	Batch query Average: ~2.28 ms / query Sum: ~27.363 ms https://explain.depesz.com/s/69DQ All execution times (12) [2.775, 2.348, 2.34, 2.341, 2.382, 2.342, 2.331, 2.351, 2.489, 2.342, 2.348, 0.974] Step query Average: ~2.253 ms / query Sum: ~27.035 ms https://explain.depesz.com/s/VEbw All execution times (12) [2.87, 2.412, 2.388, 2.26, 2.268, 2.258, 2.268, 2.393, 2.247, 2.425, 2.253, 0.993]
batched by id/start/end, single query - without index, - batch size of 10000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1584919 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1584919 LIMIT 1 OFFSET 10000	Batch query Average: ~0.064 ms / query Sum: ~0.064 ms https://explain.depesz.com/s/Panr All execution times (1) [0.064] Step query Average: ~0.065 ms / query Sum: ~0.065 ms https://explain.depesz.com/s/Mvd8 All execution times (1) [0.065]	Batch query Average: ~0.519 ms / query Sum: ~0.519 ms https://explain.depesz.com/s/iCO5 All execution times (1) [0.519] Step query Average: ~0.577 ms / query Sum: ~0.577 ms https://explain.depesz.com/s/gCiO All execution times (1) [0.577]	Batch query Average: ~3.161 ms / query Sum: ~28.446 ms https://explain.depesz.com/s/tSpn All execution times (9) [3.991, 3.665, 3.141, 3.097, 3.072, 3.093, 3.091, 3.126, 2.17] Step query Average: ~3.048 ms / query Sum: ~27.433 ms https://explain.depesz.com/s/kQee All execution times (9) [3.705, 3.316, 2.968, 3.248, 3.191, 2.928, 3.009, 2.942, 2.126]
split queries - without index, - batch size of 10000 Queries (2) Split query (w/ ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at > '2021-10-11 22:46:44.011359') Split query (w/o ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at IS NULL)	Split query (w/ ends_at) Average: ~0.049 ms / query Sum: ~0.049 ms https://explain.depesz.com/s/2Lsv All execution times (1) [0.049] Split query (w/o ends_at) Average: ~0.06 ms / query Sum: ~0.06 ms https://explain.depesz.com/s/p8HO All execution times (1) [0.06]	Split query (w/ ends_at) Average: ~0.352 ms / query Sum: ~0.352 ms https://explain.depesz.com/s/SuoE All execution times (1) [0.352] Split query (w/o ends_at) Average: ~0.643 ms / query Sum: ~0.643 ms https://explain.depesz.com/s/5FeM All execution times (1) [0.643]	Split query (w/ ends_at) Average: ~13.23 ms / query Sum: ~13.23 ms https://explain.depesz.com/s/Jr1i All execution times (1) [13.23] Split query (w/o ends_at) Average: ~20.96 ms / query Sum: ~20.96 ms https://explain.depesz.com/s/cZ3z All execution times (1) [20.96]
batched by id, split queries - without index, - batch size of 10000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:26.180873' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:26.180873' Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:26.180873' AND "incident_management_oncall_rotations"."ends_at" IS NULL Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 LIMIT 1 OFFSET 10000	Batch query (w/o NULL ends_at) Average: ~0.042 ms / query Sum: ~0.042 ms https://explain.depesz.com/s/o588 All execution times (1) [0.042] Batch query (w/ NULL ends_at) Average: ~0.08 ms / query Sum: ~0.08 ms https://explain.depesz.com/s/tBZD All execution times (1) [0.08] Step query Average: ~0.062 ms / query Sum: ~0.062 ms https://explain.depesz.com/s/UMDH All execution times (1) [0.062]	Batch query (w/o NULL ends_at) Average: ~0.325 ms / query Sum: ~0.325 ms https://explain.depesz.com/s/SsMS All execution times (1) [0.325] Batch query (w/ NULL ends_at) Average: ~0.639 ms / query Sum: ~0.639 ms https://explain.depesz.com/s/yh6Z All execution times (1) [0.639] Step query Average: ~0.549 ms / query Sum: ~0.549 ms https://explain.depesz.com/s/N2TFM All execution times (1) [0.549]	Batch query (w/o NULL ends_at) Average: ~1.542 ms / query Sum: ~18.509 ms https://explain.depesz.com/s/KBPV All execution times (12) [2.22, 1.809, 1.776, 1.447, 1.464, 1.664, 1.465, 1.458, 1.468, 1.655, 1.471, 0.612] Batch query (w/ NULL ends_at) Average: ~2.309 ms / query Sum: ~27.702 ms https://explain.depesz.com/s/F1J3 All execution times (12) [2.904, 2.573, 2.295, 2.27, 2.352, 2.528, 2.27, 2.397, 2.443, 2.447, 2.282, 0.941] Step query Average: ~2.712 ms / query Sum: ~32.545 ms https://explain.depesz.com/s/B5Im All execution times (12) [2.399, 2.273, 2.269, 2.269, 3.32, 3.179, 3.124, 3.126, 3.17, 3.023, 3.117, 1.276]
batched by id/start/end, split queries - without index, - batch size of 10000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:28.191268' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:28.191268' AND "incident_management_oncall_rotations"."id" >= 1571845 Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:28.191268' AND "incident_management_oncall_rotations"."ends_at" IS NULL AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1585212 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:28.191268' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:28.191268' AND "incident_management_oncall_rotations"."id" >= 1571845 LIMIT 1 OFFSET 10000	Batch query (w/o NULL ends_at) Average: ~0.041 ms / query Sum: ~0.041 ms https://explain.depesz.com/s/C4Q1 All execution times (1) [0.041] Batch query (w/ NULL ends_at) Average: ~0.071 ms / query Sum: ~0.071 ms https://explain.depesz.com/s/7xBb All execution times (1) [0.071] Step query Average: ~0.054 ms / query Sum: ~0.107 ms https://explain.depesz.com/s/iSDb All execution times (2) [0.042, 0.065]	Batch query (w/o NULL ends_at) Average: ~0.321 ms / query Sum: ~0.321 ms https://explain.depesz.com/s/6JCHt All execution times (1) [0.321] Batch query (w/ NULL ends_at) Average: ~0.633 ms / query Sum: ~0.633 ms https://explain.depesz.com/s/HI7I All execution times (1) [0.633] Step query Average: ~0.45 ms / query Sum: ~0.899 ms https://explain.depesz.com/s/iXTc All execution times (2) [0.313, 0.586]	Batch query (w/o NULL ends_at) Average: ~20.612 ms / query Sum: ~20.612 ms https://explain.depesz.com/s/wcadr All execution times (1) [20.612] Batch query (w/ NULL ends_at) Average: ~2.993 ms / query Sum: ~26.937 ms https://explain.depesz.com/s/8oNw All execution times (9) [3.644, 3.074, 3.09, 3.13, 3.055, 3.126, 3.135, 3.146, 1.537] Step query Average: ~4.526 ms / query Sum: ~45.255 ms https://explain.depesz.com/s/zKq4U All execution times (10) [19.296, 3.59, 3.231, 2.911, 2.954, 2.982, 2.927, 2.941, 2.95, 1.473]

single query (ORIGINAL) - with index on id, starts_at, ends_at, - batch size of 10000 Queries (1) Single query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL))	Single query Average: ~0.067 ms / query Sum: ~0.067 ms https://explain.depesz.com/s/YMhn All execution times (1) [0.067]	Single query Average: ~0.91 ms / query Sum: ~0.91 ms https://explain.depesz.com/s/pWaJ All execution times (1) [0.91]	Single query Average: ~20.542 ms / query Sum: ~20.542 ms https://explain.depesz.com/s/jDK8 All execution times (1) [20.542]
batched by id, single query - with index on id, starts_at, ends_at, - batch size of 10000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 AND (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 LIMIT 1 OFFSET 10000	Batch query Average: ~0.082 ms / query Sum: ~0.082 ms https://explain.depesz.com/s/3YsA All execution times (1) [0.082] Step query Average: ~0.055 ms / query Sum: ~0.055 ms https://explain.depesz.com/s/u4KVg All execution times (1) [0.055]	Batch query Average: ~0.801 ms / query Sum: ~0.801 ms https://explain.depesz.com/s/JoRf All execution times (1) [0.801] Step query Average: ~0.82 ms / query Sum: ~0.82 ms https://explain.depesz.com/s/BNPe All execution times (1) [0.82]	Batch query Average: ~2.373 ms / query Sum: ~28.473 ms https://explain.depesz.com/s/foHP All execution times (12) [2.832, 2.681, 2.288, 2.308, 2.422, 2.296, 2.371, 2.906, 2.569, 2.296, 2.416, 1.088] Step query Average: ~2.414 ms / query Sum: ~28.967 ms https://explain.depesz.com/s/0C01 All execution times (12) [2.983, 2.597, 2.354, 2.259, 2.549, 2.56, 2.27, 3.072, 2.438, 2.421, 2.464, 1.0]
batched by id/start/end, single query - with index on id, starts_at, ends_at, - batch size of 10000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1584919 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1584919 LIMIT 1 OFFSET 10000	Batch query Average: ~0.113 ms / query Sum: ~0.113 ms https://explain.depesz.com/s/lFD4 All execution times (1) [0.113] Step query Average: ~0.097 ms / query Sum: ~0.097 ms https://explain.depesz.com/s/71Ky All execution times (1) [0.097]	Batch query Average: ~1.032 ms / query Sum: ~1.032 ms https://explain.depesz.com/s/DBuv All execution times (1) [1.032] Step query Average: ~0.881 ms / query Sum: ~0.881 ms https://explain.depesz.com/s/YLw8 All execution times (1) [0.881]	Batch query Average: ~3.106 ms / query Sum: ~27.956 ms https://explain.depesz.com/s/rPRV All execution times (9) [3.58, 3.445, 3.063, 3.093, 3.017, 3.077, 3.143, 3.19, 2.348] Step query Average: ~2.955 ms / query Sum: ~26.596 ms https://explain.depesz.com/s/oz6n All execution times (9) [3.379, 3.235, 3.071, 2.923, 2.863, 2.814, 2.995, 2.919, 2.397]
split queries - with index on id, starts_at, ends_at, - batch size of 10000 Queries (2) Split query (w/ ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at > '2021-10-11 22:46:44.011359') Split query (w/o ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at IS NULL)	Split query (w/ ends_at) Average: ~0.05 ms / query Sum: ~0.05 ms https://explain.depesz.com/s/Gvd1 All execution times (1) [0.05] Split query (w/o ends_at) Average: ~0.062 ms / query Sum: ~0.062 ms https://explain.depesz.com/s/jDeu All execution times (1) [0.062]	Split query (w/ ends_at) Average: ~0.352 ms / query Sum: ~0.352 ms https://explain.depesz.com/s/9fJOZ All execution times (1) [0.352] Split query (w/o ends_at) Average: ~0.884 ms / query Sum: ~0.884 ms https://explain.depesz.com/s/auon All execution times (1) [0.884]	Split query (w/ ends_at) Average: ~13.424 ms / query Sum: ~13.424 ms https://explain.depesz.com/s/KdpN All execution times (1) [13.424] Split query (w/o ends_at) Average: ~23.379 ms / query Sum: ~23.379 ms https://explain.depesz.com/s/NSs5 All execution times (1) [23.379]
batched by id, split queries - with index on id, starts_at, ends_at, - batch size of 10000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:35.182823' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:35.182823' Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:35.182823' AND "incident_management_oncall_rotations"."ends_at" IS NULL Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 LIMIT 1 OFFSET 10000	Batch query (w/o NULL ends_at) Average: ~0.029 ms / query Sum: ~0.029 ms https://explain.depesz.com/s/pZRC All execution times (1) [0.029] Batch query (w/ NULL ends_at) Average: ~0.171 ms / query Sum: ~0.171 ms https://explain.depesz.com/s/89dH All execution times (1) [0.171] Step query Average: ~0.068 ms / query Sum: ~0.068 ms https://explain.depesz.com/s/XJKr All execution times (1) [0.068]	Batch query (w/o NULL ends_at) Average: ~0.137 ms / query Sum: ~0.137 ms https://explain.depesz.com/s/ls0t All execution times (1) [0.137] Batch query (w/ NULL ends_at) Average: ~0.754 ms / query Sum: ~0.754 ms https://explain.depesz.com/s/HZUp All execution times (1) [0.754] Step query Average: ~0.855 ms / query Sum: ~0.855 ms https://explain.depesz.com/s/a8wL All execution times (1) [0.855]	Batch query (w/o NULL ends_at) Average: ~0.66 ms / query Sum: ~7.915 ms https://explain.depesz.com/s/YOYY All execution times (12) [0.997, 0.699, 0.661, 0.623, 0.71, 0.633, 0.655, 0.638, 0.683, 0.651, 0.712, 0.253] Batch query (w/ NULL ends_at) Average: ~2.562 ms / query Sum: ~30.739 ms https://explain.depesz.com/s/W205 All execution times (12) [3.117, 2.754, 2.624, 2.691, 2.616, 2.53, 2.672, 2.662, 2.748, 2.619, 2.626, 1.08] Step query Average: ~2.953 ms / query Sum: ~35.436 ms https://explain.depesz.com/s/pP9Q All execution times (12) [2.997, 2.834, 3.112, 3.159, 3.115, 3.102, 3.249, 3.173, 3.134, 3.158, 3.109, 1.294]
batched by id/start/end, split queries - with index on id, starts_at, ends_at, - batch size of 10000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:37.346034' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:37.346034' AND "incident_management_oncall_rotations"."id" >= 1571845 Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:37.346034' AND "incident_management_oncall_rotations"."ends_at" IS NULL AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1585212 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:37.346034' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:37.346034' AND "incident_management_oncall_rotations"."id" >= 1571845 LIMIT 1 OFFSET 10000	Batch query (w/o NULL ends_at) Average: ~0.03 ms / query Sum: ~0.03 ms https://explain.depesz.com/s/KeKm All execution times (1) [0.03] Batch query (w/ NULL ends_at) Average: ~0.082 ms / query Sum: ~0.082 ms https://explain.depesz.com/s/fX1P All execution times (1) [0.082] Step query Average: ~0.054 ms / query Sum: ~0.107 ms https://explain.depesz.com/s/FGrl All execution times (2) [0.029, 0.078]	Batch query (w/o NULL ends_at) Average: ~0.134 ms / query Sum: ~0.134 ms https://explain.depesz.com/s/wEfl All execution times (1) [0.134] Batch query (w/ NULL ends_at) Average: ~0.731 ms / query Sum: ~0.731 ms https://explain.depesz.com/s/8iV3 All execution times (1) [0.731] Step query Average: ~0.404 ms / query Sum: ~0.807 ms https://explain.depesz.com/s/IPxX All execution times (2) [0.133, 0.674]	Batch query (w/o NULL ends_at) Average: ~6.01 ms / query Sum: ~6.01 ms https://explain.depesz.com/s/8mya All execution times (1) [6.01] Batch query (w/ NULL ends_at) Average: ~3.016 ms / query Sum: ~27.146 ms https://explain.depesz.com/s/MjGR All execution times (9) [3.579, 3.409, 3.017, 3.367, 3.113, 3.034, 3.115, 3.047, 1.465] Step query Average: ~3.199 ms / query Sum: ~31.992 ms https://explain.depesz.com/s/71fx All execution times (10) [5.828, 3.277, 3.423, 2.831, 3.487, 2.91, 2.992, 2.794, 3.047, 1.403]

single query (ORIGINAL) - with index on starts_at, ends_at, and id, - batch size of 10000 Queries (1) Single query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL))	Single query Average: ~0.075 ms / query Sum: ~0.075 ms https://explain.depesz.com/s/f059 All execution times (1) [0.075]	Single query Average: ~1.182 ms / query Sum: ~1.182 ms https://explain.depesz.com/s/fNdh All execution times (1) [1.182]	Single query Average: ~55.023 ms / query Sum: ~55.023 ms https://explain.depesz.com/s/imJ4 All execution times (1) [55.023]
batched by id, single query - with index on starts_at, ends_at, and id, - batch size of 10000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 AND (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 LIMIT 1 OFFSET 10000	Batch query Average: ~0.117 ms / query Sum: ~0.117 ms https://explain.depesz.com/s/8Evs All execution times (1) [0.117] Step query Average: ~0.059 ms / query Sum: ~0.059 ms https://explain.depesz.com/s/gjIa All execution times (1) [0.059]	Batch query Average: ~1.06 ms / query Sum: ~1.06 ms https://explain.depesz.com/s/tetW All execution times (1) [1.06] Step query Average: ~0.639 ms / query Sum: ~0.639 ms https://explain.depesz.com/s/kTvC All execution times (1) [0.639]	Batch query Average: ~2.673 ms / query Sum: ~32.074 ms https://explain.depesz.com/s/iiXU All execution times (12) [2.946, 2.417, 2.409, 2.378, 2.411, 2.417, 2.369, 2.509, 2.352, 2.347, 2.354, 5.165] Step query Average: ~2.286 ms / query Sum: ~27.435 ms https://explain.depesz.com/s/tHwV All execution times (12) [2.836, 2.323, 2.468, 2.581, 2.428, 2.383, 2.307, 2.263, 2.265, 2.326, 2.278, 0.977]
batched by id/start/end, single query - with index on starts_at, ends_at, and id, - batch size of 10000 Queries (2) Batch query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1584919 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND (ends_at > '2021-10-11 22:46:44.011359' OR ends_at IS NULL)) AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1584919 LIMIT 1 OFFSET 10000	Batch query Average: ~0.112 ms / query Sum: ~0.112 ms https://explain.depesz.com/s/2lq4 All execution times (1) [0.112] Step query Average: ~0.114 ms / query Sum: ~0.114 ms https://explain.depesz.com/s/nB2m All execution times (1) [0.114]	Batch query Average: ~1.204 ms / query Sum: ~1.204 ms https://explain.depesz.com/s/HELd All execution times (1) [1.204] Step query Average: ~1.149 ms / query Sum: ~1.149 ms https://explain.depesz.com/s/2JJQ All execution times (1) [1.149]	Batch query Average: ~3.887 ms / query Sum: ~34.985 ms https://explain.depesz.com/s/uoqU All execution times (9) [3.686, 3.825, 3.443, 3.223, 3.141, 3.557, 3.163, 3.108, 7.839] Step query Average: ~3.756 ms / query Sum: ~33.8 ms https://explain.depesz.com/s/X2wK All execution times (9) [3.596, 3.926, 3.016, 3.056, 3.005, 3.143, 3.568, 2.973, 7.517]
split queries - with index on starts_at, ends_at, and id, - batch size of 10000 Queries (2) Split query (w/ ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at > '2021-10-11 22:46:44.011359') Split query (w/o ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE (starts_at < '2021-10-11 22:46:44.011359' AND ends_at IS NULL)	Split query (w/ ends_at) Average: ~0.032 ms / query Sum: ~0.032 ms https://explain.depesz.com/s/OT3r8 All execution times (1) [0.032] Split query (w/o ends_at) Average: ~0.082 ms / query Sum: ~0.082 ms https://explain.depesz.com/s/IEr2 All execution times (1) [0.082]	Split query (w/ ends_at) Average: ~0.133 ms / query Sum: ~0.133 ms https://explain.depesz.com/s/Yzpc All execution times (1) [0.133] Split query (w/o ends_at) Average: ~0.999 ms / query Sum: ~0.999 ms https://explain.depesz.com/s/8PuP All execution times (1) [0.999]	Split query (w/ ends_at) Average: ~4.394 ms / query Sum: ~4.394 ms https://explain.depesz.com/s/sX37 All execution times (1) [4.394] Split query (w/o ends_at) Average: ~41.627 ms / query Sum: ~41.627 ms https://explain.depesz.com/s/PMJG All execution times (1) [41.627]
batched by id, split queries - with index on starts_at, ends_at, and id, - batch size of 10000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:44.599718' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:44.599718' Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 AND "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:44.599718' AND "incident_management_oncall_rotations"."ends_at" IS NULL Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."id" >= 1571838 AND "incident_management_oncall_rotations"."id" < 1581838 LIMIT 1 OFFSET 10000	Batch query (w/o NULL ends_at) Average: ~0.021 ms / query Sum: ~0.021 ms https://explain.depesz.com/s/UnV8 All execution times (1) [0.021] Batch query (w/ NULL ends_at) Average: ~0.079 ms / query Sum: ~0.079 ms https://explain.depesz.com/s/4Zad All execution times (1) [0.079] Step query Average: ~0.058 ms / query Sum: ~0.058 ms https://explain.depesz.com/s/ZRu8 All execution times (1) [0.058]	Batch query (w/o NULL ends_at) Average: ~0.114 ms / query Sum: ~0.114 ms https://explain.depesz.com/s/2efG All execution times (1) [0.114] Batch query (w/ NULL ends_at) Average: ~1.108 ms / query Sum: ~1.108 ms https://explain.depesz.com/s/ltf5 All execution times (1) [1.108] Step query Average: ~0.567 ms / query Sum: ~0.567 ms https://explain.depesz.com/s/9WN9 All execution times (1) [0.567]	Batch query (w/o NULL ends_at) Average: ~1.703 ms / query Sum: ~20.432 ms https://explain.depesz.com/s/Ng6F All execution times (12) [2.304, 1.846, 1.816, 1.678, 1.486, 1.526, 1.477, 1.55, 1.492, 1.474, 1.497, 2.286] Batch query (w/ NULL ends_at) Average: ~2.664 ms / query Sum: ~31.973 ms https://explain.depesz.com/s/w596 All execution times (12) [2.965, 2.71, 2.304, 2.329, 2.567, 2.331, 2.41, 2.312, 2.293, 2.406, 2.313, 5.033] Step query Average: ~2.928 ms / query Sum: ~35.134 ms https://explain.depesz.com/s/xtAa All execution times (12) [2.762, 2.899, 3.419, 3.252, 3.151, 3.019, 3.224, 3.146, 3.043, 2.955, 3.046, 1.218]
batched by id/start/end, split queries - with index on starts_at, ends_at, and id, - batch size of 10000 Queries (3) Batch query (w/o NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:46.627477' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:46.627477' AND "incident_management_oncall_rotations"."id" >= 1571845 Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:46.627477' AND "incident_management_oncall_rotations"."ends_at" IS NULL AND "incident_management_oncall_rotations"."id" >= 1571840 AND "incident_management_oncall_rotations"."id" < 1585212 Step query SELECT "incident_management_oncall_rotations"."id" FROM "incident_management_oncall_rotations" WHERE "incident_management_oncall_rotations"."starts_at" <= '2021-10-11 22:47:46.627477' AND "incident_management_oncall_rotations"."ends_at" >= '2021-10-11 22:47:46.627477' AND "incident_management_oncall_rotations"."id" >= 1571845 LIMIT 1 OFFSET 10000	Batch query (w/o NULL ends_at) Average: ~0.029 ms / query Sum: ~0.029 ms https://explain.depesz.com/s/ItAn All execution times (1) [0.029] Batch query (w/ NULL ends_at) Average: ~0.091 ms / query Sum: ~0.091 ms https://explain.depesz.com/s/0nkW All execution times (1) [0.091] Step query Average: ~0.057 ms / query Sum: ~0.114 ms https://explain.depesz.com/s/pGG9 All execution times (2) [0.024, 0.09]	Batch query (w/o NULL ends_at) Average: ~0.096 ms / query Sum: ~0.096 ms https://explain.depesz.com/s/lXbl All execution times (1) [0.096] Batch query (w/ NULL ends_at) Average: ~1.04 ms / query Sum: ~1.04 ms https://explain.depesz.com/s/pLP7 All execution times (1) [1.04] Step query Average: ~0.529 ms / query Sum: ~1.058 ms https://explain.depesz.com/s/XAZ3 All execution times (2) [0.093, 0.965]	Batch query (w/o NULL ends_at) Average: ~4.332 ms / query Sum: ~4.332 ms https://explain.depesz.com/s/HFkG All execution times (1) [4.332] Batch query (w/ NULL ends_at) Average: ~3.576 ms / query Sum: ~32.183 ms https://explain.depesz.com/s/xJKp All execution times (9) [3.624, 3.135, 3.615, 3.107, 3.134, 3.199, 3.345, 3.141, 5.883] Step query Average: ~3.576 ms / query Sum: ~35.755 ms https://explain.depesz.com/s/p8Vw All execution times (10) [3.902, 3.475, 3.263, 3.63, 2.975, 3.065, 2.99, 2.959, 2.984, 6.512]

How to set up and validate locally

Run the worker manually

IncidentManagement::OncallRotations::PersistAllRotationsShiftsJob.new.perform

MR 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 Oct 12, 2021 by Sarah Yasonik

Enqueue shift persistence jobs for rotations in batches

What does this MR do?

Still needed: Decision on approach

Database

How to set up and validate locally

MR acceptance checklist

Merge request reports