Detecting Full Table Scans With SQLite
38 points by alexpls
38 points by alexpls
TIL about fullscan_steps.
"Feels like we could integrate this in to Rails and warn or raise in test / development." It would be amazing if you can say something like assert_has_no_table_scans { get "/" } or even a flag that you turn on in a test/set of tests.
Query optimizers chose query plans based on collected table statistics and sometimes placeholder values (I think not the latter in SQLite’s case - I’m not familiar with its optimizer).
So if you want tests to guarantee that full table scans do not occur in production you’ll need to ship the same stats used in tests to the production database and never update the stats. There are some notes on this here: https://sqlite.org/lang_analyze.html
Some databases allow you to disable full table scans or error when they are planned, which is a reliable safe-guard that doesn’t require keeping stats static.
Very cool! I wonder if you can do the same in Postgres.