I found what actually made beta4 faster — it isn’t the aqi_source gate.
beta4 caches the search list across generated files. get_extension_list() now builds everything once on the first file of a report run, saves the result, and hands every subsequent file a copy, refreshing only the per-template pieces (the timespan binder and the NOAA file index). The comment in init says it plainly: one search-list extension instance per report run. In beta3 the full build ran for every one of the 14 generated files — 28 archive scans per cycle. In beta4 it runs once — 2 scans per cycle. That’s the speedup, and it’s the right change on its own merits: rebuilding forecasts and config for every file was waste regardless. But the scan itself is unchanged: still unbounded, still a full pass over the table when the pm2_5 column is empty. On slow hardware that’s one ~20-second query per cycle holding a read lock against the archive writer — a smaller window than beta3’s, but the same mechanism. It should still be bounded: a WHERE dateTime >= ? window of a day or two returns the same answer for any station with current PM data and costs almost nothing no matter how large the database is. I think this lays this thread to rest. If the OP gets back to me with the sql timing (OP, please run it twice), we can’t look at those numbers; but I don’t think there are any other open items. (And I want to get back to weewx-skyfield.) > On Aug 4, 2026, at 6:00 PM, John Kline <[email protected]> wrote: > > The warm figure is the interesting one: 2.64 s wall, with user+sys > accounting for essentially all of it. Once the 242 MB is cached, the cost is > pure CPU — SQLite examining every row in the table. Storage speed stops > mattering; that 2.6 s is the floor on that hardware no matter what the > database sits on. > > That matters for the fix. Bounding the lookback isn’t an I/O optimization — > it caps the worst case. Unbounded, an empty column means a full pass over the > table: 700K rows on your database, which is where your 2.6 s goes; 1.4M on > the OP’s 13 years, and growing every five minutes. Bounded to a couple of > days, the query examines at most a few hundred rows no matter how big the > database gets — and for a station with current PM data it still stops at the > first row, same as your outTemp result. > > For scale: the search list extension runs once per generated file, not once > per report cycle, and beta3 ships 14 template sections. At your warm number > that’s around 37 s of scanning per cycle, and more when weewxd is competing > for the same page cache in 1 GB. > > Your outTemp comparison is the cleanest demonstration in this thread, > incidentally. Same query shape, same table, 30,000× difference — and the only > variable is whether the first row examined has a value in it. > >> On Aug 4, 2026, at 5:37 PM, Greg Troxel <[email protected]> wrote: >> I rebooted the machine and then ran the query 3 times. >> >> $ sqlite3 weewx.sdb >> SQLite version 3.53.2 2026-06-03 19:12:13 >> Enter ".help" for usage hints. >> sqlite> .timer on >> sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL ORDER BY >> dateTime DESC LIMIT 1; >> Run Time: real 20.939687 user 1.811444 sys 1.555754 >> sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL ORDER BY >> dateTime DESC LIMIT 1; >> Run Time: real 2.640085 user 1.682205 sys 0.956678 >> sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL ORDER BY >> dateTime DESC LIMIT 1; >> Run Time: real 2.675249 user 1.669083 sys 1.004916 >> sqlite> >> >> $ ls -lh weewx.sdb >> -rw-r--r-- 1 gdt users 242M Aug 4 20:30 weewx.sdb -- You received this message because you are subscribed to the Google Groups "weewx-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/weewx-user/B54515BC-B58E-4A04-961F-EB8E915183CC%40johnkline.com.
