Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

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

Aperta
  • Qualcosa non va?
#1,157 1 commento 10 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
55/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
php, postgresql
Ambito
databases, observability

Direzione di ricerca

The rule is in apps/serverinfo/lib/Database/RuleSet/Postgres.php; read its seq_scan/idx_scan check and then inspect Advisor.php's MIN_UPTIME_HOURS/COUNTER_SENSITIVE_RULE_IDS gate. Compare the proposed size or row-count signals against the reported PostgreSQL behavior, and confirm done by ensuring genuinely small indexed tables are no longer reported while large sequentially scanned tables remain visible.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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):

table live rows total size seq_scan idx_scan
oc_users 3 40 kB 2,198,160 0
oc_preferences 89 96 kB 2,197,028 0
oc_guests_users 0 32 kB 1,782,632 0
oc_mimetypes 240 88 kB 1,709,915 0
oc_accounts 3 72 kB 1,658,128 0
oc_circles_membership 0 64 kB 1,189,503 0
oc_deck_boards 0 56 kB 1,001,275 0

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.

Lingua principale
JavaScript
Stelle
133
Fork
67
Merge medio
22h 55m
PR unite (30g)
53

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di nextcloud/serverinfo

Tutte le issue di nextcloud/serverinfo

Issue simili

Altre issue su JavaScript

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.