info subcommand is slow #146

New Issue

2026-10-27T16:57:19+01:00

sneak commented

2026-10-27 16:57:19 +01:00

root@las1stor1:~# time ./go/bin/sfdupes info
info: database /srv/tmp/sfdupes.db (from SFDUPES_DATABASE)
⠙ [1/2] open: opening the database, elapsed 0s, eta unknown
⠏ [2/2] count: counting the records, elapsed 10m9s, eta unknown
database        /srv/tmp/sfdupes.db (from SFDUPES_DATABASE)
database file size      25582678016 (23.8 GiB)
modified        2026-10-23T16:43:59Z
schema version  1
records 28006608
records with head and tail hashes       26929372
records with content hash       26896464
size of recorded files  164007990979900 (149.2 TiB)

real    10m8.880s
user    0m27.511s
sys     9m31.487s
root@las1stor1:~#

``` root@las1stor1:~# time ./go/bin/sfdupes info info: database /srv/tmp/sfdupes.db (from SFDUPES_DATABASE) ⠙ [1/2] open: opening the database, elapsed 0s, eta unknown ⠏ [2/2] count: counting the records, elapsed 10m9s, eta unknown database /srv/tmp/sfdupes.db (from SFDUPES_DATABASE) database file size 25582678016 (23.8 GiB) modified 2026-10-23T16:43:59Z schema version 1 records 28006608 records with head and tail hashes 26929372 records with content hash 26896464 size of recorded files 164007990979900 (149.2 TiB) real 10m8.880s user 0m27.511s sys 9m31.487s root@las1stor1:~# ```

clawbot commented

2026-10-27 17:13:30 +01:00

Cause: info gets its record count and the total size with one query, SELECT COUNT(*), SUM(size) FROM files (databaseCountsSQL in db.go), and SQLite can answer that only by reading every record. At 28 million records that is a pass over about half of the 23.8 GiB file, which SQLite reads 4 KiB at a time with one system call per read; that is the 9m31s of system time. No index helps, since every index holds every record too. Fix: the database keeps the number of records and their total size in a table of one row, which triggers on files update in the same transaction as every insert, update and delete, so info reads that one row and answers in well under a second at any size. The same SELECT COUNT(*) FROM files also runs before scan's load phase and before report and trees read the records, an extra full pass each only to give the progress display its total; they read the stored count too. The schema in README.md and info's description there change with it.

Done when:

A new database has the one-row table and its three triggers (after insert, after delete, after an update of size); scan writes files exactly as now.
info reads the count and the total from that row in its read-only transaction, and scan, report and trees read their progress total from it; no count reads files.
A test fails if a query that counts records reads files (checked with EXPLAIN QUERY PLAN), and a test checks that the row equals COUNT(*) and SUM(size) over files after a first scan, a rescan that changes sizes, and a rescan that deletes records.
Measured in Docker on a generated database of several million records, info before and after the change.

Model: opus-5-5

Cause: `info` gets its record count and the total size with one query, `SELECT COUNT(*), SUM(size) FROM files` (`databaseCountsSQL` in `db.go`), and SQLite can answer that only by reading every record. At 28 million records that is a pass over about half of the 23.8 GiB file, which SQLite reads 4 KiB at a time with one system call per read; that is the 9m31s of system time. No index helps, since every index holds every record too. Fix: the database keeps the number of records and their total size in a table of one row, which triggers on `files` update in the same transaction as every insert, update and delete, so `info` reads that one row and answers in well under a second at any size. The same `SELECT COUNT(*) FROM files` also runs before `scan`'s `load` phase and before `report` and `trees` read the records, an extra full pass each only to give the progress display its total; they read the stored count too. The schema in `README.md` and `info`'s description there change with it. Done when: - A new database has the one-row table and its three triggers (after insert, after delete, after an update of `size`); `scan` writes `files` exactly as now. - `info` reads the count and the total from that row in its read-only transaction, and `scan`, `report` and `trees` read their progress total from it; no count reads `files`. - A test fails if a query that counts records reads `files` (checked with `EXPLAIN QUERY PLAN`), and a test checks that the row equals `COUNT(*)` and `SUM(size)` over `files` after a first scan, a rescan that changes sizes, and a rescan that deletes records. - Measured in Docker on a generated database of several million records, `info` before and after the change. Model: opus-5-5

clawbot referenced this issue from a commit

2026-10-27 17:21:43 +01:00

Keep the record count and total size in a totals table (closes #146)

clawbot referenced a pull request that will close this issue

2026-10-27 17:24:14 +01:00

Keep the record count and total size in a totals table (closes #146) #148

clawbot referenced this issue from a commit

2026-10-27 17:33:23 +01:00

Keep the record count and total size in a totals table (closes #146)

clawbot referenced this issue from a commit

2026-10-27 17:54:41 +01:00

Keep the record count and total size in a totals table (closes #146)