pg.tables_without_index_use false-positives on small, correctly-indexed tables #1157

Nextcloud & app version

• Nextcloud Server: 35.0.0
• serverinfo app: 7.0.0
• Database: PostgreSQL 15.18 (Debian 15.18-0+deb12u1)

Summary

The "Database checks" advisor on Settings → Overview → Database flags "Tables scanning sequentially" (pg.tables_without_index_use) as failing for tables that are only a few kilobytes / a handful of rows, already carry a primary-key index, and for which PostgreSQL's own planner correctly and deliberately chooses a sequential scan over an index scan — because the table is small enough that a seq scan is the cheaper plan.

The rule (apps/serverinfo/lib/Database/RuleSet/Postgres.php) triggers purely on:

seq_scan > 1_000_000 AND idx_scan = 0

This threshold only measures scan count, never table size or row count. Since Nextcloud's own small system/config tables (e.g. oc_users, oc_accounts, oc_preferences) are read on effectively every request, their seq_scan counter climbs into the millions over normal server uptime even though each individual scan touches a single 8 KB heap page. The rule has no way to distinguish "1M scans over 2 rows" (free) from "1M scans over 2M rows" (a real missing-index problem), so it reliably false-positives on any long-running, low-traffic instance.

Steps to reproduce

1. Run a Nextcloud 35 instance on PostgreSQL for long enough that core tables accumulate >1M sequential scans (any small/medium instance left running for a few weeks will do this — these tables are queried on essentially every authenticated request).
2. Open Settings → Overview. The "Database configuration" check shows a warning.
3. Open Settings → Administration → Database (or the servrinfo #database panel) → Database checks → "S problémy" (Failing). See "Tables scanning sequentially", listing oc_users, oc_accounts, oc_preferences, oc_mimetypes, oc_guests_users, oc_circles_membership, oc_deck_boards (exact set depends on the instance, but core low-cardinality tables are the reliable offenders).

Evidence that this is a false positive

Actual state on our instance (2 real user accounts, small install):

Indexes already exist and are correct per Nextcloud's own schema (occ db:add-missing-indices, occ db:add-missing-primary-keys, occ db:add-missing-columns all report nothing missing):

$ psql -c "SELECT indexname, indexdef FROM pg_indexes WHERE tablename IN ('oc_users','oc_accounts');"
       indexname       |                                   indexdef
------------------------+------------------------------------------------------------------------------
 oc_accounts_pkey       | CREATE UNIQUE INDEX oc_accounts_pkey ON public.oc_accounts USING btree (uid)
 oc_users_pkey          | CREATE UNIQUE INDEX oc_users_pkey ON public.oc_users USING btree (uid)
 user_uid_lower         | CREATE INDEX user_uid_lower ON public.oc_users USING btree (uid_lower)

And PostgreSQL's planner, with that index available, deliberately picks the sequential scan anyway because it's the actually-cheaper plan for a single-page table:

$ psql -c "EXPLAIN SELECT * FROM oc_users WHERE uid = 'h0nyik';"
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on oc_users  (cost=0.00..1.02 rows=1 width=954)
   Filter: ((uid)::text = 'h0nyik'::text)

$ psql -c "EXPLAIN SELECT * FROM oc_accounts WHERE uid = 'h0nyik';"
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on oc_accounts  (cost=0.00..3.08 rows=1 width=178)
   Filter: ((uid)::text = 'h0nyik'::text)

So the rule's own suggested remedy ("Add appropriate indexes to the affected tables") would not change anything: the index is already there, and the planner already knows about it, and correctly ignores it.

Expected behavior

The rule shouldn't fire for tables where a sequential scan is genuinely the optimal plan. A few ways to fix this that seem consistent with the codebase's existing patterns:

1. Gate on table size, analogous to the existing MIN_UPTIME_HOURS / COUNTER_SENSITIVE_RULE_IDS gate in Advisor.php that already exists to avoid false positives from counters that haven't accumulated meaningful data yet (restart-sensitive ratios). E.g. only flag a table if pg_total_relation_size(relid) (or n_live_tup) is above some minimum (a table under, say, a few hundred KB / a few thousand rows essentially can't benefit from an index scan on PostgreSQL).
2. Alternatively, cross-check against pg_stat_user_tables.seq_tup_read / seq_scan (average rows read per scan) — a consistently tiny average strongly indicates the table is trivially small, regardless of the raw scan count.
3. At minimum, mention table size next to each flagged table name in the details so an admin can immediately tell a real missing-index case from a "this table is 40 kB, ignore" case, without having to run their own pg_stat_user_tables query to figure that out (as I had to here).

Additional note

There currently appears to be no way for an admin to acknowledge/dismiss one specific rule result once it's understood to be a non-issue for their instance — every page load re-evaluates and re-flags it (checked Advisor.php, CheckSummary.php, DatabaseController.php, and the core SetupCheckManager — no skip/ignore/acknowledge mechanism beyond the automatic "required value missing" / uptime-gate skips). Even if the size-based fix above isn't adopted, a persistent per-instance "don't show this rule again" would at least let an admin who has verified a result is a non-issue stop seeing it flagged as failing on every visit.

Happy to provide any more detail (pg_stat_user_tables dumps, full EXPLAIN ANALYZE, etc.) if useful.