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/3FA2D9CA-365F-4C12-86C1-25A56C518288%40johnkline.com.
