3 ms·
The culprit for the slowdown is this section, reformatted to be readable: LEFT OUTER JOIN ( boundary INNER JOIN threshold ON (threshold.id = b
by magicalhippo 3y ago
The culprit for the slowdown is this section, reformatted to be readable:
LEFT OUTER JOIN (
boundary
INNER JOIN threshold ON (threshold.id = boundary.threshold_id)
INNER JOIN model ON (model.id = boundary.model_id)
LEFT OUTER JOIN alert ON (alert.boundary_id = boundary.id)
) ON (boundary.metric_id = metric.id)
From what I can gather, it does a full scan on "boundary" table so it can do the inner joins before doing the left outer join against "metric".
The author then introduces the "metric_boundary" view which does the left outer join between "metric" and "boundary". However the SQL using the "metric_boundary" view is not shown, and from the query planner results I can't see that the new SQL is equivalent?
That is, without the "metric_boundary" view, one immediate thought on how to optimize the original query is to avoid doing the "threshold" and "model" inner joins, instead flattening the query:
LEFT OUTER JOIN boundary ON (boundary.metric_id = metric.id)
LEFT OUTER JOIN threshold ON (threshold.id = boundary.threshold_id)
LEFT OUTER JOIN model ON (model.id = boundary.model_id)
LEFT OUTER JOIN alert ON (alert.boundary_id = boundary.id)
This should allow the database to just search on the primary keys, similar to the final result in the article. However, this is not equivalent. It will include values from "boundary" even if there are no matches for "threshold" and "model", unlike the original query. In the original, "boundary" rows will not be returned unless there's also linked rows in "threshold" and "model".
However, from the query planner results after introducing the "metric_boundary" view, from what I can see the author has in effect done just the same kind of flattening?
|--SEARCH boundary USING INDEX sqlite_autoindex_boundary_2 (metric_id=?) LEFT-JOIN
|--SEARCH threshold USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
|--SEARCH model USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
Am I missing something here? Did the author not really need to reject "boundary" table rows which didn't have any corresponding rows in "threshold" and "model" table, ie didn't really need the inner joins, and didn't realize this?