Query performance benchmarks via snapshot testing

[cameronmcefee]

Disclaimer: This involves AI generated code. If you would like to discuss the merits of such things, please do so elsewhere.

In the recent weeks I'd become frustrated with frequent regressions to queries that had once been efficient. The causes (to me, an experienced developer but SQLite beginner) were non-obvious both because I'm not super familiar with the performance characteristics of SQLite and also because I was working with Claude to do the development in an area beyond my familiarity. I asked Claude if we could develop a benchmarking system so we'd be able to see when queries got noticeably slower. It noodled on that a bit, but the lack of determinism in query performance eventually steered us away.

However Claude suggested something I didn't know was a thing — we could use a combination of SQL EXPLAIN and query plan to evaluate the table scans, sort operations, auto index creations, and vm steps to extract some more deterministic values that we could snapshot. In addition, I asked Claude to also build a tool that would verify which indexes were being used by queries so I'd know if we ever make a change that causes an index to stop being used. We wired up all of our queries to be tested, dropped in some expectations and ran the tests.

Mind you, I'm new to the details here, but it immediately yielded some interesting results. I discovered that we'd created a bunch of indexes that were never used, and some I thought were being used were not, in favor of others — I learned that SQL only uses one index for a query so you need to have properly tailored, non-redundant indexes. I also learned about what specifically the indexes help to avoid — the table scans and sort operations which become slow as they build up.

I also discovered a few seemingly simple queries were requiring thousands of vm steps. What's a vm step? I have no idea (hyperbolic)! But I know that number seemed unnecessarily high, and would increase when I made changes to (what I though was) unrelated tables. By improving my indexes and not querying entire json objects for single values, I got those numbers way down. vm steps seem to fluctuate a bit between runs, but I've found a margin of error of 50-100 is usually enough to keep tests consistent while still signaling regressions.

While I can't say the system is robust (I wouldn't know) or battle tested (I've only been using it for a couple of weeks), I can say I've learned a few things about what influences query performance and I've found some (mostly) stable metrics that have already alerted me when I or the robot has made a change that negatively impacts performance.

If there's one thing I've taken away, and what I'm hoping to raise in this discussion, it's that this feels like something that, in the hands of an experienced practitioner — such as the Point Free team perhaps — could be a really useful testing asset if wrapped up in a nice package. For posterity, here's some of the current code Claude put together. Don't ask me to explain it (I get the gist of it but couldn't explain the details, plus Claude added some 🙄 metrics). It's probably enough you could hand to Claude and have it figure out what to do with it.

  @Test("MasonryGalleryItemsQuery - folder")
  func masonryFolder() async throws {
    let ids = try await setupTestData(in: database)

    try await database.read { db in
      let query = MasonryGalleryItemsQuery(galleryType: .folder(ids.folderID), limit: 100).makeQuery()
      try assertInlineSnapshot(of: getQueryIndexes(query, in: db), as: .description) {
        """
        feedFolders: idx_feedFolders_folderID_feedID
        feeds: sqlite_autoindex_Feeds_1
        mediaItems: idx_mediaItems_postID_mediaSource_postOrder
        posts: idx_posts_feedID_date_id
        """
      }
      try assertInlineSnapshot(of: getQueryStatistics(query, in: db), as: .description) {
        """
        QueryStatistics:
          fullScanSteps: 0
          sortOperations: 1
          autoIndexCreations: 0
          vmSteps: ~2000
        """
      }
    }
  }

[mbrandonw]

Hey @cameronmcefee, this is an excellent exploration, thanks for sharing!

One area of improvement I see is that the assertInlineSnapshot tool can be used to build custom inline snapshot helpers. You can make your own assertIndexes (or whatever you want to call it) that expands multiple trailing closures for the snapshots:

assertIndexes(query) {
  """
  feedFolders: idx_feedFolders_folderID_feedID
  feeds: sqlite_autoindex_Feeds_1
  mediaItems: idx_mediaItems_postID_mediaSource_postOrder
  posts: idx_posts_feedID_date_id
  """
} statistics: {
  """
  fullScanSteps: 0
  sortOperations: 1
  autoIndexCreations: 0
  vmSteps: ~2000
  """
}

You can look at how we define assertQuery in the library for more tips on how to do this.

It's also got me wondering whether these kinds of tools could be built and configured more deeply into SQLiteData. Certainly something worth thinking about more!

[cameronmcefee]

Ah nice, I'll take a look at that! I do feel like this has a lot of room for ergonomic improvement, and I think built-in support would be a pretty big boon for your testability story.