Query Risk Assessment for Generated SQL

Overview

OpenSpecimen generates SQL dynamically from AQL queries. Majority of the generated queries execute successfully. However, a small percentage of queries can produce expensive database execution plans. Such queries consume excessive database resources, eventually timing out or affecting overall system performance.

To reduce this risk, OpenSpecimen now supports an optional Query Risk Assessment layer. This layer evaluates the generated SQL before execution using the database explain plan. If the query is considered safe, it is allowed to execute. If the query is risky, it is rejected before it reaches the database execution phase.

This feature is intended to protect the database from slow-running or resource-intensive queries and provide administrators with enough diagnostic information to tune forms, indexes, AQLs, or risk thresholds.

JIRA: OPSMN-7313

High-Level Flow

Expanded Flow

Risk Assessor Internals

Its responsibility is to:

Take the SQL generated from AQL.
Run MySQL EXPLAIN FORMAT=JSON.
Parse the explain plan into an internal structure.
Apply configurable risk rules.
Return one of:
- OK: query can run.
- REJECT: query is considered risky and should not run.
- Observe only: risk is logged, but query is allowed.

What Happens When a Query Is Rejected?

When a query is rejected, OpenSpecimen notifies the system administrators with the following details:

Original AQL
Generated SQL
Raw explain plan
Rejection reason
Structured risk metrics

Risk Assessment Configuration

Risk assessment is controlled through user-supplied/default configuration. A full default configuration looks like:

{
  "enabled": true,
  "observeOnly": false,
  "largeTableRows": 1000000,
  "maxRowsExaminedPerScan": 1000000,
  "maxRowsProducedPerJoin": 5000000,
  "maxTotalRowsExamined": 10000000,
  "maxSortRows": 1000000,
  "maxTempTableRows": 1000000,
  "maxFullScanRowsForSortOrTemp": 100000,
  "maxDependentSubqueryRows": 100000,
  "maxJoinTables": 30,
  "maxQueryCost": -1.0,
  "maxEstimatedJoinWork": -1.0,
  "maxJoinBufferRows": 100000,
  "minFilteredPercentForFullScan": 10.0
}

A value of 0 or -1 disables the corresponding rule.

Risk Rules

Rule	Level	Config Key(s)	Default	Rejects When	Rationale

Rule	Level	Config Key(s)	Default	Rejects When	Rationale
Total rows examined	Query	`maxTotalRowsExamined`	`10000000`	The query is estimated to examine more than `maxTotalRowsExamined` rows across all table accesses.	Catches queries whose total scan effort is too high across the whole plan.
MySQL query cost	Query	`maxQueryCost`	`-1`	MySQL’s estimated query cost is greater than `maxQueryCost`.	Uses MySQL’s global cost estimate. Disabled by default because cost needs production calibration.
Estimated join work	Query / join	`maxEstimatedJoinWork`	`-1`	The estimated repeated join work is greater than `maxEstimatedJoinWork`.	Catches join fan-out where repeated scan work is high.
Max rows produced by join	Query / join	`maxRowsProducedPerJoin`	`5000000`	A join step is estimated to produce more than `maxRowsProducedPerJoin` intermediate rows.	Prevents large intermediate join results.
Too many joined tables	Query	`maxJoinTables`	`30`	The query joins more than `maxJoinTables` tables.	Guards against broad AQLs that create complex join plans.
Rows examined per table scan	Table	`maxRowsExaminedPerScan`	`1000000`	Any single table access is estimated to examine more than `maxRowsExaminedPerScan` rows.	Catches one table access that is too expensive by itself.
Full scan on large low-selectivity table	Table	`largeTableRows`, `minFilteredPercentForFullScan`	`1000000`, `10.0`	A table is fully scanned, examines more than `largeTableRows` rows, and MySQL estimates at least `minFilteredPercentForFullScan` percent of rows may remain after filtering.	Catches large full scans where meaningful data survives filtering.
Missing usable key	Table	`largeTableRows`	`1000000`	A table has candidate indexes, but MySQL chooses no index and estimates scanning more than `largeTableRows` rows.	Flags possible index mismatch, stale stats, or low-selectivity predicates.
Join buffer / hash join over large rows	Table / join	`maxJoinBufferRows`	`100000`	A table join uses join buffer or hash join and is estimated to produce more than `maxJoinBufferRows` intermediate rows.	Useful for detecting unindexed joins.
Large full scan feeding sort/temp table	Hybrid	`maxFullScanRowsForSortOrTemp`	`100000`	The query uses filesort or a temporary table, and at least one table is fully scanned for more than `maxFullScanRowsForSortOrTemp` rows.	Catches grouped or ordered aggregate queries that full-scan many rows before temp-table or filesort work.
Filesort over large result	Query	`maxSortRows`	`1000000`	The query uses filesort and is estimated to process more than `maxSortRows` rows during the plan.	Prevents expensive sort operations.
Temporary table over large result	Query	`maxTempTableRows`	`1000000`	The query uses a temporary table and is estimated to process more than `maxTempTableRows` rows during the plan.	Guards against memory or disk pressure from large temporary tables.
Dependent subquery repeated work	Query / subquery	`maxDependentSubqueryRows`	`100000`	A dependent subquery is estimated to perform repeated work greater than `maxDependentSubqueryRows`.	Accounts for repeated execution cost, roughly outer rows multiplied by subquery work.