Use `metric` primary key for `boundary` table

[epompeii]

Create a separate boundary table and use the same foreign key in both boundary and metric. Then make the boundary.id a foreign key. So in this case you'd have:

        CREATE TABLE boundary (
            id INTEGER PRIMARY KEY NOT NULL,
            ...,
            FOREIGN KEY (id) REFERENCES metric (id) ON DELETE CASCADE
        );

https://www.reddit.com/r/rust/s/aXIhhbQYys

[epompeii]

I tried to implement this, and unfortunately, it seems like it does not help with the performance.

This is the query plan:

QUERY PLAN
|--MATERIALIZE (join-5)
|  |--SCAN boundary
|  |--SEARCH threshold USING INTEGER PRIMARY KEY (rowid=?)
|  |--SEARCH model USING INTEGER PRIMARY KEY (rowid=?)
|  `--SEARCH alert USING INDEX index_alert_boundary (boundary_id=?) LEFT-JOIN
|--SEARCH branch USING INDEX sqlite_autoindex_branch_1 (uuid=?)
|--SEARCH testbed USING INDEX sqlite_autoindex_testbed_1 (uuid=?)
|--SEARCH benchmark USING INDEX sqlite_autoindex_benchmark_1 (uuid=?)
|--SEARCH measure USING INDEX sqlite_autoindex_measure_1 (uuid=?)
|--SEARCH report USING INDEX index_report_testbed_end_time (testbed_id=? AND end_time<?)
|--SEARCH version USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH branch_version USING COVERING INDEX sqlite_autoindex_branch_version_1 (branch_id=? AND version_id=?)
|--SEARCH report_benchmark USING INDEX index_report_benchmark (report_id=? AND benchmark_id=?)
|--SEARCH metric USING INDEX sqlite_autoindex_metric_2 (report_benchmark_id=? AND measure_id=?)
|--BLOOM FILTER ON (join-5) (id=?)
|--SEARCH (join-5) USING AUTOMATIC COVERING INDEX (id=?) LEFT-JOIN
`--USE TEMP B-TREE FOR ORDER BY

The VIEW would therefore still be required to get rid of the SCAN boundary. It does save a column, by having the metric table id be the primary key for boundary. Though the UNIQUE constraint on the original model still guarantees the required semantics.

[wmanley]

What's the query?

[epompeii]

What's the query?

@wmanley sorry just saw your question. The query was the same one from the post: https://bencher.dev/learn/engineering/sqlite-performance-tuning/#from-orm-to-sql-query