oxidecomputer / oxidecomputer/omicron
Should we add explicit index hints to queries?
Open
Nobody has claimed this yet.
- Dominant language
- Rust
- Stars
- 572
- Forks
- 97
- Avg merge
- 2d 12h
- Merged PRs (30d)
- 96
Description
On #5299, we had a query that was using an inordinate amount of memory because it was FULL SCANing the pkey index of a table that was erroneously very large. #5301 fixes the "erroneously very large", but it's still surprising that the query in question was doing a FULL SCAN; when running omicron tests locally, the query required the creation of the inv_zpool_by_id_and_time index to pass our "no full scans allowed" check.
When running a portion of the query by hand under EXPLAIN ANALYZE, we can see that even once the table has been pruned down to its expected size of a few hundred rows, we're still full scanning inv_zpool@inv_zpool_pkey:
root@[fd00:1122:3344:109::3]:32221/omicron> explain analyze WITH "old_zpool_usage" AS (SELECT "dataset"."pool_id", sum("dataset"."size_used") AS size_used FROM "dataset" WHERE (("dataset"."size_used" IS NOT NULL) AND ("dataset"."time_deleted" IS NULL)) GROUP BY "dataset"."pool_id"), "candidate_zpools" AS (SELECT DISTINCT ON ("zpool"."sled_id")"old_zpool_usage"."pool_id" FROM ("old_zpool_usage" INNER JOIN ("zpool" INNER JOIN "sled" ON ("zpool"."sled_id" = "sled"."id")) ON ("zpool"."id" = "old_zpool_usage"."pool_id")) WHERE (((("old_zpool_usage"."size_used" + 10737418240) <= (SELECT total_size FROM omicron.public.inv_zpool WHERE inv_zpool.id = old_zpool_usage.pool_id ORDER BY inv_zpool.time_collected DESC LIMIT 1)) AND ("sled"."sled_policy" = 'in_service')) AND ("sled"."sled_state" = 'active')) ORDER BY "zpool"."sled_id", md5((CAST("zpool"."id" as BYTEA)))) select * from candidate_zpools;
info
-------------------------------------------------------------------------------------------------------------------------------------
planning time: 4ms
execution time: 264ms
distribution: full
vectorized: true
rows read from KV: 1,716 (177 KiB)
cumulative time spent in KV: 265ms
maximum memory usage: 890 KiB
network usage: 45 KiB (36 messages)
• render
│ nodes: n1
│ actual row count: 11
│ estimated row count: 11
│
└── • distinct
│ nodes: n1, n4
│ actual row count: 11
│ estimated max memory allocated: 20 KiB
│ estimated max sql temp disk usage: 0 B
│ distinct on: sled_id
│
└── • sort
│ nodes: n1, n4
│ actual row count: 110
│ estimated max memory allocated: 40 KiB
│ estimated max sql temp disk usage: 0 B
│ estimated row count: 52
│ order: +column53
│
└── • render
│ nodes: n1, n4
│ actual row count: 110
│ estimated max memory allocated: 130 KiB
│ estimated max sql temp disk usage: 0 B
│ estimated row count: 52
│
└── • hash join
│ nodes: n1, n4
│ actual row count: 110
│ estimated max memory allocated: 130 KiB
│ estimated max sql temp disk usage: 0 B
│ estimated row count: 52
│ equality: (sled_id) = (id)
│ right cols are key
│
├── • hash join
│ │ nodes: n1, n4
│ │ actual row count: 110
│ │ estimated max memory allocated: 120 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ estimated row count: 34
│ │ equality: (id) = (pool_id)
│ │ left cols are key
│ │ right cols are key
│ │
│ ├── • scan
│ │ nodes: n1
│ │ actual row count: 132
│ │ KV time: 8ms
│ │ KV contention time: 0µs
│ │ KV rows read: 132
│ │ KV bytes read: 13 KiB
│ │ estimated max memory allocated: 30 KiB
│ │ estimated row count: 132 (100% of the table; stats collected 5 days ago)
│ │ table: zpool@zpool_pkey
│ │ spans: FULL SCAN
│ │
│ └── • filter
│ │ nodes: n1, n4
│ │ actual row count: 110
│ │ estimated row count: 34
│ │ filter: total_size >= (size_used + 10737418240)
│ │
│ └── • distinct
│ │ nodes: n1, n4
│ │ actual row count: 110
│ │ estimated max memory allocated: 20 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ distinct on: pool_id
│ │
│ └── • sort
│ │ nodes: n1, n4
│ │ actual row count: 1,210
│ │ estimated max memory allocated: 400 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ estimated row count: 1,123
│ │ order: -time_collected
│ │
│ └── • hash join (right outer)
│ │ nodes: n1, n4
│ │ actual row count: 1,210
│ │ estimated max memory allocated: 230 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ estimated row count: 1,123
│ │ equality: (id) = (pool_id)
│ │ right cols are key
│ │
│ ├── • scan
│ │ nodes: n1
│ │ actual row count: 1,452
│ │ KV time: 254ms
│ │ KV contention time: 0µs
│ │ KV rows read: 1,452
│ │ KV bytes read: 147 KiB
│ │ estimated max memory allocated: 230 KiB
│ │ estimated row count: 1,452 (100% of the table; stats collected 6 seconds ago)
│ │ table: inv_zpool@inv_zpool_pkey
│ │ spans: FULL SCAN
│ │
│ └── • render
│ │ nodes: n4
│ │ actual row count: 110
│ │ estimated max memory allocated: 140 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ estimated row count: 102
│ │
│ └── • group (hash)
│ │ nodes: n4
│ │ actual row count: 110
│ │ estimated max memory allocated: 140 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ estimated row count: 102
│ │ group by: pool_id
│ │
│ └── • filter
│ │ nodes: n4
│ │ actual row count: 110
│ │ estimated row count: 110
│ │ filter: (size_used IS NOT NULL) AND (time_deleted IS NULL)
│ │
│ └── • scan
│ nodes: n4
│ actual row count: 121
│ KV time: 1ms
│ KV contention time: 0µs
│ KV rows read: 121
│ KV bytes read: 14 KiB
│ estimated max memory allocated: 30 KiB
│ estimated row count: 121 (100% of the table; stats collected 5 minutes ago)
│ table: dataset@dataset_pkey
│ spans: FULL SCAN
│
└── • filter
│ nodes: n1
│ actual row count: 11
│ estimated row count: 11
│ filter: (sled_policy = 'in_service') AND (sled_state = 'active')
│
└── • scan
nodes: n1
actual row count: 11
KV time: 1ms
KV contention time: 0µs
KV rows read: 11
KV bytes read: 2.0 KiB
estimated max memory allocated: 20 KiB
estimated row count: 11 (100% of the table; stats collected 14 hours ago)
table: sled@sled_pkey
spans: FULL SCAN
(159 rows)
Time: 271ms total (execution 269ms / network 2ms)
@smklein pointed out that CRDB supports index hints. If we modify the above query, changing FROM omicron.public.inv_zpool to FROM omicron.public.inv_zpool@{NO_FULL_SCAN}, the full scan of inv_zpool is gone, and the max memory used by the query is reduced significantly:
root@[fd00:1122:3344:109::3]:32221/omicron> explain analyze WITH "old_zpool_usage" AS (SELECT "dataset"."pool_id", sum("dataset"."size_used") AS size_used FROM "dataset" WHERE (("dataset"."size_used" IS NOT NULL) AND ("dataset"."time_deleted" IS NULL)) GROUP BY "dataset"."pool_id"), "candidate_zpools" AS (SELECT DISTINCT ON ("zpool"."sled_id")"old_zpool_usage"."pool_id" FROM ("old_zpool_usage" INNER JOIN ("zpool" INNER JOIN "sled" ON ("zpool"."sled_id" = "sled"."id")) ON ("zpool"."id" = "old_zpool_usage"."pool_id")) WHERE (((("old_zpool_usage"."size_used" + 10737418240) <= (SELECT total_size FROM omicron.public.inv_zpool@{NO_FULL_SCAN} WHERE inv_zpool.id = old_zpool_usage.pool_id ORDER BY inv_zpool.time_collected DESC LIMIT 1)) AND ("sled"."sled_policy" = 'in_service')) AND ("sled"."sled_state" = 'active')) ORDER BY "zpool"."sled_id", md5((CAST("zpool"."id" as BYTEA)))) select * from candidate_zpools;
info
------------------------------------------------------------------------------------------------------------------------------------------
planning time: 4ms
execution time: 381ms
distribution: full
vectorized: true
rows read from KV: 2,684 (245 KiB)
cumulative time spent in KV: 364ms
maximum memory usage: 360 KiB
network usage: 12 KiB (24 messages)
• render
│ nodes: n1
│ actual row count: 11
│ estimated row count: 11
│
└── • distinct
│ nodes: n1, n4
│ actual row count: 11
│ estimated max memory allocated: 20 KiB
│ estimated max sql temp disk usage: 0 B
│ distinct on: sled_id
│
└── • sort
│ nodes: n1, n4
│ actual row count: 110
│ estimated max memory allocated: 50 KiB
│ estimated max sql temp disk usage: 0 B
│ estimated row count: 52
│ order: +column53
│
└── • render
│ nodes: n1, n4
│ actual row count: 110
│ estimated max memory allocated: 130 KiB
│ estimated max sql temp disk usage: 0 B
│ estimated row count: 52
│
└── • hash join
│ nodes: n1, n4
│ actual row count: 110
│ estimated max memory allocated: 130 KiB
│ estimated max sql temp disk usage: 0 B
│ estimated row count: 52
│ equality: (sled_id) = (id)
│ right cols are key
│
├── • hash join
│ │ nodes: n1, n4
│ │ actual row count: 110
│ │ estimated max memory allocated: 120 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ estimated row count: 34
│ │ equality: (id) = (pool_id)
│ │ left cols are key
│ │ right cols are key
│ │
│ ├── • scan
│ │ nodes: n1
│ │ actual row count: 132
│ │ KV time: 2ms
│ │ KV contention time: 0µs
│ │ KV rows read: 132
│ │ KV bytes read: 13 KiB
│ │ estimated max memory allocated: 30 KiB
│ │ estimated row count: 132 (100% of the table; stats collected 5 days ago)
│ │ table: zpool@zpool_pkey
│ │ spans: FULL SCAN
│ │
│ └── • filter
│ │ nodes: n4
│ │ actual row count: 110
│ │ estimated row count: 34
│ │ filter: total_size >= (size_used + 10737418240)
│ │
│ └── • distinct
│ │ nodes: n4
│ │ actual row count: 110
│ │ estimated max memory allocated: 10 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ distinct on: pool_id
│ │
│ └── • sort
│ │ nodes: n4
│ │ actual row count: 1,210
│ │ estimated max memory allocated: 380 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ estimated row count: 1,123
│ │ order: -time_collected
│ │
│ └── • lookup join (left outer)
│ │ nodes: n4
│ │ actual row count: 1,210
│ │ KV time: 159ms
│ │ KV contention time: 0µs
│ │ KV rows read: 1,210
│ │ KV bytes read: 102 KiB
│ │ estimated max memory allocated: 760 KiB
│ │ table: inv_zpool@inv_zpool_pkey
│ │ equality: (inv_collection_id, sled_id, id) = (inv_collection_id,sled_id,id)
│ │ equality cols are key
│ │
│ └── • lookup join (left outer)
│ │ nodes: n4
│ │ actual row count: 1,210
│ │ KV time: 199ms
│ │ KV contention time: 0µs
│ │ KV rows read: 1,210
│ │ KV bytes read: 114 KiB
│ │ estimated max memory allocated: 60 KiB
│ │ estimated row count: 1,123
│ │ table: inv_zpool@inv_zpool_by_id_and_time
│ │ equality: (pool_id) = (id)
│ │
│ └── • render
│ │ nodes: n4
│ │ actual row count: 110
│ │ estimated max memory allocated: 140 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ estimated row count: 102
│ │
│ └── • group (hash)
│ │ nodes: n4
│ │ actual row count: 110
│ │ estimated max memory allocated: 140 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ estimated row count: 102
│ │ group by: pool_id
│ │
│ └── • filter
│ │ nodes: n4
│ │ actual row count: 110
│ │ estimated row count: 110
│ │ filter: (size_used IS NOT NULL) AND (time_deleted IS NULL)
│ │
│ └── • scan
│ nodes: n4
│ actual row count: 121
│ KV time: 3ms
│ KV contention time: 0µs
│ KV rows read: 121
│ KV bytes read: 14 KiB
│ estimated max memory allocated: 30 KiB
│ estimated row count: 121 (100% of the table; stats collected 15 seconds ago)
│ table: dataset@dataset_pkey
│ spans: FULL SCAN
│
└── • filter
│ nodes: n1
│ actual row count: 11
│ estimated row count: 11
│ filter: (sled_policy = 'in_service') AND (sled_state = 'active')
│
└── • scan
nodes: n1
actual row count: 11
KV time: 1ms
KV contention time: 0µs
KV rows read: 11
KV bytes read: 2.0 KiB
estimated max memory allocated: 20 KiB
estimated row count: 11 (100% of the table; stats collected 14 hours ago)
table: sled@sled_pkey
spans: FULL SCAN
(162 rows)
Time: 388ms total (execution 386ms / network 2ms)
There are at least a couple possible issues here:
- How can we inject these index hints? For this particular query, we're using a raw string, so it's actually trivial, but I'm not sure what we'd do if we needed to attach this to diesel queries.
- How can we know which tables of which queries ought to have index hints? When we run this query in the
nexus-db-queriesunit tests, not having the index trips over the "no full scans allowed" check. If we run the query (with the index present but noNO_FULL_SCANhint) against an empty database, CRDB uses the index instead of a full scan even though the hint isn't present. Maybe the necessity of the hint depends on the size of the table?
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
No source file or test is named. Start with the EXPLAIN ANALYZE query in the issue and compare the plans with and without the @{NO_FULL_SCAN} hint, using the referenced inv_zpool_by_id_and_time index as context. Done means reaching and implementing a clear decision about whether explicit hints should be added.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- databases, performance
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100