Enqueue shift persistence jobs for rotations in batches
What does this MR do?
Related issue: #322157 (closed)
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)'))
- The
- 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.
- RSpec script to seed & print out explain plans/aggregate execution times
- Output of query analysis script
Table of query options & performance details
Production-like data | 10x Growth | 500x Growth | |
---|---|---|---|
Data Composition | 234 total rotations
|
2461 total rotations
|
114352 total rotations
|
single query (ORIGINAL) - without index, - batch size of 1000 Queries (1)Single querySELECT "incident_management_oncall_rotations"."id" |
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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.067 ms / query Sum: ~0.067 ms https://explain.depesz.com/s/29ob All execution times (1)[0.067] 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] 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] 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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.084 ms / query Sum: ~0.084 ms https://explain.depesz.com/s/3vwv All execution times (1)[0.084] 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] 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] 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" Split query (w/o ends_at)** SELECT "incident_management_oncall_rotations"."id" |
**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] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.083 ms / query Sum: ~0.083 ms https://explain.depesz.com/s/ykUT All execution times (1)[0.083] 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] 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] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.066 ms / query Sum: ~0.066 ms https://explain.depesz.com/s/nZD2r All execution times (1)[0.066] 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] Average: ~0.44 ms / query Sum: ~0.879 ms https://explain.depesz.com/s/qbvH All execution times (2)[0.445, 0.434] 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] 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] 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 querySELECT "incident_management_oncall_rotations"."id" |
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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.09 ms / query Sum: ~0.09 ms https://explain.depesz.com/s/urjFw All execution times (1)[0.09] 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] 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] 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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.097 ms / query Sum: ~0.097 ms https://explain.depesz.com/s/tr25 All execution times (1)[0.097] 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] 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] 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" Split query (w/o ends_at)** SELECT "incident_management_oncall_rotations"."id" |
**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] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.073 ms / query Sum: ~0.073 ms https://explain.depesz.com/s/CSaF All execution times (1)[0.073] 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] 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] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.085 ms / query Sum: ~0.085 ms https://explain.depesz.com/s/V488 All execution times (1)[0.085] 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] Average: ~0.37 ms / query Sum: ~0.74 ms https://explain.depesz.com/s/3Gws All execution times (2)[0.433, 0.307] 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] 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] 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 querySELECT "incident_management_oncall_rotations"."id" |
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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.096 ms / query Sum: ~0.096 ms https://explain.depesz.com/s/brZL All execution times (1)[0.096] 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] 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] 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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.106 ms / query Sum: ~0.106 ms https://explain.depesz.com/s/Eu1S All execution times (1)[0.106] 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] 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] 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" Split query (w/o ends_at)** SELECT "incident_management_oncall_rotations"."id" |
**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] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.076 ms / query Sum: ~0.076 ms https://explain.depesz.com/s/EoXa All execution times (1)[0.076] 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] 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] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.115 ms / query Sum: ~0.115 ms https://explain.depesz.com/s/anTv All execution times (1)[0.115] 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] Average: ~0.471 ms / query Sum: ~0.942 ms https://explain.depesz.com/s/2szX All execution times (2)[0.443, 0.499] 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] 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] 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 querySELECT "incident_management_oncall_rotations"."id" |
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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.062 ms / query Sum: ~0.062 ms https://explain.depesz.com/s/7qPyM All execution times (1)[0.062] 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] 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] 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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.064 ms / query Sum: ~0.064 ms https://explain.depesz.com/s/Panr All execution times (1)[0.064] 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] 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] 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" Split query (w/o ends_at)** SELECT "incident_management_oncall_rotations"."id" |
**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] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.08 ms / query Sum: ~0.08 ms https://explain.depesz.com/s/tBZD All execution times (1)[0.08] 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] Average: ~0.639 ms / query Sum: ~0.639 ms https://explain.depesz.com/s/yh6Z All execution times (1)[0.639] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.071 ms / query Sum: ~0.071 ms https://explain.depesz.com/s/7xBb All execution times (1)[0.071] 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] Average: ~0.633 ms / query Sum: ~0.633 ms https://explain.depesz.com/s/HI7I All execution times (1)[0.633] 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] 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] 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 querySELECT "incident_management_oncall_rotations"."id" |
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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.082 ms / query Sum: ~0.082 ms https://explain.depesz.com/s/3YsA All execution times (1)[0.082] 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] 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] 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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.113 ms / query Sum: ~0.113 ms https://explain.depesz.com/s/lFD4 All execution times (1)[0.113] 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] 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] 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" Split query (w/o ends_at)** SELECT "incident_management_oncall_rotations"."id" |
**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] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.171 ms / query Sum: ~0.171 ms https://explain.depesz.com/s/89dH All execution times (1)[0.171] 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] Average: ~0.754 ms / query Sum: ~0.754 ms https://explain.depesz.com/s/HZUp All execution times (1)[0.754] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.082 ms / query Sum: ~0.082 ms https://explain.depesz.com/s/fX1P All execution times (1)[0.082] 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] Average: ~0.731 ms / query Sum: ~0.731 ms https://explain.depesz.com/s/8iV3 All execution times (1)[0.731] 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] 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] 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 querySELECT "incident_management_oncall_rotations"."id" |
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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.117 ms / query Sum: ~0.117 ms https://explain.depesz.com/s/8Evs All execution times (1)[0.117] 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] 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] 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 querySELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
Batch query Average: ~0.112 ms / query Sum: ~0.112 ms https://explain.depesz.com/s/2lq4 All execution times (1)[0.112] 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] 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] 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" Split query (w/o ends_at)** SELECT "incident_management_oncall_rotations"."id" |
**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] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.079 ms / query Sum: ~0.079 ms https://explain.depesz.com/s/4Zad All execution times (1)[0.079] 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] Average: ~1.108 ms / query Sum: ~1.108 ms https://explain.depesz.com/s/ltf5 All execution times (1)[1.108] 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] 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] 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" Batch query (w/ NULL ends_at) SELECT "incident_management_oncall_rotations"."id" Step query SELECT "incident_management_oncall_rotations"."id" |
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] Average: ~0.091 ms / query Sum: ~0.091 ms https://explain.depesz.com/s/0nkW All execution times (1)[0.091] 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] Average: ~1.04 ms / query Sum: ~1.04 ms https://explain.depesz.com/s/pLP7 All execution times (1)[1.04] 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] 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] 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.