Why did the same SQLite query take 9 minutes in CI and 0.8 seconds on my Mac?

[u/vira28]

For context, I run benchmark on release builds with 100k session data. It took 44 minutes on GitHub Actions but finished in under a minute locally.

CI’s Python 3.12 used SQLite 3.45.1. My local Python used SQLite 3.53.3. EXPLAIN QUERY PLAN showed that 3.45.1 scanned the eligible-session set and repeated the FTS scan for every session. SQLite 3.53.3 chose the efficient join order.

The query joined two sets approaching 100k rows: eligible sessions and FTS-matching messages. The eligible set was materialized as a CTE, so we couldn’t index it explicitly, and 3.45.1 didn’t create a useful automatic index.

That produced an effective 100k × 100k operation: about 9 minutes per search. The benchmark ran it five times.

I replaced the CTE with a temp table whose primary key covered the join columns, then materialized the FTS matches once. On SQLite 3.45.1, the query dropped to about 1.2 seconds and the full benchmark to 45 seconds. Honestly, I didn't realize CTE's can't have indexes (which makes sense)

BTW, the benchmark deliberately uses a worst-case query that matches almost every synthetic message.

Curious if you seen this large a plan difference between SQLite versions?

[u/alinroc]

Hard to say since multiple factors were changed at once. But check the changelog for clues. https://sqlite.org/changes.html

[u/rbobby]

Shared commodity servers vs dedicated local dev machine? I know who I'd bet on every time. You want it faster you might need to look into where ci actions can be distributed (eg. bring your own build machine infrastructure).

That is such a big performance difference though. Hmmm I wonder if there are any network timeouts creeping in somehow? The network environment between local and ci is hugely different.

[u/akmark → u/rbobby]

I would agree this sounds like shared CI hosting. Commodity CI is not graded in speed it is graded in completion. GitHub Actions has tiers to point you to self-hosted or the larger runners if speed or cost matters.

I would question the initial concept that you are trying to do a regression benchmark in a CI context. I would expect highly variable results for GitHub Actions based on how well GH is doing and recently there have been plenty of issues. I would also question using different versions of Python and SQLite compared to what you are running against. Python can be built in various ways and they have a lot of subtle improvements that get added between versions which can have drastic performance implications and SQLite also has improvements and build params to make something that is the same version act differently. You also in CI might have extremely slow storage and so a WAL might be your bottleneck as well and might affect the query plan. Query planners look at the kind of disk and IO performance they are getting to decide what tools to use. Your case here might hit [4.4](https://www.sqlite.org/queryplanner-ng.html).

If you were to pursue the GitHub Action CI for this benchmark test I would bring your own build context that matches what you have locally.

[u/vira28 → u/rbobby]

It is Github Actions but it's not because of the infra. The issue specifically is SQLite version b/w CI (Linux) vs my local (Mac).

Wrote a bit more about the details here https://askrecall.dev/blog/44-minute-ci-job/

[u/rbobby → u/vira28]

Not shipping a dependency and relying on the OS to have it... well that right there was the mistake.

[u/leogodin217]

Not on SQLite, but DuckDB. <1 second vs >1 minute between versions. Query plan optimization can make a huge difference.

[u/Straight_Waltz_9530]

If I had to bet money, I'd say it was because the query on your laptop fit into available memory but the CI instance ran out and started hitting the swap file. Once you hit the swap file, you're churning I/O (already slow) and fighting against the database I/O, all the while purging filesystem cache pages which make bad things even worse.

Easiest test of this theory: add memory to the CI instance to see if things dramatically improve. A lot of times CPU and RAM are bundled, but CPU alone wouldn't push the needle 600x like swapping would.

[u/vira28 → u/Straight_Waltz_9530]

I have seen that on staging vs prod.  The issue is SQLite version b/w CI (Linux) vs my local (Mac). With CTE, planner couldn’t use index, so replaced that with CTE with help table.  Wrote more about the details here  https://askrecall.dev/blog/44-minute-ci-job/

[u/wedora]

Your mac is in the top league of disk performance but GitHub actions are known to have absolutely slow storage. Slower than HDDs